sqlite – 如何填写缺少的月份?

sqlite – 如何填写缺少的月份?,第1张

概述我有以下SQL: SELECT STRFTIME('%m', date) ord, CASE STRFTIME('%m', date) WHEN '01' THEN 'January' WHEN '02' THEN 'Febuary' WH 我有以下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,ordORDER BY ord

这给我的结果类似于:

ord  month        count01   January      102   Febuary      103   march        305   May          407   July         208   August       209   September    211   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,ordORDER BY ord;
@H_403_44@ 总结

以上是内存溢出为你收集整理的sqlite – 如何填写缺少的月份?全部内容,希望文章能够帮你解决sqlite – 如何填写缺少的月份?所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1150272.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-05-31
下一篇 2022-05-31

发表评论

登录后才能评论

评论列表(0条)

保存