分岐SQLを一発のSQLで実現したい

質問

【データベース：SQLServer2005】
SQLのアドバイスを頂きたいです。

【テーブル】
テーブル名：Table1
フィールド名：種類、キー1、キー2

テーブル名：Table2
フィールド名：種類、キー1、キー2

テーブル名：Table3
フィールド名：種類、キー1、キー2

【データ】
Table1
種類、キー1、キー2
001　　AAA　　BBB
001　　AAA　　CCC
001　　AAA　　DDD

Table2
種類、キー1、キー2
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　DDD

Table3
種類、キー1、キー2
NULL　 AAA　　BBB
NULL　 AAA　　CCC
NULL　 AAA　　DDD

これをUNIONで取得します。
種類、キー1、キー2
001　　AAA　　BBB
001　　AAA　　CCC
001　　AAA　　DDD
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　DDD
NULL　 AAA　　BBB
NULL　 AAA　　CCC

実現したいのは、
１）同種類、キー1、キー2のデータで、
種類にNULLが含まれていたら、NULLのデータは取得しない
２）同種類、キー1、キー2のデータで、
種類にNULLしかないデータは、取得する

001　　AAA　　BBB
001　　AAA　　CCC
001　　AAA　　DDD
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　DDD　→いる
NULL　 AAA　　BBB　→いらない
NULL　 AAA　　CCC　→いらない

↓結果

001　　AAA　　CCC
001　　AAA　　DDD
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　DDD

上記を実現する為、UNION後のSQLでも構わないので、
一発のSQLで取得する事は可能でしょうか？

有識者の方にご享受頂ければ幸いです。
どうぞ宜しくお願い申し上げます。

回答No.3

yamada_g · ベストアンサー

補足ありがとうございます。

ということは
>001　　AAA　　DDD
があって
>NULL　 AAA　　DDD
もあるというのは間違っていますよね。

ですので、以下のデータと結果を正として考えます。
--データ
001　　AAA　　BBB
001　　AAA　　CCC
001　　AAA　　DDD
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　EEE --DDDをEEEに変えました
NULL　 AAA　　BBB
NULL　 AAA　　CCC

--結果
001　　AAA　　BBB
001　　AAA　　CCC
001　　AAA　　DDD
002　　AAA　　BBB
002　　AAA　　CCC
NULL　 AAA　　EEE

以下のSQLでどうでしょうか。

select 種類,キー1,キー2
from (
　select 種類,キー1,キー2,count(*) over(partition by キー1,キー2) cnt
　from (
　　select 種類,キー1,キー2 from Table1
　　union select 種類,キー1,キー2 from Table2
　　union select 種類,キー1,キー2 from Table3
　)
)
where 種類 is not null or cnt = 1
order by 種類,キー1,キー2;

全角スペースでインデントしているので、実行時には削除するか半角スペースに置き換えるなどしてください。

お礼

括弧閉じるの後に、テーブル別名を付ければ可能でした！
すみません！

over句により、partition by でグループ化された、数（count(*) ）が、cntに表示されるのですね！

NULLだけのデータなら、必ず1になりますから、
where句で条件付けすると！

勉強になりました！

真にありがとうございます！

補足

ありがとうございます。

over句やpartition by は知識がなかったので、
学ぼうと思います！

ちょっと実行してみたのですが、
----------------------------------------
')' 付近に不適切な構文があります。
----------------------------------------
最後の')'付近のエラーが出ます。

全角スペースのインデントも取り除いておりますので、
エラーの原因はお分かりになるでしょうか？

またご面倒ですが、返信宜しくお願い申し上げます。

回答No.2

yamada_g

条件がいまいち理解できません・・

>１）同種類、キー1、キー2のデータで、
>種類にNULLが含まれていたら、NULLのデータは取得しない
>２）同種類、キー1、キー2のデータで、
>種類にNULLしかないデータは、取得する

同種類のなかで種類がNULLって矛盾していませんか？
同種類というくくりにならないですよね。

仮に２）を
同一のキー1、キー2のデータで、
種類にNULLしかないデータは、取得する
という条件だとしても
>001　　AAA　　DDD
があるのに
>NULL　 AAA　　DDD
がいるというのが分かりません。

