cardcode status
c000001 w
c000001 w
c000001 w
c000001 w
c000001 w
c000001 w
c000001 w
c000001 L
c000001 L
得到结果如下:
盈利 损失 成功%
7 2 77
注:盈利(W)、损失(L)
1、
select
sum(case when status='w' then 1 else 0 end) as 盈利,
sum(case when status='L' then 1 else 0 end) as 损失,
sum(case when status='w' then 1 else 0 end)*100/count(1) as [成功%],
from tb
group by cardcode
2、
select 盈利=(select sum(case when cardcode=A.cardcode and status='w' then 1 else 0 end) from tb)),损失=(select sum(case when cardcode=a.cardcode and status='l' then 1 else 0 end)),盈利*100.0/(盈利+损失) as 成功% FROM tb A group by A.cardcode