分组累计结余课程数

【问题】


I want to list grouped by month and   the days remaining to complete the course in the next column. The Course has   10 days.

Example data

ID Name Date
1 Sandy 2015-05-06
2 Candy 2015-05-06
3 Sandy 2015-05-28
4 Candy 2015-05-29
5 Candy 2015-06-01
 

Preferred output

| Name | Month | Attended | Remaining|
| Sandy| May   |   2      |     8    |
| Candy| May   |   2      |     8    |
| Candy| June  |   1      |     7    |
 

If I use GROUP BY DATE_FORMAT(date,   '%Y%m'), Name and try to do the calculation it does not work.

正确答案:

两句SQL:
SELECT Name, YEAR(DATE) AS YY, MONTH(DATE) AS MM, COUNT(*) AS Attended
 

FROM test

GROUP BY Name, YEAR(DATE), MONTH(DATE)

SET @currcount = NULL, @currvalue = NULL;

SELECT Name

    , YY

    , MM

    , Attended

    , @currcount   := IF(@currvalue = Name, @currcount, 10) - Attended AS Remaining

    , @currvalue   := Name AS dontcare

FROM (

    SELECT Name,   YEAR(DATE) AS YY, MONTH(DATE) AS MM, COUNT(*) AS Attended

    FROM test

    GROUP BY Name,   YEAR(DATE), MONTH(DATE)

) AS whatever

ORDER BY Name, YY, MM

 

【回答】

mysql有变量,思路跟上面这个sql一样,再看看SPL分组后跨行计算是不是更直观:

 


A

1

$select * from tb

2

=A1.groups(Name,left(string(Date),7):Month;count():Attended)

3

=A2.derive(if(Name==Name[-1],Remaining[-1],10)-Attended:Remaining)

 

A1sql取数

A2:按人,月份分组,统计每个人当月上课次数

A3:增加Remaining列,列值为每人当月还剩余课程数