mysql - sql怎么查询分组后所有组一共有多少行数.

Question

count(),只能统计每组有多少行,我现在想统计每组一共有多少行该怎么做?

SELECT COUNT(1) count,ac.chanceId,act.quantity,act.fullScale
FROM actgamechance ac 
LEFT JOIN actredpacketrain act ON ac.rainId = act.rainId 
WHERE ac.chanceState = 0 AND (NOW()<=ac.expireTime AND NOW()>=ac.effectiveTtime) 
AND ac.userInfoId = '001ecc591eeb4ee180a35b29da89b395'
GROUP BY ac.sources
ORDER BY act.rainLevel DESC
FOR UPDATE

查询为

2 1 50 50
2 3 50 50

变为

2 1 50 50 4(所有的count相加)
2 3 50 50 4(所有的count相加)

怎么实现?

Answer 1

試試使用
http://segmentfault.com/q/1010000004152762

select
c, chanceid, quantity, fullscale, 
@total := @total + c as s
from (
SELECT COUNT(1) count,ac.chanceId,act.quantity,act.fullScale
FROM actgamechance ac 
LEFT JOIN actredpacketrain act ON ac.rainId = act.rainId 
WHERE ac.chanceState = 0 AND (NOW()<=ac.expireTime AND NOW()>=ac.effectiveTtime) 
AND ac.userInfoId = '001ecc591eeb4ee180a35b29da89b395'
GROUP BY ac.sources
) as j, (select @total := 0) as t
ORDER BY act.rainLevel DESC