mysql行数倒计时和动态行数

Question

我相信它可以通过临时表/存储过程来解决，但万一它可以通过单个 SQL 语句来完成。

目标:列出按年份倒数的所有行，但每年的行数不同。行可以按日期排序

结果臂至:

|-Count Down-|-Date-------|
| 3          | 2013-01-01 | <- Start with number of Row of each year
| 2          | 2013-03-15 |
| 1          | 2013-06-07 |
| 5          | 2014-01-01 | <- Start with number of Row of each year
| 4          | 2014-03-17 |
| 3          | 2014-07-11 |
| 2          | 2014-08-05 |
| 1          | 2014-11-12 |

SQL:

Select @row_number:=@row_number-1 AS CountDown, Date
FROM table JOIN 
    (Select @row_number:=COUNT(*), year(date) FROM table GROUP BY year(date))

有什么解决办法吗？

Answer 1

按年份获取计数的子查询需要返回年份，因此可以将其与主表连接以获取倒计时的起始数字。并且您需要检测年份何时发生变化，因此您需要另一个变量。

SELECT @row_number := IF(YEAR(d.Date) = @prevYear, @row_number-1, y.c) AS CountDown,
       d.Date, @prevYear := YEAR(d.Date)
FROM (SELECT Date 
      FROM Table1 
      ORDER BY Date) AS d
JOIN 
    (Select count(*) AS c, year(date) AS year
     FROM Table1 
     GROUP BY year(date)) AS y
ON YEAR(d.Date) = y.year
CROSS JOIN (SELECT @prevYear := NULL) AS x

DEMO