しかも、自分の中で問題になっていた。「存在しない日付の月集計」もこれを使えば解決できました。
select * FROM (SELECT CAST(DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) as CHAR) AS ym FROM (select 0 generate_series from dual where (@num:=1 - 2) * 0 union all select @num:=@num + 1 from `information_schema`.columns limit 12) VIRTUAL) VIRTUAL_BY_MONTH_PARE LEFT OUTER JOIN (SELECT DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) AS ym FROM (SELECT 0 generate_series FROM DUAL WHERE (@num_s:=1 - 2) * 0 UNION ALL SELECT @num_s:=@num_s + 1 FROM `information_schema`.COLUMNS LIMIT 12) VIRTUAL) VIRTUAL_BY_MONTH_PARE2 on (VIRTUAL_BY_MONTH_PARE.ym = VIRTUAL_BY_MONTH_PARE2.ym)
このSQLは、ダミーのテーブルを2つ作成してそれを紐付ける既存のDBに関係のないコードです。
見づらいので抜粋しますが、下記コードを2つ用意しています。
SELECT CAST(DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) as CHAR) AS ym FROM (select 0 generate_series from dual where (@num:=1 - 2) * 0 union all select @num:=@num + 1 from `information_schema`.columns limit 12) VIRTUAL
これだけを実行すると
+--------+ | ym | +--------+ | 201401 | | 201402 | | 201403 | | 201404 | | 201405 | | 201406 | | 201407 | | 201408 | | 201409 | | 201410 | | 201411 | | 201412 | +--------+ 12 rows in set (0.04 sec)
と、なるわけですよ。
んで、大元を実行すると
+--------+--------+ | ym | ym | +--------+--------+ | 201401 | 201401 | | 201402 | 201402 | | 201403 | 201403 | | 201404 | 201404 | | 201405 | 201405 | | 201406 | 201406 | | 201407 | 201407 | | 201408 | 201408 | | 201409 | 201409 | | 201410 | 201410 | | 201411 | 201411 | | 201412 | 201412 | +--------+--------+ 12 rows in set (0.07 sec)
ってなるわけですよ。
しかし、私はなぜかこの結果がでなかった。でも理由がわかったのでメモ。
変数を使いまわしているせいでした。
(@num:=1 - 2) * 0 union all select @num:=@num + 1
抜粋していますが、最初上記コードを書き上げた時は、VIRTUAL_BY_MONTH_PARE
VIRTUAL_BY_MONTH_PARE2
のどちらのサブクエリも@num
で記載していましたが、そうするとjoinで失敗します。上書きするので、VIRTUAL_BY_MONTH_PARE
で201401〜12まで用意しても、VIRTUAL_BY_MONTH_PARE2
のクエリの結果は201413〜24になっているからでした。。。orz
はぁあーーーーーーーーーーーーーーorz これで2時間よ!2時間(^ω^#
ま、これは解決したのでおkとして。
んで、ちょっと問題が。。。
SQLだんだん長くなってきてね・・・?
PHPでループさせたくないから、可能な限りSQL側で対応していますが、凝った集計になるとSQLが膨れます。
PHP側はコードがすっきりしますが、SQL側のメンテは大丈夫なのだろうか。。。?
ちと不安です。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本(ソフトカバー)
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る