3流プログラマのメモ書き

元開発職→社内SE→派遣で営業支援→開発戻り浦島太郎状態の三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。

(SQL)SELECT句に副問い合わせを含める

SELECT句の副問い合わせはJOIN内の副問い合わせとは違い、外のフィールドが使えるので便利です。

たとえば、下記のように Customer テーブルと利用履歴を残した UseHistroy テーブルがあるとします。

Customer テーブル

┏━━━━━━━━━━━━━━━━━┓

┃CustomerID CustomerName Birthday┃

┃1 安部礼司 19711010┃

┃2 倉橋優 19810831┃

┃3 飯野平太 19800210┃

┃4 刈谷勇 19710401┃

┗━━━━━━━━━━━━━━━━━┛

UseHistory テーブル

┏━━━━━━━━━━━━━━┓

┃CustomerID LineNo UseDate ┃

┃1 1 20090101┃

┃1 2 20090220┃

┃1 3 20090310┃

┃2 1 20090225┃

┃2 2 20090310┃

┃3 1 20090310┃

┃4 1 20090311┃

┗━━━━━━━━━━━━━━┛

(Birthday,UseDateは文字列型の列)

上記のテーブルデータを使い、20090301 に利用した人のデータと、その人が 20090301 以前の直近の利用日(前回利用日)を抽出したいSQLはこうなります。

SELECT Customer.CustomerID   ,

Customer.CustomerName ,

/*年齢を出す(詳しくはhttp://jehupc.exblog.jp/9075644/)*/

INT((INT(Format(DATE(),'yyyymmdd')) - INT(Customer.Birthday) )/ 10000) AS Age ,

/*前回利用日を出す副問い合わせ*/

( SELECT MAX(UseDate)

FROM UseHistory AS UseHistory2

WHERE UseHistory2.UseDate < '20090310'

/*副問い合わせの外のフィールドの値を条件に用いる*/

AND UseHistory2.CustomerID = Customer.CustomerID

) AS LastUseDate

FROM UseHistory

INNER JOIN Customer

ON UseHistory.CustomerID = Customer.CustomerID

WHERE UseHistory.UseDate = '20090310'

結果はこうなります。

┏━━━━━━━━━━━━━━━━━━━━━━┓

┃CustomerID CustomerName Age LastUseDate ┃

┃1 安部礼司 37 20090220 ┃

┃2 倉橋優 27 20090225 ┃

┃3 飯野平太 29 ┃

┗━━━━━━━━━━━━━━━━━━━━━━┛

実は最初、上記SQL内の副問い合わせは下記のSQLのようにしてました。

(SELECT   TOP 1 UseDate

FROM UseHistory AS UseHistory2

WHERE UseHistory2.UseDate < '20090310'

AND UseHistory2.CustomerID = Customer.CustomerID

ORDER BY UseDate DESC ) AS LastUseDate

しかし、この方法だと一旦 ORDER BY で並び変えるため、大量のデータを扱う場合にレスポンスに問題が出るという点を先輩から指摘されました。言われてみれば確かにソートは処理速度のリスクが高いですね。(なんで気付かなかったんだろう…)

ということで、MAX 関数を利用することにしました。