しかも、自分の中で問題になっていた。「存在しない日付の月集計」もこれを使えば解決できました。
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側のメンテは大丈夫なのだろうか。。。?
ちと不安です。