mysql - MySQL 的 Count() 不返回零

Question

我是一名 Web 开发人员，尝试在 mysql 查询上搜索解决方案。我无法获得 count() 函数返回零的正确解决方案。count() 函数不会对所有日期返回零。

查询如下。谁能帮我解决这个问题。

SELECT
    count(stat_id) as typeSuccess,
    device_type as typeName,
    YEARWEEK(date_auth) as date
FROM auth_stat
WHERE
    AUTH_RESULT = 'SUCCESS' AND
    date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY YEARWEEK(date_auth), device_type
ORDER BY YEARWEEK(date_auth)

我尝试形成的查询是

select
    date_auth,
    count(stat_id) as typeSuccess,
    device_type as typeName,
    YEARWEEK(date_auth) as date
from
( 
    select @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
    from
    ( 
        select @curDate := CURDATE()
    ) sqlvars, auth_stat limit 18 
) dateAll
LEFT JOIN auth_stat U on dateAll.MyJoinDate = U.date_auth
group by dateAll.MyJoinDate

实际输出:

+------------+-------------+
| date       | typeSuccess |
+------------+-------------+
| 2015-03-18 |          11 |
+------------+-------------+

预期输出:

+------------+-------------+
| date       | typeSuccess |
+------------+-------------+
| 2015-03-18 |          11 |
| 2015-03-19 |          0  |
| 2015-03-20 |          0  |
+------------+-------------+

Answer 1

我相信你想做的就是这个

SELECT
    SUM(AUTH_RESULT='SUCCESS') as numSuccess,
    YEARWEEK(date_auth) as date
FROM auth_stat
WHERE date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY YEARWEEK(date_auth), device_type
ORDER BY YEARWEEK(date_auth)

基本上我所做的就是告诉 MySQL 当 auth_result 成功时对 bool 值(0 或 1)求和。SUM() 将在特定的一周返回 0(如果有)那一周没有成功

主要问题是您过滤掉了所有不成功的 auth_results，这些结果将不被计算在内。所以把它从 where 中删除，你应该就可以了!

如果你每天都想要，那么你可以这样做

SELECT
    SUM(AUTH_RESULT='SUCCESS') as numSuccess,
    DATE(date_auth) as date
FROM auth_stat
WHERE date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY DATE(date_auth), device_type
ORDER BY DATE(date_auth)

SQLFIDDLE FOR BOTH RESULTS