mysql - 总是返回一个计数？ mysql

Question

假设我有一些这样的数据

1 Red 2018-02-01
2 Red 2018-02-01
3 Red 2018-02-01
4 Blue 2018-02-01
5 Red 2018-02-02
5 Blue 2018-02-03

我想获取每种颜色每天的总计数，就像这样。

Select Count(Color) as count, color, myDate from colors
where color in ('red', 'blue') 
group by myDate , color

这应该给我带来类似的东西

3 red 2018-02-01
1 blue 2018-02-01
1 red 2018-02-02
1 blue 2018-02-03

我真正想要的是这个

3 red 2018-02-01
1 blue 2018-02-01
1 red 2018-02-02
0 blue 2018-02-02 <---- I want to know there was 0 blue on this day
1 blue 2018-02-03
0 red 2018-02-03 <----- I want to know there was 0 red on this day.

有没有简单的方法可以返回这个结果？我总是需要查看 in 子句中的内容。如果我添加其他东西，我每天需要看到 3 种颜色。

编辑

希望这能让它更清晰

表

Id (int) - Pk
UserId (int)
Activity (varchar(20))
dDate (datetime)

数据

1 123 'Visited Home Page' 2018-02-23 05:15:14
2 123 'Visited Page 1' 2018-02-23 04:15:15
3 456 'Visited Home Page' 2018-02-23 04:15:16
4 456 'Visited Page 1' 2018-02-23 04:15:15
5 456 'Visited Page 1' 2018-02-24 04:15:15

预期结果

Count, Activity, dDate
2 'Visited Home Page' 2018-02-23
2 'Visited Page 1' 2018-02-23
1 'Visited Page 1' 2018-02-24
0 'Visited Home Page' 2018-02-24

SELECT Count(Activity) as Count, Activity,  Date(dDate) as d FROM myTable
WHERE Activity IN('Visited Home Page', 'Visited Page 1') AND 
DATE(dDateTime) BETWEEN '2018-01-01' AND '2018-01-31'
GROUP BY Date(dDate), Activity

Current Result

    Count, Activity, dDate
    2 'Visited Home Page' 2018-02-23
    2 'Visited Page 1' 2018-02-23
    1 'Visited Page 1' 2018-02-24

由于没有用户访问主页，所以没有记录任何内容。然而，我总是需要对所有事情有一个结果我放入了 where 子句。

如果我添加另一个“已访问页面 2”，那么结果应该是

Count, Activity, dDate
2 'Visited Home Page' 2018-02-23
2 'Visited Page 1' 2018-02-23
0 'Visited Page 2' 2018-02-23

1 'Visited Page 1' 2018-02-24
0 'Visited Home Page' 2018-02-24
0 'Visited Page 2' 2018-02-24

如果那天没有发生任何事情，我可能不会带回任何结果。虽然我想看看如何完成。

Answer 1

您可以简单地将颜色、myDate 结果与计数结果连接起来，例如:

select distinct a.color, a.myDate , ifnull(t.num_color,0)
from colors a
left join ( 
  select color, myDate, count(*) num_color
  from colors
  group by color, myDate
) t on t.color= a.color AND t.myDate = a.myDate 

例如，对于 Between 子句，您可以

select distinct a.color, a.myDate , ifnull(t.num_color,0)
from colors a
left join ( 
  select color, myDate, count(*) num_color
  from colors
  group by color, myDate
) t on t.color= a.color AND t.myDate = a.myDate 
        and date(a.myDate) between ( '2017-01-01') and ('2018-02-23')

并查看更新后的问题示例

您可以使用带有计数的 sublect 来加入不同的事件和 ddate

select distinct m.activity, date(m.dDate)
from my_table m
left join  (
  select activity,  date(dDate), count(*) as my_count 
  from my_table 
  group by activity,  date(dDate) as my_date
) t on t.activity = m.activity and date(m.dDate) = t.my_date