MySQLで連番テーブル生成

group by したときに引っかからなかった列の値を0にしたいとかありますよね？

例えばこんなの

mysql> SELECT a,count(a) count FROM hoge GROUP BY a;
+---+-------+
| a | count |
+---+-------+
| 1 |   110 |
| 3 |   540 |
| 4 |   245 |
+---+-------+

を

+---+-------+
| a | count |
+---+-------+
| 1 |   110 |
| 2 |     0 |
| 3 |   540 |
| 4 |   245 |
| 5 |     0 |
+----+------+

ってな風に。

PostgreSQLだと generate_series() があるのに、MySQLだとなくてイライラ。

なので generate_series() の代わりにこんなクエリで代用する。

SELECT s.a, coalesce(r.count,0) count FROM (
  SELECT a,count(a) count FROM hoge GROUP BY a
) r RIGHT JOIN (
  SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
) s ON r.a=s.a;

んで、大量の連番をつくるのであればこんな風にやる。

SELECT s.a, coalesce(r.count,0) count FROM (
  SELECT a,count(a) count FROM hoge GROUP BY a
) r RIGHT JOIN (
  SELECT s0.a*25 + s1.a*5 + s2.a a FROM
  (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) s0,
  (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) s1,
  (SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) s2
) s ON r.a=s.a;

これだと0〜124の連番が生成される。もっと欲しいならRIGHT JOIN内のUNIONを増やすか、SELECTを増やせばいい。