表
person_oid check_name
a 优秀
b 优秀
a 良好
b 差
a 优秀
b 良好
a 优秀
b 差我想得到
person_oid note
a 优秀占总数:75%,良好占总数:25%,差点总数:0%
b 优秀占总数:25%,良好占总数:25%,差点总数:50%
person_oid check_name
a 优秀
b 优秀
a 良好
b 差
a 优秀
b 良好
a 优秀
b 差我想得到
person_oid note
a 优秀占总数:75%,良好占总数:25%,差点总数:0%
b 优秀占总数:25%,良好占总数:25%,差点总数:50%
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([person_oid] nvarchar(1),[check_name] nvarchar(2))
Insert #T
select N'a',N'优秀' union all
select N'b',N'优秀' union all
select N'a',N'良好' union all
select N'b',N'差' union all
select N'a',N'优秀' union all
select N'b',N'良好' union all
select N'a',N'优秀' union all
select N'b',N'差'
Go
select
[person_oid],
[差]=str(sum(case when check_name=N'差' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%',
[良好]=str(sum(case when check_name=N'良好' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%',
[优秀]=str(sum(case when check_name=N'优秀' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
from #T
group by [person_oid]
/*person_oid 差 良好 优秀
a 0.00% 25.00% 75.00%
b 50.00% 25.00% 25.00%
*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([person_oid] nvarchar(1),[check_name] nvarchar(2))
Insert #T
select N'a',N'优秀' union all
select N'b',N'优秀' union all
select N'a',N'良好' union all
select N'b',N'差' union all
select N'a',N'优秀' union all
select N'b',N'良好' union all
select N'a',N'优秀' union all
select N'b',N'差'
Go
select
[person_oid],
[差]='差点总数:'+str(sum(case when check_name=N'差' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%',
[良好]='良好占总数:'+str(sum(case when check_name=N'良好' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%',
[优秀]='优秀占总数:'+str(sum(case when check_name=N'优秀' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
from #T
group by [person_oid]
/*
person_oid 差 良好 优秀
a 差点总数: 0.00% 良好占总数:25.00% 优秀占总数:75.00%
b 差点总数:50.00% 良好占总数:25.00% 优秀占总数:25.00%
*/
[person_oid],
note='差点总数:'+str(sum(case when check_name=N'差' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'+
+'良好占总数:'+str(sum(case when check_name=N'良好' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
+'优秀占总数:'+str(sum(case when check_name=N'优秀' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
from #T
group by [person_oid]
select
[person_oid],
[Note]='优秀占总数:'+str(sum(case when check_name=N'优秀' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
+
'良好占总数:'+str(sum(case when check_name=N'良好' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'
+
'差点总数:'+str(sum(case when check_name=N'差' then 1 else 0 end)*100.0/COUNT(1),5,2)+'%'from #T
group by [person_oid]
/*
person_oid Note
a 优秀占总数:75.00%良好占总数:25.00%差点总数: 0.00%
b 优秀占总数:25.00%良好占总数:25.00%差点总数:50.00%
*/