590
社区成员
发帖
与我相关
我的任务
分享
WITH table1(id,user_id,subject_id)AS(
SELECT 1, 1, 11 UNION ALL
SELECT 2, 1, 12 UNION ALL
SELECT 3, 1, 13 UNION ALL
SELECT 4, 2, 11 UNION ALL
SELECT 5, 2, 12 UNION ALL
SELECT 6, 2, 15 UNION ALL
SELECT 7, 3, 13
),
a AS (
SELECT DISTINCT user_id, subject_id
FROM table1
WHERE subject_id IN(11,12,13)
)
,b AS (
SELECT user_id,
SUM(CASE subject_id
WHEN 11 THEN 1
WHEN 12 THEN 1
WHEN 13 THEN -2
END) mask
FROM a
GROUP BY user_id
)
SELECT *
FROM b
/* 最后自己加条件过滤,2就是有11、12没有13的,-2就是有13没有11、13的。
WHERE mask = ?
*/
user_id mask
----------- -----------
1 0
2 2
3 -2
; with tbl(id,user_id,subject_id) as
(
select 1, 1, 11 union all
select 2, 1, 12 union all
select 3, 1, 13 union all
select 4, 2, 11 union all
select 5, 2, 12 union all
select 6, 2, 15 union all
select 7, 3, 13
),
tb01 as
(
select id, [user_id],
case subject_id when '11' then 1 else 0 end as s1,
case subject_id when '12' then 1 else 0 end as s2,
case subject_id when '13' then 1 else 0 end as s3
from tbl
)
select
[user_id]
from tb01 group by [user_id]
-- subject_id 有11 和 12 但是没有13
having max(s1)>0 and max(s2)>0 and max(s3) = 0
-- subject_id 有13 但是没有11和12
-- having max(s1)=0 and max(s2)=0 and max(s3) > 0