mysql - 如何在一个查询中每天包含多个 COUNT？

Question

尝试获取由过去 8 天的列名称“adsource”值确定的 2-3 种类型的条目的计数。到目前为止，我只能获取一种类型并添加到结果中。数据库 MySql。

我现在得到的结果(只有一种类型):

days_ago     monthday        type1
---------------------------------
1            2016-05-11      2
2            2016-05-10      34
3            2016-05-09      11
4            2016-05-08      1
5            2016-05-07      0
6            2016-05-06      16
7            2016-05-05      42
8            2016-05-04      76

我追求的结果 -> 2-3 种类型(adsource 列):

monthday        type1        type2
-----------------------------------------
2016-05-11      1            2
2016-05-10      6            0
2016-05-09      6            6
2016-05-08      1            65
2016-05-07      4            23
2016-05-06      1            12
2016-05-05      3            9
2016-05-04      6            11        

我不需要第一列 days_ago，不确定如何将其从显示中删除。我需要能够显示结果:

adsource = 9 as type1
adsource = 12 as type2
adsource = 3 as type3

这是我正在使用的查询:

SELECT *
FROM (
SELECT DATEDIFF(NOW(), edate) AS days_ago, DATE(edate) AS monthday, 
COUNT(DISTINCT entries.email) AS type1
FROM entries
WHERE iplocation = 'mx' AND adsource = 9
GROUP BY DATE(edate)) AS temp
WHERE days_ago <= 8
GROUP BY monthday
ORDER BY monthday DESC

感谢您的任何意见:)

Answer 1

尝试这样的事情..

SELECT *
FROM (
SELECT DATEDIFF(NOW(), edate) AS days_ago, DATE(edate) AS monthday, 
COUNT(DISTINCT (case when adsource=9 then entries.email end)) AS type1,
COUNT(DISTINCT (case when adsource=12 then entries.email end)) AS type2,
COUNT(DISTINCT (case when adsource=3 then entries.email end)) AS type3
FROM entries
WHERE iplocation = 'mx'
GROUP BY DATE(edate)) AS temp
WHERE days_ago <= 8
GROUP BY monthday
ORDER BY monthday DESC