分组累计结余课程数
【问题】
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) |
A1:sql取数
A2:按人,月份分组,统计每个人当月上课次数
A3:增加Remaining列,列值为每人当月还剩余课程数