【Stef-SQL查询】查询求百分比,求大侠们相助_Sql Server

【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 codeSELECT 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