34,576
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int,name nvarchar(20),ok nvarchar(4))
go
insert tb
select 1,'张三','成功' union all
select 2,'李四','成功' union all
select 3,'张三','失败' union all
select 4,'黎明','成功' union all
select 5,'刘备','失败'
go
select name as 姓名,LTRIM(s)+'%' as 成功率,RANK()over(order by s desc)排名 from(
select name,SUM(case when ok='成功' then 100 else 0 end)/COUNT(*)s from tb group by name
)t
/*
姓名 成功率 排名
-------------------- ------------- --------------------
黎明 100% 1
李四 100% 1
张三 50% 3
刘备 0% 4
(4 行受影响)
*/
go
drop table tb
SELECT [NAME] ,
SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) ,
DENSE_RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) DESC ) 名次1 ,
RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) DESC ) 名次2
FROM TB
GROUP BY [NAME]
select
name,
成功率,
row_number()over(order by 成功率 desc) as [AS]
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tb
group by
name
)t
select
name,
成功率,
row_number()over(order by 成功率 desc) as AS
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tb
group by
name
)t
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%'
from
tb
group by
name