代码之家 › 专栏 › 技术社区 › Harry

MySQL并集选择结果到列

union select mysql

Harry · 技术社区 · 2 年前

我使用下面的查询一次获得每年的总值和每月的总值,结果是两行,但我希望结果是列。有人能帮忙吗?谢谢

SELECT SUM(a.smd24_18v_act)
FROM smd24_log_monthly as a
WHERE  a.smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE
AND NOT YEAR(a.smd24_date) = YEAR(CURRENT_DATE)
UNION
SELECT SUM(b.smd24_18v_act)
FROM smd24_log_monthly as b
WHERE MONTH(b.smd24_date) = 1
AND b.smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE
AND NOT YEAR(b.smd24_date) = YEAR(CURRENT_DATE);

1 回复 | 直到 2 年前

Tim Biegeleisen 2 年前

将单程查询与条件聚合一起使用:

SELECT
    SUM(smd24_18v_act) AS val1,
    SUM(CASE WHEN MONTH(smd24_date) = 1 THEN smd24_18v_act ELSE 0 END) AS val2
FROM smd24_log_monthly
WHERE smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE AND
      NOT YEAR(smd24_date) = YEAR(CURRENT_DATE);

推荐文章