mysql - 按年份计算新的和返回的项目

Question

继问题“Select new or returning items for a specified year”之后，我现在想创建关于每年有多少新品或返回品的统计数据。这是我的示例表:

+---------------------+----------+
| date                | item     |
+---------------------+----------+
| 2008-11-30 11:15:59 | Plums    |
| 2012-11-08 19:42:37 | Lemons   |
| 2013-01-30 18:58:07 | Apples   |
| 2013-02-12 13:44:45 | Pears    |
| 2014-06-08 11:46:48 | Apples   |
| 2014-09-01 20:28:03 | Oranges  |
+---------------------+----------+

我正在寻找一个查询，该查询将返回前 年未出现的项目数量，以及前几年已经出现的项目数量。

结果应该是这样的:

Year   New Items   Returning Items
2008   1           0
2012   1           0
2013   2           0
2014   1           1

如何实现？

Answer 1

好吧，使用您链接到的查询，我想出了以下 ( SQL Fiddle):

SELECT m1.UniqYear,  
  CASE WHEN m2.NewItems IS NULL THEN 0 ELSE m2.NewItems END AS NewItems,
  CASE WHEN m3.ReturningItems IS NULL THEN 0 ELSE m3.ReturningItems END AS ReturningItems
FROM 
(
  SELECT DISTINCT YEAR(s.date) AS UniqYear 
  FROM MyTable s
) m1 LEFT JOIN 
(
  SELECT YEAR(s1.date) AS Year, COUNT(*) AS NewItems
  FROM MyTable s1
    LEFT JOIN MyTable s2 ON s1.item = s2.item AND YEAR(s2.date) < YEAR(s1.date)
  WHERE s2.date IS NULL
  GROUP BY YEAR(s1.date)
) m2 ON m1.UniqYear = m2.Year
LEFT JOIN 
(
  SELECT YEAR(s3.date) AS Year, COUNT(*) AS ReturningItems
  FROM MyTable s3
    INNER JOIN MyTable s4 ON s3.item = s4.item
  WHERE YEAR(s4.date) < YEAR(s3.date)
  GROUP BY YEAR(s3.date)
) m3 ON m1.UniqYear = m3.Year
ORDER BY m1.UniqYear;

我正在使用 CASE 语句返回 0 而不是 null。第一个子查询 (m1) 用于获取不同的年份。第二个子查询 (m2) 用于获取New Items 计数。第三个子查询 (m3) 用于获取Returning Items 计数。请注意，我必须添加一个 group by 语句来获取每年的计数。我不得不将 LEFT JOIN 编辑 m2 和 m3 到 m1 因为你并不总是会有一个 NewItems 计数或每年的返回。