【Stef-SQL查询】查询求百分比,求大侠们帮助
表A

name status

A1 Pass
A2 Fail
A3 Pass
A2 Pass
A2 Pass
A4 Pass
A5 Pass
A2 Fail
A5 Pass
A6 Pass
A2 Fail

如何写sql语句，得出以下结果：
name StatusTime Pass Pass-Ratio

A2 5 2 40%

[解决办法]

SQL code

--> --> (Roy)生成 if not object_id('Tempdb..#') is null    drop table #GoCreate table #([name] nvarchar(2),[status] nvarchar(4))Insert #select N'A1',N'Pass' union allselect N'A2',N'Fail' union allselect N'A3',N'Pass' union allselect N'A2',N'Pass' union allselect N'A2',N'Pass' union allselect N'A4',N'Pass' union allselect N'A5',N'Pass' union allselect N'A2',N'Fail' union allselect N'A5',N'Pass' union allselect N'A6',N'Pass' union allselect N'A2',N'Fail'GoSelect [name],StatusTime=COUNT(1),Pass=sum(case when [status]=N'Pass' then 1 else 0 end),[Pass-Ratio]=str(sum(case when [status]=N'Pass' then 1 else 0 end)*100.0/COUNT(1),6,2)+'%'from # group by [name]/*name    StatusTime    Pass    Pass-RatioA1    1    1    100.00%A2    5    2     40.00%A3    1    1    100.00%A4    1    1    100.00%A5    2    2    100.00%A6    1    1    100.00%*/
[解决办法]
SQL code
SELECT name,COUNT(*) as statusTime,SUM(case when status='pass' then 1 else 0 end) as pass,CONVERT(varchar, CONVERT(float,SUM(case when status='pass' then 1 else 0 end))/COUNT(*)*100)+'%' as PassRatio  FROM A  GROUP BY name