分组累计结余课程数

【问题】

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

IDNameDate
1Sandy2015-05-06
2Candy2015-05-06
3Sandy2015-05-28
4Candy2015-05-29
5Candy2015-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)ASYY,MONTH(DATE)ASMM,COUNT(*)ASAttended
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)

A1：sql取数

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

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