mysql - 求和不同的 MYSQL | WHERE条款

Question

我想从表(历史)中获取基于 SUM 的结果，其中用户名包含“red”并按月分组。这里的查询:

select month(date),
SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) as total_sucess, 
SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END) as total_failed
from history
where date between '201305%' AND '201311%'  AND username like '%@red%' 
GROUP BY month(history.date);

结果:

+------------+--------------+--------------+
| month(date) | total_sucess | total_failed |
+------------+--------------+--------------+
|          5 |        10960 |         3573 |
|          6 |         2336 |         1202 |
|          7 |         2211 |         1830 |
|          8 |         5312 |         3125 |
|          9 |         9844 |         5407 |
|         10 |         6351 |         3972 |
+------------+--------------+--------------+

问题是，如何获得不同的 total_success 和 total_failed SUM？只是在一个查询中？

我试过用这个

select month(tgl),
SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) as total_sucess, 
SUM(DISTINCT (username) CASE WHEN status='success' THEN 1 ELSE 0 END) as distinct_total_sucess, 
SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END) as total_failed,
SUM(DISTINCT (username) CASE WHEN status='failed' THEN 1 ELSE 0 END) as distinct_failed_sucess
from history_auth
where tgl between '201305%' AND '201311%'  AND username like '%@t.sel%' 
GROUP BY month(history_auth.tgl);

但是得到错误的 sql 语法...我不知道这个 :(

Answer 1

我能从您的要求中得到的最好结果是您希望每个月成功/失败的不同用户名的数量。

如果是这样，我认为您需要一对子选择来获得这些数字。

重新调整了查询​​(添加另一个子选择来获取 6 个月，而不是依赖于所表示的所有月份。

SELECT Sub1.aMonth,
SUM(CASE WHEN history.status='success' THEN 1 ELSE 0 END) as total_sucess, 
SUM(CASE WHEN history.status='failed' THEN 1 ELSE 0 END) as total_failed,
IFNULL(SuccessCount, 0),
IFNULL(FailedCount, 0)
FROM 
(
    SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 0 MONTH)) AS aMonth 
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 1 MONTH)) 
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 2 MONTH)) 
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 3 MONTH))
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 4 MONTH))
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 5 MONTH)) 
    UNION SELECT MONTH(DATE_SUB('2013-11-01', INTERVAL 6 MONTH))
) Sub1
LEFT OUTER JOIN history 
ON MONTH(history.date) = Sub1.aMonth
AND username LIKE '%@red%' 
LEFT OUTER JOIN
(
    SELECT MONTH(date) AS aMonth, COUNT(DISTINCT username) AS SuccessCount
    FROM history
    WHERE status='success'
    AND username LIKE '%@red%' 
    GROUP BY MONTH(date)
) Sub2
ON Sub1.aMonth = Sub2.aMonth
LEFT OUTER JOIN
(
    SELECT MONTH(date) AS aMonth, COUNT(DISTINCT username) AS FailedCount
    FROM history
    WHERE status='failed'
    AND username LIKE '%@red%' 
    GROUP BY MONTH(date)
) Sub3
ON Sub1.aMonth = Sub3.aMonth
GROUP BY Sub1.aMonth, SuccessCount, FailedCount