我有一个表,其中列出了现场巡演的日期,我想每年对它们进行分组,但也要创建一个新列,其中包含今年的剩余日期:
year | count /*of remaining dates*/
--------------------
2014 | 12
2013 | 0
2012 | 0
2010 | 0
我试过这个,但不起作用:
SELECT YEAR(`date`) AS year, COUNT(`date` > NOW()) AS count
FROM live
GROUP BY YEAR(`date`)
ORDER BY YEAR(`date`) DESC
这样试试
SELECT YEAR(`date`) AS year, SUM(`date` > CURDATE()) AS count
FROM live
GROUP BY YEAR(`date`) DESC
样本输出:
|年份|计数||------|-------||2013 | 2||2012 |0|
这是SQLFiddle演示