みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

分岐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)同種類、キー1、キー2のデータで、
種類にNULLが含まれていたら、NULLのデータは取得しない
2)同種類、キー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で取得する事は可能でしょうか?

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

投稿日時 - 2012-08-22 18:45:40

QNo.7657669

困ってます

質問者が選んだベストアンサー

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

ということは
>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;

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

投稿日時 - 2012-08-23 11:26:34

補足

ありがとうございます。

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


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

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

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

投稿日時 - 2012-08-23 14:45:14

お礼

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

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

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

勉強になりました!

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

投稿日時 - 2012-08-23 14:52:59

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

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

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

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

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

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


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

投稿日時 - 2012-08-23 09:57:21

補足

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

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

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

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

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

上記になります。

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

投稿日時 - 2012-08-23 10:57:51

ANo.1

↓結果 に
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にまったく同一のデータはない、または、あったときには、
1レコードでるだけでいいならば union all をunionにしてもらっても問題ないです。

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

投稿日時 - 2012-08-22 22:56:19

お礼

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

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

投稿日時 - 2012-08-23 10:54:10

あなたにオススメの質問