blocks|key|2117000|text|我认为使用WHERE+NOT+EXISTS子句可以很容易地实现这一点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2117001|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
++)
)|code-block|syntax|javascript|2117002|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think you can accomplish this pretty easily with a WHERE NOT EXISTS clause

<pre><code>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 &lt;&gt; Playlists.FirstItemId
 )
)
</code></pre>

blocks|key|975368|text|@seanmk有一个好主意...像这样..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|975369|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
++)
)|code-block|syntax|javascript|975370|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

@seanmk has a good idea ... like this ...

<pre><code>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 &lt;&gt; p.FirstItemId
 )
)
</code></pre>

blocks|key|2582357|text|下面的查询应该能满足您的要求。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2582358|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
)|code-block|syntax|javascript|2582359|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The query below should meet your requirements.

<pre><code>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
)
</code></pre>

blocks|key|4708162|text|无子查询、having或group+by：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4708163|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|code-block|syntax|javascript|4708164|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Without sub-querys, having, or group by:

<pre><code>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 &lt;&gt; '00000000-0000-0000-0000-000000000000'
WHERE p2.StreamID IS NULL
</code></pre>

blocks|key|2117060|text|这是看待这个问题的另一种方式。它不使用exists，而是对以下条件使用聚合：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2117061|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;|code-block|syntax|javascript|2117062|我不确定哪个版本会表现得更好。这只是看待问题的另一种方式。|2117063|entityMap^0|J|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

This is an alternative way of looking at the question. Instead of using <code>exists</code>, it uses aggregation for the conditions:

<pre><code>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;
</code></pre>

I'm not sure which versions would perform better. This is just an alternative way of looking at your problem.

blocks|key|975437|text|尝试下面的查询，以选择非活动用户输出|type|unstyled|depth|inlineStyleRanges|entityRanges|data|975438|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|code-block|syntax|javascript|975439|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try the query below, to select inactive user out

<pre><code>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
</code></pre>

<pre><code>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
)
</code></pre>

I'm trying to select all Users who have only Streams with 1 Playlist and whose 1 Playlist has a FirstItemId of Guid.Empty. I'm trying to do some database maintenance and remove accounts which were created but never used.

I've got the first part of the query working pretty well, but I'm not sure how I can apply my 'Having only 1 child' filter properly.

Select only parents with one child

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

SELECT TOP 4000 Users.Id  FROM Users  JOIN Streams ON Users.Id = Streams.UserId  JOIN Playlists ON Streams.Id = Playlists.StreamId  WHERE Playlists.FirstItemId ...

问仅选择具有一个子项的父项
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅选择具有一个子项的父项EN