SiegruneさんのSQLでも、
>NULL　 AAA　　DDD
このデータは取得できないように思います。

本当に１）と２）の条件で実現したいことを表せていますか？
もしくは提示したデータと期待結果が間違ってはいないですか？

補足

書込みありがとうございます。

仰る通り言葉がおかしかったです

同種類とは云っても、NULLは別物です。

１）
同キー1、同キー2のデータで、
種類がNULLと、種類がNULL以外が混じっていたら、NULL以外のデータを取得

２）
同キー1、同キー2のデータで、
種類がNULLだけのデータなら、NULLのデータを取得

上記になります。

正しいご指摘誠にありがとうございます！

回答No.1

Siegrune

↓結果 に
001　　AAA　　BBB
がありませんが、あるものとして回答します。
（002　　AAA　　BBB　を除去せず、
　001　　AAA　　BBB　のみを除去するためのロジックが書かれていないので。）

select * from Table1 as m1
where not (
種類 is null and
(
exists
(select * from Table1 as s1
where s1.種類 is not null
and s1.キー1 = m1.キー1
and s1.キー2 = m1.キー2
) or
exists
(select * from Table2 as s2
where s2.種類 is not null
and s2.キー1 = m1.キー1
and s2.キー2 = m1.キー2
) or
exists
(select * from Table3 as s3
where s3.種類 is not null
and s3.キー1 = m1.キー1
and s3.キー2 = m1.キー2
)
))
union all
select * from Table2 as m2
where not (
種類 is null and
(
exists
(select * from Table1 as s1
where s1.種類 is not null
and s1.キー1 = m2.キー1
and s1.キー2 = m2.キー2
) or
exists
(select * from Table2 as s2
where s2.種類 is not null
and s2.キー1 = m2.キー1
and s2.キー2 = m2.キー2
) or
exists
(select * from Table3 as s3
where s3.種類 is not null
and s3.キー1 = m2.キー1
and s3.キー2 = m2.キー2
)
))
union all
select * from Table3 as m3
where not (
種類 is null and
(
exists
(select * from Table1 as s1
where s1.種類 is not null
and s1.キー1 = m3.キー1
and s1.キー2 = m3.キー2
) or
exists
(select * from Table2 as s2
where s2.種類 is not null
and s2.キー1 = m3.キー1
and s2.キー2 = m3.キー2
) or
exists
(select * from Table3 as s3
where s3.種類 is not null
and s3.キー1 = m3.キー1
and s3.キー2 = m3.キー2
)
))

インデックスの持ち方とデータ件数によっては下のほうが早いかもしれません。

select * from Table1 as m1
where not (
種類 is null and
exists
(select * from
(select distinct キー1,キー2 from
(select * from Table1 as s1
where s1.種類 is not null
union
select * from Table2 as s2
where s2.種類 is not null
union
select * from Table3 as s3
where s3.種類 is not null
)
) as x
where m1.キー1 = x.キー1
and m1.キー2 = x.キー2
))
union all
select * from Table2 as m2
where not (
種類 is null and
exists
(select * from
(select distinct キー1,キー2 from
(select * from Table1 as s1
where s1.種類 is not null
union
select * from Table2 as s2
where s2.種類 is not null
union
select * from Table3 as s3
where s3.種類 is not null
)
) as x
where m2.キー1 = x.キー1
and m2.キー2 = x.キー2
))
union all
select * from Table3 as m3
where not (
種類 is null and
exists
(select * from
(select distinct キー1,キー2 from
(select * from Table1 as s1
where s1.種類 is not null
union
select * from Table2 as s2
where s2.種類 is not null
union
select * from Table3 as s3
where s3.種類 is not null
)
) as x
where m3.キー1 = x.キー1
and m3.キー2 = x.キー2
))

table1/table2/table3にまったく同一のデータはない、または、あったときには、
１レコードでるだけでいいならば union all をunionにしてもらっても問題ないです。

## 未検証。ケアレスミスがあったらごめんなさい。
## エラーメッセージ書いてもらえば訂正に戻ってきますので。
## (とりあえず考え方は分ってもらえるかなと。)

お礼

結果を間違っておりました！
仰る通り、001　　AAA　　BBBは取得します。

ちょっと参考にしてみます！
頑張ってみます。