【SQL】同一のカラムの値で・・・②

Question

下記の条件でのSQLをなるべくデータベースに掛かるコストが軽くなるように
作成したいのですが、なかなかいい案が思いつきません。

【条件】
抽出結果としては、
下記の条件に当てはまる項目Aを出力しようと考えております。

①項目Aの値が同一である
②項目Bの値が'赤'になっているレコードが含まれている
③項目Bの値が'青'になっているレコードが含まれていない
④項目Bの値が'黄'になっているレコードが含まれていない
⑤項目Bの値が'赤'になっているレコードのみしか存在せず、2件以上の場合は抽出しない

(例)
項目A 項目B
100 赤
100 青
100 黄
200 赤
200 赤
300 赤
400 赤
400 青
400 黄
500 赤
500 赤
600 赤
600 青

上記の例の場合には、
300が結果として抽出したいということです。

有識者の方々、ご指南いただけると幸いです。

Comments

[ttyp03]

600は青が含まれているから対象外ではないのですか？

[nitoage]

ご記載いただいている条件で、300と600が抽出される理由がわからないです。すべての条件がandで達成される場合だと300のみが結果な気がしますが。。。あってますでしょうか？

[AT_WORK24]

すみません！回答が誤っていたため、更新しました。ご指摘通り、300のみが抽出されます。

[yambejp]

600は青を含んでいますが？

[SVC34]

「①項目Aの値が同一である」とは具体的に何と同一であることを確認するのでしょうか

[sazi]

前回の質問は解決でしたけど、結局コストというところで解決していないのではないですか？コストが最優先なら名称は兎も角、構造やインデックス、実行計画などの状況などを提示された方が良いですよ。

Answer 1

SQLServerの分析関数の使用例
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html
の「7. 全称肯定,全称否定,存在肯定,存在否定」を見ながら
SQLFiddleのSQLServer2014環境で作ってみました :-)

with t(項目A,項目B) as(
select 100,N'赤' union all
select 100,N'青' union all
select 100,N'黄' union all
select 200,N'赤' union all
select 200,N'赤' union all
select 300,N'赤' union all
select 400,N'赤' union all
select 400,N'青' union all
select 400,N'黄' union all
select 500,N'赤' union all
select 500,N'赤' union all
select 600,N'赤' union all
select 600,N'青' union all
select 700,N'赤' union all
select 700,N'緑' union all
select 700,N'黒' union all
select 800,N'赤' union all
select 800,N'赤' union all
select 800,N'黒')
select 項目A
  from t
group by 項目A
having sum(case 項目B when N'赤' then 1 else 0 end) > 0 --条件2
   and sum(case 項目B when N'青' then 1 else 0 end) = 0 --条件3
   and sum(case 項目B when N'黄' then 1 else 0 end) = 0 --条件4
   and not (min(case 項目B when N'赤' then 1 else 0 end) = 1 --条件5
        and count(*) >= 2)
order by 項目A

| 項目A |
|-----|
| 300 |
| 700 |
| 800 |

Answer 2

300だけでいいならコウです

select 項目A from tbl
group by 項目A
having sum(case 項目B when '赤' then 1 else 2 end)=1

追記

実は赤・青・黄以外にも色がありその色が指定されたデータ数は不問で、
青・黄だけ除いた上で赤が１つだけのデータをほしいならこうです

select 項目A from tbl
group by 項目A
having sum(case 項目B when '赤' then 1 when '青' then 2 when '黄' then 2 else 0 end)=1

Answer 3

動作確認できてませんが、こんな感じでしょうか。

select 項目A,
        sum(case when 項目B='赤' then 1 else 0 end) as rcnt,
        sum(case when 項目B='青' then 1 else 0 end) as bcnt,
        sum(case when 項目B='黄' then 1 else 0 end) as ycnt
from tbl
group by 項目A
having rcnt=1 and bcnt=0 and ycnt=0;

Comments

[AT_WORK24]

仮に赤、青、黄のほかに別色が存在した場合に、赤を2件以上含んでいて別色が含まれているものは抽出したい場合は(赤のみで2件以上のみを除きたい)、上記のクエリだと難しいですよね？

[ttyp03]

そうですね。
他の色の可能性も含めるのであれば、赤と赤以外の件数を数えればよいかと思います。
select 項目A,
sum(case when 項目B='赤' then 1 else 0 end) as rcnt,
sum(case when 項目B<>'赤' then 1 else 0 end) as nrcnt
from tbl
group by 項目A
having rcnt=1 and nrcnt=0;