mysql - 如何在不重复行的情况下计算每个月？

Question

我试图在查询中创建所有月份，而不是使用多行。

信息如下:

CREATE TABLE quotes (
  id INT,
  created_at DATE,
  num_policy INT);

INSERT INTO quotes VALUES
 (   1,     '2014-01-01',1234),      
 (   2,     '2014-02-06',5678),  
 (   3,     '2013-04-24',9123), 
 (   4,     '2013-05-24',4567),
 (   5,     '2014-06-20',8912);

我试过这个查询:http://sqlfiddle.com/#!2/2f4f51/5

SET @year := 2014;

SELECT count(*) AS jan FROM `quotes` WHERE (created_at BETWEEN CONCAT( @year, "-01-01") AND CONCAT( @year, "-01-31")) ;
SELECT count(*) AS feb FROM `quotes` WHERE (created_at BETWEEN CONCAT( @year, "-02-01") AND CONCAT( @year, "-02-29")) ;
......

SELECT count(*) AS dem FROM `quotes` WHERE (created_at BETWEEN CONCAT( @year, "-12-01") AND CONCAT( @year, "-12-31")) ;

我试图在一行中显示所有月份

  |jan|  |feb|  |mar|  |apr| ......
    1       1      0     0

有人可以帮助我吗？

我会感谢所有的帮助。

Answer 1

这可以起到神奇的作用:

SELECT CASE WHEN MonthSET @year := 2014;

SELECT SUM(CASE WHEN Month(created_at) = 1 THEN 1 ELSE 0 END) AS jan,
 SUM(CASE WHEN Month(created_at) = 2 THEN 1 ELSE 0 END) AS feb,
 SUM(CASE WHEN Month(created_at) = 3 THEN 1 ELSE 0 END) AS mar,
 SUM(CASE WHEN Month(created_at) = 4 THEN 1 ELSE 0 END) AS apr,
 SUM(CASE WHEN Month(created_at) = 5 THEN 1 ELSE 0 END) AS may,
 SUM(CASE WHEN Month(created_at) = 6 THEN 1 ELSE 0 END) AS jun,
 SUM(CASE WHEN Month(created_at) = 7 THEN 1 ELSE 0 END) AS jul,
 SUM(CASE WHEN Month(created_at) = 8 THEN 1 ELSE 0 END) AS aug,
 SUM(CASE WHEN Month(created_at) = 9 THEN 1 ELSE 0 END) AS sep,
 SUM(CASE WHEN Month(created_at) = 10 THEN 1 ELSE 0 END) AS oct,
 SUM(CASE WHEN Month(created_at) = 11 THEN 1 ELSE 0 END) AS nov,
 SUM(CASE WHEN Month(created_at) = 12 THEN 1 ELSE 0 END) AS `dec`
FROM `quotes` 
WHERE YEAR(created_at) = @year

链接到 SQL fiddle : http://sqlfiddle.com/#!2/2f4f51/13/1