Mysql 查询 - 按年份分组的项目计数，包括 "sub-counts"

Question

我有一个像这样的表“事件”

   id   |  user_id   |   date     |  is_important
---------------------------------------------------
   1    |     3      | 01/02/2012 |       0
   1    |     3      | 01/02/2012 |       1
   1    |     3      | 01/02/2011 |       1
   1    |     3      | 01/02/2011 |       1
   1    |     3      | 01/02/2011 |       0

基本上，我需要得到的是:

(for the user_id=3)

   year   |  count   |   count_importants
--------------------------------------------
  2012    |    2     |          1
  2011    |    3     |          2

我已经尝试过这个:

SELECT YEAR(e1.date) as year,COUNT(e1.id) as count_total, aux.count_importants
FROM events e1 
LEFT JOIN 
(
  SELECT YEAR(e2.date) as year2,COUNT(e2.id) as count_importants
  FROM `events` e2
  WHERE e2.user_id=18 
  AND e2.is_important = 1
  GROUP BY year2
) AS aux ON aux.year2 = e1.year
WHERE e1.user_id=18 
GROUP BY year

但是 mysql 给了我一个错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'aux ON aux.year2 = e1.year WHERE e1.user_id=18 GROUP BY year LIMIT 0, 30' at line 10

我已经没有想法来进行这个查询 u_Uº。是否可以仅使用一个查询来完成此操作？

提前致谢

Answer 1

编辑:我认为我把事情过于复杂化了。难道不能通过一个简单的查询来完成此操作吗？

SELECT 
    YEAR(`year`) AS `year`, 
    COUNT(`id`) AS `count`, 
    SUM(`is_important`) AS `count_importants`
FROM `events`
WHERE user_id = 18
GROUP BY YEAR(`year`)

<小时/>

这是添加摘要的大解决方案:)

考虑使用MySQL GROUP BY ROLLUP 。这基本上会完成与普通 GROUP BY 类似的工作，但也会添加摘要行。

在下面的示例中，您会看到 2000 年芬兰的两条记录，分别为 1500 英镑和 100 英镑，然后是包含 NULL 产品且总值(value)为 1600 英镑的行。它还为按分组依据的每个维度添加 NULL 汇总行。

来自手册:

SELECT year, country, product, SUM(profit)
FROM sales
GROUP BY year, country, product WITH ROLLUP

+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

这是一个特别适合您的情况的示例:

SELECT year(`date`) AS `year`, COUNT(`id`) AS `count`, SUM(`is_important`) AS `count_importants`
FROM new_table
GROUP BY year(`date`) WITH ROLLUP;