sqlite - 缺失的月份怎么填？

我有以下 SQL:

  SELECT STRFTIME('%m', date) ord,
         CASE STRFTIME('%m', date) WHEN '01' THEN 'January'
                                   WHEN '02' THEN 'Febuary'
                                   WHEN '03' THEN 'March'
                                   WHEN '04' THEN 'April'
                                   WHEN '05' THEN 'May'
                                   WHEN '06' THEN 'June'
                                   WHEN '07' THEN 'July'
                                   WHEN '08' THEN 'August'
                                   WHEN '09' THEN 'September'
                                   WHEN '10' THEN 'October'
                                   WHEN '11' THEN 'November'
                                   WHEN '12' THEN 'December'
         ELSE '' END AS month,
         count(*) AS count
    FROM events
   WHERE type='Birth' AND
         date <> ''
GROUP BY month,ord
ORDER BY ord

这给了我类似的结果:

ord  month        count
01   January      1
02   Febuary      1
03   March        3
05   May          4
07   July         2
08   August       2
09   September    2
11   November     4

但正如你所看到的，它有差距。有没有办法用0计数填补缺失的月份？

最佳答案

学习外连接。

运行以下一次:

CREATE TABLE months (
  mm char(2),
  name char(10)
);
INSERT INTO months VALUES ('01', 'January');
INSERT INTO months VALUES ('02', 'Febuary');
INSERT INTO months VALUES ('03', 'March');
INSERT INTO months VALUES ('04', 'April');
INSERT INTO months VALUES ('05', 'May');
INSERT INTO months VALUES ('06', 'June');
INSERT INTO months VALUES ('07', 'July');
INSERT INTO months VALUES ('08', 'August');
INSERT INTO months VALUES ('09', 'September');
INSERT INTO months VALUES ('10', 'October');
INSERT INTO months VALUES ('11', 'November');
INSERT INTO months VALUES ('12', 'December');

并运行这个:

  SELECT m.mm AS ord,
         m.name AS month,
         count(e.date) AS count
    FROM months m LEFT OUTER JOIN events e
      ON strftime('%m', e.date) = m.mm
   WHERE e.type='Birth' AND
         e.date <> ''
GROUP BY month,ord
ORDER BY ord;

关于sqlite - 缺失的月份怎么填？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18324387/