selectの書き方を教えてください

質問

Oracleで以下のデータから１）、２）の条件でコードをselectしてくるには
どのようなSQL文を書けばよいでしょうか。

[DATA]
code　date　status　
0001　3-15　A
0001　3-15　B
0001　3-15　C
0002　3-15　A
0002　3-15　B
0003　3-15　B
0004　3-15　A
0005　3-15　C

１）3-15においてstatusがAとBを持つcodeを返したい

　→0001,0002が返ってきてほしい

２）3-15においてstatusにCを持つがそれ以外（A,B）は持たないcodeを返したい

　→0005が返ってきてほしい

単純なものなのかもしれませんが、
どうか、お力添えのほど、よろしくお願いいたします。

回答No.2

bitsu · ベストアンサー

>１）3-15においてstatusがAとBを持つcodeを返したい
>
>　→0001,0002が返ってきてほしい

select code
from DATA d1,DATA d2
where d1.status = 'A'
and d2.status = 'B'
and d1.date = '3-15'
and d1.code = d2.code
and d1.date = d2.date

>２）3-15においてstatusにCを持つがそれ以外（A,B）は持たないcodeを返したい
>
>　→0005が返ってきてほしい

select code
from DATA
where status = 'C'
and code not in(select code from DATA where status in('A','B'))
and date = '3-15'

で取れると思います。
SQLでデータを取るためには頭の中でどういった表を用意したら
取得できるかの妄想力が必要です。
がんばってください。

お礼

お返事ありがとうございます。
期待通りの結果が返ってきました。

not in (select ～)

なんて考えつきませんでした・・・。
まだまだ妄想力が不足しているようです。
助かりました。

補足

お教えいただいた記述を応用して以下のようにしました。

select code, date
from DATA
where
(code, date) not in
(select code, date from DATA where status!='C')
group by code, date;

ありがとうございます。

回答No.7

dda167

いちお～こちらのかんきょう（10gXE）でかくにんしてますけど～（笑）

SQL> with data as (
2 select '0001' code, '3-15' "date", 'A' status from dual union all
3 select '0001' , '3-15' , 'B' from dual union all
4 select '0001' , '3-15' , 'C' from dual union all
5 select '0002' , '3-15' , 'A' from dual union all
6 select '0002' , '3-15' , 'B' from dual union all
7 select '0003' , '3-15' , 'B' from dual union all
8 select '0004' , '3-15' , 'A' from dual union all
9 select '0005' , '3-15' , 'C' from dual
10 )
11 select code from data
12 where "date" = '3-15'
13 group by code
14 having
15 0 < sum(case when status = 'A' then 1 else 0 end)
16 and
17 0 < sum(case when status = 'B' then 1 else 0 end);

CODE
--------
0001
0002

SQL> with data as (
2 select '0001' code, '3-15' "date", 'A' status from dual union all
3 select '0001' , '3-15' , 'B' from dual union all
4 select '0001' , '3-15' , 'C' from dual union all
5 select '0002' , '3-15' , 'A' from dual union all
6 select '0002' , '3-15' , 'B' from dual union all
7 select '0003' , '3-15' , 'B' from dual union all
8 select '0004' , '3-15' , 'A' from dual union all
9 select '0005' , '3-15' , 'C' from dual
10 )
11 select code from data
12 where "date" = '3-15'
13 group by code
14 having
15 0 < sum(case when status = 'C' then 1 else 0 end)
16 and
17 0 = sum(case when status = 'C' then 0 else 1 end);

CODE
--------
0005

1)でandをorに変えると別のSQLになっちゃいますよ。

お礼

ごめんなさい、私何か間違えていたかもしれません・・・。
どこが違っているかわからないのですが・・・。

たいへん失礼いたしました - - ；

回答No.6

dda167

再アップ

2)

select code from data
where date = '3-15'
group by code
having
0 < sum(case when status = 'C' then 1 else 0 end)
and
0 = sum(case when status = 'C' then 0 else 1 end);

こっちの方がカッコイイ（笑）

お礼

いろいろ考案していただき感謝です。
having句は私にとって新しい風でした。

とても勉強になります。
たしかにSQL文かっこよすです笑。

回答No.5

dda167

別の切り口

1)

select code from data
where date = '3-15'
group by code
having
0 < sum(case when status = 'A' then 1 else 0 end)
and
0 < sum(case when status = 'B' then 1 else 0 end);

2)

select code from data
where date = '3-15'
group by code
having
0 < sum(case when status = 'C' then 1 else 0 end)
and
0 = sum(case when status <> 'C' then 1 else 0 end);

お礼

ご回答いただきありがとうございます。

１）の方法だと、うまく値が返ってきませんでした。
and を or に変えるとAとBを含むレコードが返ってきました。

２）の方法だと、C以外を含むレコードも返ってきました。
No.1さんと同じ結果になります。

回答No.4

maiko0318

No2さんのでOKでしょうか。こういう条件はやったことないので申し訳ない。

お礼

お気遣いいただきありがとうございます。
また、よろしくお願いいたします ^ ^

回答No.3

bitsu

ごめんなさい
1）ですが
select code
ではなく
select d1.code

ですね＾＾；

失礼しました。

お礼

ご丁寧にありがとうございます。

回答No.1

maiko0318

1)select code from table where date='3-15' and status in('A','B')

2)select code from table where date='3-15' and status ='C'

お礼

さっそくありがとうございます。
私の説明が至らず、誤解させてしまってすみません。

１）の書き方だと次のように返ってきます。

　code　
　0001
　0001
　0002
　0002
　0003
　0004

0003はAを含まない、0004はBを含まないので、欲しい情報には該当しません。
あと、これは書かなかったのですが、同一コードは返ってほしくないです。

　code　
　0001
　0002

と返ってほしいのですが、なんとか伝わりますでしょうか・・・？
また、２）も同様で、

　code　
　0001
　0005

と返ってしまい、0001にはAもBも含むため、
今回欲しい情報には該当しません。こちらも、

　code　
　0005

と返ってほしいです。
誤解のある書き方で大変お手数をおかけしました。
申し訳ありませんが、もう一度ご教授いただけますと助かります。
よろしくお願いします。