从连接表中按年查询数据

I have one table named colors and I need to create a query that returns how many unique colors are used each year based on the date in my other table, programs. It looks like this:

我有一个名为colors的表,我需要创建一个查询,根据我的其他表程序中的日期,返回每年使用的唯一颜色数。它看起来像这样:

colors
+----+----------+
| id |   name   |
+----+----------+
|  1 | blue     |
|  2 | yellow   |
+----+----------+

programs
+----+------------+
| id |    date    |
+----+------------+
|  1 | 2016-01-08 |
|  2 | 2016-02-08 |
|  3 | 2017-02-08 |
+----+------------+

programs_colors
+------------+----------+
| program_id | color_id |
+------------+----------+
|          1 |        1 |
|          1 |        1 |
|          2 |        2 |
|          2 |        1 |
|          3 |        1 |
|          3 |        1 |
+------------+----------+

I have tried with this:

我试过这个:

SELECT min(date), count(*) FROM (
  SELECT min(date) AS date FROM programs_colors INNER JOIN programs ON programs.id = program_id GROUP BY color_id
) AS a GROUP BY year(date)

min(date):      count(*):
2016-01-08      2
2017-01-08      0

But the above query groups my colors as a whole, but I need them grouped by each year

但是上面的查询将我的颜色整体分组,但我需要按年份分组

Expected result:

min(date):      count(*):
2016-01-08      2
2017-01-08      1

I hope my question makes sense

我希望我的问题有道理

2 个解决方案

#1

SELECT  min(date), count(distinct color_id) 
FROM    programs_colors 
INNER   JOIN programs 
ON      programs.id = program_id 
GROUP   BY year(date);

#2

If I understand right, you might need to do something like this

如果我理解正确,你可能需要做这样的事情

SELECT min(date), sum(count) FROM (
  SELECT min(date) AS date, count(*) as count FROM programs_colors INNER JOIN programs ON programs.id = program_id GROUP BY color_id
) AS a GROUP BY year(date)