MYSQL 连接两个表并比较两列

Question

我有两张 table 1)用户(id，注册日期)2)user_answer(userid,answer,updated_date)

我想要每天零使用的计数。每天有多少用户在注册但没有回答。结果将是这样的:

Date        registedCount   notAnsweredCount

15-09-02    20              10
15-09-01    20              10
15-08-31    12              4

提前致谢。

Answer 1

检查下面的查询，它应该能得到你想要的结果

SELECT results.date, 
    SUM(results.answered) as answered,
    COUNT(results.userid)-SUM(results.answered) as notanswered
FROM
(SELECT @id:= u.id as userid, @regdate:= u.regdate as date,
   (CASE WHEN EXISTS (SELECT 1 FROM user_answer WHERE updated_date = @regdate AND userid = @id)
    THEN 1 ELSE 0 END) AS answered
FROM users u) AS results
GROUP BY results.date