特殊な？日付データ条件のSELECT文で困っています。回答御願いします。

質問

すいません。初心者のSQL構文の質問です。
データベースはSQLServer2005です。

ID | 履歴 | 有効日付
100 | 1 | 2009/5/15
100 | 2 | 2009/7/15
100 | 3 | NULL(最新データ)
200 | 1 | NULL(最新データ)

のようなテーブルがあります。
データ履歴を作成するたびに有効日付には指定した日付が入ります。
作成した最新のデータには有効日付にはNULLが入るようになってます。

有効日付はdate型です。

このテーブルでのSELECTで

例えば、

2009/6/15と入力された時（条件）には
結果： 2 |2009/7/15

2009/7/15と入力された時（条件）には
結果： 2 |2009/7/15

2009/7/16と入力された時（条件）には
結果： 3 |NULL(最新データ)

という結果を取得したいのです。

どなたか詳しい方のご教授をよろしくお願い致します。

回答No.7

jamshid6 · ベストアンサー

#6の方のご意見は尤もだと思いますが、「SQL ServerのカテゴリでSQL Server2005を使っている人の質問に対し、SQL Serverの標準構文で回答する」ことにはあまり問題を感じないです。

全く同じ質問が知恵袋にも出ていて、そちらにも一往復回答がついていますが、そこで止まっているところを見ると、たぶん解決したんだろうと思っています。

ただ、OKWaveは質問を締めなくても履歴が残るので、後で見る人に「IS NULLの動きが設定でコントロールできる」という誤解を与えないようにコメントを追加しただけ、というのが正直なところです。

回答No.8

khazad-lefty

#4です。
ああ。確かにここまで書いてて検証してないというのはあたまわるすぎですね＜私
失礼しました。

多分「ひとつでも式にNullがあるとすべてNullになる」というのを以前聞いてて、無条件にそれを信じ込んでいたせいだと思います。

回答No.6

nda23

＃２です。
こういうことはあまり言いたくないのですが、特定のDBエンジンに依存
する話とか、特別な環境設定を云々と言うのは本筋ではないような…
SQLを考える時、どういう理論で結果を得るかを考えることが先では
ないですか？その上で、なるべく一般的な書式を模索するべきで、
特定DBエンジンに依拠する書式は止むを得ない場合にのみ使用する
方がSQLの移植性も向上するし、勉強にもなるのではないでしょうか。

回答No.5

jamshid6

#1,#3です。

#4の方のコメントはもしかしたら、私のクエリが通らない可能性について書いていただいたのかもしれないと思いますが、ANSI_NULLSはIS NULL/IS NOT NULLの動きには影響をあたえません。

http://technet.microsoft.com/ja-jp/library/ms188048(SQL.90).aspx
>スクリプトが意図どおりに動作するようにするには、ANSI_NULLS データベース オプションや SET ANSI_NULLS の設定とは無関係に、NULL 値を含む可能性のある比較で、IS NULL と IS NOT NULL を使用するようにしてください。

なお、UNKNOWN OR TRUEはTRUEです。
http://technet.microsoft.com/ja-jp/library/ms188361(SQL.90).aspx

つまり「有効日付>=@date OR 有効日付 IS NULL」は有効日付がNULLの場合、常に成立します。
したがって、残念ながら質問者さんが自分の環境で試すに際して、正しく書き換えてくれなかったのだろうと推測します。

お礼

いろいろと回答いただきありがとうございます。貼っていただいたURLとても参考になりました。考え方も教えていただきありがとうございます。SQLもっと勉強します。

回答No.4

khazad-lefty

まず、
「有効日付>=@date OR 有効日付 IS NULL」
は、有効日付がNullの場合条件が成立するかどうかはデータベースの設定によります。

NULL 比較検索条件
http://msdn.microsoft.com/ja-jp/library/ms191270.aspx

によると、
>SET ANSI_NULLS を ON にすると、比較する式の 1 つ以上が NULL の場合、TRUE または FALSE ではなく、UNKNOWN が返されます。
>Transact-SQL は、NULL 値について比較した後に TRUE または FALSE を返す比較演算子の拡張機能をサポートしています。このオプションは、ANSI_NULLS を OFF にするとアクティブになります。

なので、SET ANSI_NULLS が ONなら、「有効日付>=@date OR 有効日付 IS NULL」は有効日付がNull

(Null)>=@date OR (Null) IS NULL
→(Unknown) or (true)
→(Unknown)

なので、条件は成立しません。
その場合、
Case when 有効日付 is Null then 1
　　when 有効日付>=@date then 1
　　else 0
end =1

の用に場合分けする必要があるようです。

あと、SelectするときにID指定が必須だというなら
order by して、top 1を取得するのが簡単だとは思います。

お礼

回答いただきありがとうございます。
order by でtop 1という考え方は全く思いつきませんでした。
ありがとうございます。

回答No.3

jamshid6

#1です。
どうやって試されたかわかりませんが、先のクエリは条件を満たしていますよ。NULLしかなくてもちゃんと取得できます。
（こういう質問は通常こちらでも確認して回答を書いていますから）

回答No.2

nda23

先ず、論理を決めて考えましょう。新しい課題の度に苦労しますよ。
(1)ID毎に有効日付の最大値（最新日）を求める。
(2)上記中、最大値が指定条件以上のものがあれば、これを抽出する。
(3)上記中、最大値が指定条件未満で、有効日付がNullのものを抽出する。
(4)上記、(2)と(3)の集合が求めるクエリである。

(2)のクエリ
SELECT MAX(履歴) AS 履歴,MAX(有効日付) AS 有効日付
FROM テーブル GROUP BY ID HAVING MAX(有効日付)>=指定日
★有効日付≠NULLの最新データが指定日以降であることが条件
　有効日付=NULLのデータの有無には関係しない。
(3)のクエリ
SELECT MAX(履歴) AS 履歴,NULL AS 有効日付
FROM テーブル GROUP BY ID HAVING MAX(有効日付)<指定日
AND SUM(CASE WHEN 有効日付 IS NULL THEN 1 ELSE 0 END)>0
★有効日付≠NULLの最新データが指定日以前だが、有効日付=NULLの
　データがあることが条件

UNION句で(2)と(3)を繋げる。尚、履歴はオートインクリメントの
ように、日付が最新のものほど大きい（つまり、比例している）と
想定しています。
★の所を見てもらうと分かりますが、有効日付=NULLのデータが無く、
　しかも、最新日が指定日以前のデータは抽出されません。

回答No.1

jamshid6

入力日付を@dateとすれば

SELECT ID,履歴,有効日付 FROM テーブル t
WHERE 履歴 IN
(SELECT MIN(履歴) FROM テーブル
WHERE ID=t.ID AND (有効日付>=@date OR 有効日付 IS NULL))
ORDER BY ID

お礼

回答ありがとうございます。
MIN(履歴) で最小の履歴しか取ってこれないようです。

100 | 1 | 2009/5/15（～2009/5/15まで有効）
100 | 2 | 2009/7/15（2009/5/16～2009/7/15まで有効）
100 | 3 | NULL（2009/7/16～まで有効）

履歴のない1レコードのみのデータとしては

履歴1 | NULL （全期間有効）

が必ず入ります。

範囲は各データで、重複するしないことを前提として、
取得したいデータとしては、

上記の通りで、入力日付に対して、取得データは1レコードのみ。

where 有効日付>=@date OR 有効日付 IS NULL とした場合は、
有効日付が入っている場合は、
確かに取ってこれるのですが、
NULL（最新データ）のみのデータが取ってこれません。

どうかご教授御願いします。。