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

Question

すいません。初心者の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(最新データ)

という結果を取得したいのです。

どなたか詳しい方のご教授をよろしくお願い致します。

jamshid6 · Accepted Answer

#6の方のご意見は尤もだと思いますが、「SQL ServerのカテゴリでSQL Server2005を使っている人の質問に対し、SQL Serverの標準構文で回答する」ことにはあまり問題を感じないです。

全く同じ質問が知恵袋にも出ていて、そちらにも一往復回答がついていますが、そこで止まっているところを見ると、たぶん解決したんだろうと思っています。

ただ、OKWaveは質問を締めなくても履歴が残るので、後で見る人に「IS NULLの動きが設定でコントロールできる」という誤解を与えないようにコメントを追加しただけ、というのが正直なところです。

khazad-lefty · Answer

#4です。
ああ。確かにここまで書いてて検証してないというのはあたまわるすぎですね＜私
失礼しました。

多分「ひとつでも式にNullがあるとすべてNullになる」というのを以前聞いてて、無条件にそれを信じ込んでいたせいだと思います。

nda23 · Answer

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

jamshid6 · Answer

#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の場合、常に成立します。
したがって、残念ながら質問者さんが自分の環境で試すに際して、正しく書き換えてくれなかったのだろうと推測します。

khazad-lefty · Answer

まず、
「有効日付>=@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を取得するのが簡単だとは思います。

jamshid6 · Answer

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

nda23 · Answer

先ず、論理を決めて考えましょう。新しい課題の度に苦労しますよ。
(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のデータが無く、
　しかも、最新日が指定日以前のデータは抽出されません。

jamshid6 · Answer

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

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

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

#4です。

＃２です。

#1,#3です。

まず、

#1です。

先ず、論理を決めて考えましょう。

入力日付を@dateとすれば

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング