sqlite – 如何填写缺少的月份?
发布时间:2020-12-12 18:53:34 所属栏目:百科 来源:网络整理
导读:我有以下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
我有以下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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |