mysql计算几个月内的叶子数

Question

我有一张 table

Name    duration    date_leave_from date_leave_to
John    1           2015-03-01      2015-03-01
Doe     .5          2015-03-02      2015-03-02
John    .5          2015-03-02      2015-03-02
Doe     1           2015-01-03      2015-01-03
Doe     1           2015-02-04      2015-02-04

我想获取以下格式的数据:

Name      ||  Jan     ||   Feb    ||  March     ||  April .. to DEC
John      ||  1       ||   0      ||  1.5       ||  0  
Doe       ||  0       ||   1      ||  .5        ||  0 

Answer 1

你可以这样做:

SELECT name,
   SUM(CASE WHEN MONTH(from_date) = '1' THEN duration else 0 END) AS Jan,
   SUM(CASE WHEN MONTH(from_date) = '2' THEN duration else 0 END) AS Feb,
   SUM(CASE WHEN MONTH(from_date) = '3' THEN duration else 0 END) AS March,
   SUM(CASE WHEN MONTH(from_date) = '4' THEN duration else 0 END) AS April,
   SUM(CASE WHEN MONTH(from_date) = '5' THEN duration else 0 END) AS May,
   SUM(CASE WHEN MONTH(from_date) = '6' THEN duration else 0 END) AS Jun,
   SUM(CASE WHEN MONTH(from_date) = '7' THEN duration else 0 END) AS Jul,
   SUM(CASE WHEN MONTH(from_date) = '8' THEN duration else 0 END) AS Aug,
   SUM(CASE WHEN MONTH(from_date) = '9' THEN duration else 0 END) AS Sep,
   SUM(CASE WHEN MONTH(from_date) = '10' THEN duration else 0 END) AS Oct,
   SUM(CASE WHEN MONTH(from_date) = '11' THEN duration else 0 END) AS Nov,
   SUM(CASE WHEN MONTH(from_date) = '12' THEN duration else 0 END) AS   December
FROM test
GROUP BY name

这是fiddle