条件付きでのグループ数の検索について

Question

#テーブル

下記のようなテーブルがあります。

CLASS NAME
xxx a
xxx b
xxx c
xxx d
yyy b
yyy c
yyy d
zzz c
zzz d
zzz e
www e
www f
www g

#課題

差し当たり、下記の条件に該当するＳＱＬが書けなくて困っています。

1)NAMEにaまたはbを含むCLASS　
WHERE IN (a,b) →　xxx,yyy　→　OK
これは問題ありませんでした。

2)NAMEにa、bどちらも含まないCLASS
WHERE NOT IN (a,b) →　xxx,yyy,zzz,www　→　NG
正解はZZZ,WWW

#質問

2)の答えとしては、1)と反対の集合になります。
NOT IN では無理でした。
a,bは数が増える可能性がありますので、IN節のように、スマートに書きたいです。

何か良い方法があれば教えてください。

select distinct t.class as cls  from test as t
where t.name in ('a','b')

xxx
yyy

select distinct t.class as cls  from test as t
where t.name not in ('a','b')

xxx
yyy
zzz
www

Answer 1

やり方はいろいろありますが、この要件であればGROUP BYを利用することが多いかと思います。

SELECT t.class AS cls
FROM test AS t
GROUP BY t.class
HAVING SUM(CASE WHEN t.name IN ('a', 'b') THEN 1 ELSE 0 END) = 0;

あるいはNOT EXISTSを利用することもできます。

SELECT DISTINCT t.class AS cls
FROM test AS t
WHERE NOT EXISTS (
  SELECT *
  FROM test AS s
  WHERE s.class = t.class
  AND s.name IN ('a', 'b')
);

Comments

[yuujiMotoki]

ありがとうございます。

一点追加でお聞きしたいのですが、これらのカウントを列で出す場合に、1)と2)のクエリを併記することは、できるものでしょうか？

1)のみの場合は、下記のようにwhere条件で指定できます。

```SQL
select count(distinct t.class) as cls from test as t
where t.name in ('a','b')
```

2)のcountをselectで併記した場合、whereが全体的にかかってしまいます。

単純にunionでつないだとすれば、1)のカウントと2)のカウントが２行で並ぶと思いますが
これを横2列にしたいのですが

[neko_the_shadow]

「AとBのどちらかを含むclass」の数と「AとBのどちらも含まないclass」の数を横持ちしたいということでしょうか? であれば以下のようなSQLになるかと思います
---
SELECT
SUM(CASE WHEN cnt > 0 THEN 1 ELSE 0 END) AS "AとBのどちらかを含む",
SUM(CASE WHEN cnt = 0 THEN 1 ELSE 0 END) AS "AとB両方を含まない"
FROM (
SELECT SUM(CASE WHEN name IN ('a', 'b') THEN 1 ELSE 0 END) AS cnt
FROM test
GROUP BY class
) AS s

[yuujiMotoki]

恐れ入りました。SUMという関数を重ねて使うのですね。このクエリには、DISTINCTもCOUNTもWHEREも使わないので、いままでとは全く別次元でした。もう少し勉強したいと思います。

Answer 2

2)の答えとしては、1)と反対の集合になります。

とのことなので、その通りに組みます。

select distinct t.class as cls  from test as t
where t.class NOT in (
  select distinct tc.class as cls  from test as tc
  where tc.name in ('a','b')
)

Answer 3

2)NAMEにa、bどちらも含まないCLASS

CLASS   NAME
xxx    c
xxx    d

WHEREの条件にc, d を含まないと書いていないからSELECT対象になっているだけでは？条件を見直しては？