只选择一个孩子的父母

问题描述：

SELECT TOP 4000 Users.Id 
    FROM Users 
    JOIN Streams ON Users.Id = Streams.UserId 
    JOIN Playlists ON Streams.Id = Playlists.StreamId 
    WHERE Playlists.FirstItemId = '00000000-0000-0000-0000-000000000000' 
    // Doesn't work 
    HAVING COUNT(1) = (
    SELECT COUNT(Playlists.Id) 
    FROM Playlists WHERE Playlists.StreamId = Streams.Id 
)

我试图选择所有只有Streams有1个播放列表和1个播放列表有一个Guid.Empty的FirstItemId的用户。我试图做一些数据库维护和删除已创建但从未使用的帐户。只选择一个孩子的父母

我已经得到了查询的第一部分工作得很好，但我不知道我可以如何正确应用我的“只有1个孩子”过滤器。

你能描述用户，溪流，和播放列表表？ –

用户与Streams有一对多的关系。流与播放列表有一对多的关系。如果有帮助，我会添加表格的屏幕截图？ –

您的问题只是count关键字？也就是说，在你的子句中，你能比较子查询与“1”而不是“COUNT（1）”吗？ – Zec

答

尝试下面的查询，选择活动用户进行

SELECT TOP 4000 Users.Id 
FROM Users 
JOIN Streams ON Users.Id = Streams.UserId 
JOIN Playlists ON Streams.Id = Playlists.StreamId 
WHERE Playlists.FirstItemId = '00000000-0000-0000-0000-000000000000' 
AND (SELECT COUNT(Playlists.Id) FROM Playlists WHERE Playlists.StreamId = Streams.Id) =1

答

我想你可以用WHERE很容易地做到这一点NOT EXISTS子句

SELECT TOP 4000 Users.Id 
    FROM Users 
    JOIN Streams ON Users.Id = Streams.UserId 
    JOIN Playlists ON Streams.Id = Playlists.StreamId 
    WHERE Playlists.FirstItemId = '00000000-0000-0000-0000-000000000000' 
    AND NOT EXISTS (
    SELECT 1 FROM Playlists 
    WHERE Playlists.StreamId <> Playlists.FirstItemId 
) 
)

这就近了。我想它给了我一个关于如何去做的想法。谢谢！ –

@seanmk +1使我的答案更容易编写 – xagyg

答

@seanmk有一个好主意......像这样...下面

SELECT TOP 4000 Users.Id 
    FROM Users 
    JOIN Streams ON Users.Id = Streams.UserId 
    JOIN Playlists p ON Streams.Id = p.StreamId 
    WHERE p.FirstItemId = '00000000-0000-0000-0000-000000000000' 
    AND NOT EXISTS (
    SELECT 1 FROM Playlists q 
    WHERE q.Id = p.Id 
     AND q.FirstItemId <> p.FirstItemId 
) 
)

答

查询应符合你的要求。

SELECT TOP 4000 Users.Id 
FROM Users 
JOIN Streams ON Users.Id = Streams.UserId 
JOIN Playlists ON Streams.Id = Playlists.StreamId 
WHERE Playlists.FirstItemId = '00000000-0000-0000-0000-000000000000' 
AND Playlists.StreamId IN (
    SELECT Playlists.StreamId 
    FROM Playlists 
    GROUP BY Playlists.StreamId 
    HAVING COUNT(*) = 1 
)

答

没有子querys，具有，或组由：

SELECT TOP 4000 Users.Id 
FROM Users 
INNER JOIN Streams ON Users.Id = Streams.UserId 
INNER JOIN Playlists p1 ON Streams.Id = p1.StreamId AND p1.FirstItemId = '00000000-0000-0000-0000-000000000000' 
LEFT JOIN Playlists p2 ON Streams.ID = p2.StreamID and p2.FirstItemID <> '00000000-0000-0000-0000-000000000000' 
WHERE p2.StreamID IS NULL

答

这是着眼于问题的另一种方式。除了使用exists，它采用聚集的条件：

select top 4000 Id 
from (SELECT u.Id, 
      min(FirstItemId) as MinFI, 
      max(FirstItemId) as MaxFI, 
      min(p.Id) as minpid, 
      max(pid) as maxpid 
     FROM Users u JOIN 
      Streams s 
      ON u.Id = s.UserId join 
      Playlists p 
      ON s.Id = p.StreamId 
     group by u.id 
    ) u 
where MinFi = MaxFi and MinFi = '00000000-0000-0000-0000-000000000000' and 
     minpid = maxpid;

我不知道哪个版本会表现得更好。这只是查看问题的一种替代方法。

只选择一个孩子的父母

相关推荐