首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在单个SELECT语句中获取每月数据分布

问在单个SELECT语句中获取每月数据分布
EN

Stack Overflow用户
提问于 2019-01-26 07:26:22
回答 1查看 82关注 0票数 0

我需要使用一条mysql语句按月分发我的数据。以下语句的预期结果:一个包含12行的表。如果在一个月内没有可用的数据,它应该返回这个月的0。但是,在当前SQL语句的结果中,如果缺少数据,则会缺少一行或多行。我错过了什么?

代码语言:javascript
复制
SELECT m.Month as m1, MONTH(FROM_UNIXTIME(crdate)) as m2,
SUM(CASE WHEN keylock = 1 THEN 1 ELSE 0 END) AS c1, SUM(CASE WHEN keylock = 2 THEN 1 ELSE 0 END) AS c2
FROM tx_bhm_domain_model_access a 
RIGHT OUTER JOIN (
    SELECT 1 AS Month 
    UNION SELECT 2 AS Month 
    UNION SELECT 3 AS Month 
    UNION SELECT 4 AS Month 
    UNION SELECT 5 AS Month 
    UNION SELECT 6 AS Month 
    UNION SELECT 7 AS Month 
    UNION SELECT 8 AS Month 
    UNION SELECT 9 AS Month
    UNION SELECT 10 AS Month 
    UNION SELECT 11 AS Month 
    UNION SELECT 12 AS Month ) m
ON MONTH(FROM_UNIXTIME(a.crdate)) = m.Month
WHERE YEAR(FROM_UNIXTIME(a.crdate))=2018 
GROUP BY m.Month
ORDER BY m.Month
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-01-26 07:28:56

需要在ON子句中进行YEAR()比较。我更喜欢LEFT JOIN

代码语言:javascript
复制
SELECT m.Month as m1, MONTH(FROM_UNIXTIME(crdate)) as m2,
       SUM(CASE WHEN keylock = 1 THEN 1 ELSE 0 END) AS c1,
       SUM(CASE WHEN keylock = 2 THEN 1 ELSE 0 END) AS c2
FROM (SELECT 1 AS Month UNION ALL
      SELECT 2 AS Month UNION ALL 
      SELECT 3 AS Month UNION ALL
      SELECT 4 AS Month UNION ALL 
      SELECT 5 AS Month UNION ALL
      SELECT 6 AS Month UNION ALL
      SELECT 7 AS Month UNION ALL
      SELECT 8 AS Month UNION ALL
      SELECT 9 AS Month UNION ALL
      SELECT 10 AS Month UNION ALL
      SELECT 11 AS Month UNION ALL
      SELECT 12 AS Month
     ) m LEFT JOIN
     tx_bhm_domain_model_access a
     ON MONTH(FROM_UNIXTIME(a.crdate)) = m.Month AND
        YEAR(FROM_UNIXTIME(a.crdate)) = 2018 
GROUP BY m.Month
ORDER BY m.Month;

我还将UNION%s更改为UNION ALL%s。没有必要产生删除重复项的开销。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/54374135

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档