分组累计结余课程数

【问题】

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列,列值为每人当月还剩余课程数

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值