27,579
社区成员
发帖
与我相关
我的任务
分享
--2. 1到12的全都有的人有哪些
;WITH cte(aindex) AS(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT * FROM tableName AS b WHERE b.isright=2
AND EXISTS(
SELECT 1 FROM tableName AS b2
WHERE b2.id = b.id
GROUP BY b2.aindex
HAVING COUNT(b2.aindex)=12
) AS t
--1. 1到12的人有哪些
;WITH cte(aindex) AS(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT * FROM cte AS a LEFT JOIN tableName AS b ON a.aindex=b.aindex
WHERE b.isright=2
--2. 1到12的全都有的人有哪些
;WITH cte(aindex) AS(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT * FROM tableName AS b WHERE b.isright=2
AND (
SELECT COUNT(1) FROM (
SELECT b2.aindex FROM tableName AS b2
WHERE b2.id = b.id
GROUP BY b2.aindex
) AS t
INNER JOIN cte ON t.aindex=cte.aindex
)=12