SQLServerの分析関数の使用例
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html
の「7. 全称肯定,全称否定,存在肯定,存在否定」を見ながら
SQLFiddleのSQLServer2014環境で作ってみました :-)
sql
1with t(項目A,項目B) as(
2select 100,N'赤' union all
3select 100,N'青' union all
4select 100,N'黄' union all
5select 200,N'赤' union all
6select 200,N'赤' union all
7select 300,N'赤' union all
8select 400,N'赤' union all
9select 400,N'青' union all
10select 400,N'黄' union all
11select 500,N'赤' union all
12select 500,N'赤' union all
13select 600,N'赤' union all
14select 600,N'青' union all
15select 700,N'赤' union all
16select 700,N'緑' union all
17select 700,N'黒' union all
18select 800,N'赤' union all
19select 800,N'赤' union all
20select 800,N'黒')
21select 項目A
22 from t
23group by 項目A
24having sum(case 項目B when N'赤' then 1 else 0 end) > 0 --条件2
25 and sum(case 項目B when N'青' then 1 else 0 end) = 0 --条件3
26 and sum(case 項目B when N'黄' then 1 else 0 end) = 0 --条件4
27 and not (min(case 項目B when N'赤' then 1 else 0 end) = 1 --条件5
28 and count(*) >= 2)
29order by 項目A
30
31| 項目A |
32|-----|
33| 300 |
34| 700 |
35| 800 |