id type user grade
1 第一项 张 A
2 第一项 王 B
3 第一项 李 A
4 第一项 赵 C
5 第二项 张 C
6 第二项 王 B
7 第二项 李 B
怎么变成这种?
type Pcount gradeA gradeB gradeC gradeD
第一项 4 50% 25% 25% 0%
第二项 4 0% 50% 25% 0%
12 个解决方案
#1
select type,sum(1) as pcount,(case grade when 'a' then 1 else 0 end)/sum(1) as gradeA,(case grade when 'b' then 1 else 0 end)/sum(1) as gradeB,(case grade when 'c' then 1 else 0 end)/sum(1) as gradeC,(case grade when 'd' then 1 else 0 end)/sum(1) as gradeD from table1 group by type
#2
select type,sum(1) as pcount,
cast(100*(case grade when 'a' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeA,
cast(100*(case grade when 'b' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeB,
cast(100*(case grade when 'c' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeC,
cast(100*(case grade when 'd' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeD
from table1
group by type
#3
搞定,楼主把分给偶,楼上是我的马甲.
#4
--帮1楼改改格式
select type,sum(1) as pcount,
cast(100*(case grade when 'a' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeA,
cast(100*(case grade when 'b' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeB,
cast(100*(case grade when 'c' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeC,
cast(100*(case grade when 'd' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeD
from table1 group by type
#5
呵呵
#6
晕,漏了个地方,更正如下:
declare @t table(id int,[type] varchar(8),[user] varchar(8),grade varchar(2))
insert into @t
select 1,'第一项','张','A'
union select 2,'第一项','王','B'
union select 3,'第一项','李','A'
union select 4,'第一项','赵','C'
union select 5,'第二项','张','C'
union select 6,'第二项','王','B'
union select 7,'第二项','李','B'
select * from @t
select [type],sum(1) as pcount,
cast(100*sum((case grade when 'a' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeA,
cast(100*sum((case grade when 'b' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeB,
cast(100*sum((case grade when 'c' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeC,
cast(100*sum((case grade when 'd' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeD
from @t
group by type
order by type
(7 行受影响)
id type user grade
----------- -------- -------- -----
1 第一项 张 A
2 第一项 王 B
3 第一项 李 A
4 第一项 赵 C
5 第二项 张 C
6 第二项 王 B
7 第二项 李 B
(7 行受影响)
type pcount gradeA gradeB gradeC gradeD
-------- ----------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
第二项 3 0% 66% 33% 0%
第一项 4 50% 25% 25% 0%
(2 行受影响)
#7
声明:1,2楼的有问题,更正如6楼所示.
#8
ss剩的1%怎么处理?我早就想问下了
#9
declare @t table(id int,[type] varchar(8),[user] varchar(8),grade varchar(2))
insert into @t
select 1,'第一项','张','A'
union select 2,'第一项','王','B'
union select 3,'第一项','李','A'
union select 4,'第一项','赵','C'
union select 5,'第二项','张','C'
union select 6,'第二项','王','B'
union select 7,'第二项','李','B'
select * from @t
select [type],sum(1) as pcount,
cast(cast(round(100*sum((case grade when 'a' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeA,
cast(cast(round(100*sum((case grade when 'b' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeB,
cast(cast(round(100*sum((case grade when 'c' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeC,
cast(cast(round(100*sum((case grade when 'd' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeD
from @t
group by type
order by type
(7 行受影响)
id type user grade
----------- -------- -------- -----
1 第一项 张 A
2 第一项 王 B
3 第一项 李 A
4 第一项 赵 C
5 第二项 张 C
6 第二项 王 B
7 第二项 李 B
(7 行受影响)
type pcount gradeA gradeB gradeC gradeD
-------- ----------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
第二项 3 0% 67% 33% 0%
第一项 4 50% 25% 25% 0%
(2 行受影响)
#10
列 table1.grade 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
但是一加到group by 里,就变成这个样子了
type Pcount gradeA gradeB gradeC gradeD
第一项 2 50% 0% 0% 0%
第一项 1 0% 25% 0% 0%
第一项 1 0% 0% 25% 0%
但是一加到group by 里,就变成这个样子了
type Pcount gradeA gradeB gradeC gradeD
第一项 2 50% 0% 0% 0%
第一项 1 0% 25% 0% 0%
第一项 1 0% 0% 25% 0%
#11
好的,我再按最新的试试
#12
谢谢,青锋-SS
太棒了!马上结帖
太棒了!马上结帖
#1
select type,sum(1) as pcount,(case grade when 'a' then 1 else 0 end)/sum(1) as gradeA,(case grade when 'b' then 1 else 0 end)/sum(1) as gradeB,(case grade when 'c' then 1 else 0 end)/sum(1) as gradeC,(case grade when 'd' then 1 else 0 end)/sum(1) as gradeD from table1 group by type
#2
select type,sum(1) as pcount,
cast(100*(case grade when 'a' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeA,
cast(100*(case grade when 'b' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeB,
cast(100*(case grade when 'c' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeC,
cast(100*(case grade when 'd' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeD
from table1
group by type
#3
搞定,楼主把分给偶,楼上是我的马甲.
#4
--帮1楼改改格式
select type,sum(1) as pcount,
cast(100*(case grade when 'a' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeA,
cast(100*(case grade when 'b' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeB,
cast(100*(case grade when 'c' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeC,
cast(100*(case grade when 'd' then 1 else 0 end)/sum(1) as varchar)+'%' as gradeD
from table1 group by type
#5
呵呵
#6
晕,漏了个地方,更正如下:
declare @t table(id int,[type] varchar(8),[user] varchar(8),grade varchar(2))
insert into @t
select 1,'第一项','张','A'
union select 2,'第一项','王','B'
union select 3,'第一项','李','A'
union select 4,'第一项','赵','C'
union select 5,'第二项','张','C'
union select 6,'第二项','王','B'
union select 7,'第二项','李','B'
select * from @t
select [type],sum(1) as pcount,
cast(100*sum((case grade when 'a' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeA,
cast(100*sum((case grade when 'b' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeB,
cast(100*sum((case grade when 'c' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeC,
cast(100*sum((case grade when 'd' then 1 else 0 end))/sum(1) as varchar)+'%' as gradeD
from @t
group by type
order by type
(7 行受影响)
id type user grade
----------- -------- -------- -----
1 第一项 张 A
2 第一项 王 B
3 第一项 李 A
4 第一项 赵 C
5 第二项 张 C
6 第二项 王 B
7 第二项 李 B
(7 行受影响)
type pcount gradeA gradeB gradeC gradeD
-------- ----------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
第二项 3 0% 66% 33% 0%
第一项 4 50% 25% 25% 0%
(2 行受影响)
#7
声明:1,2楼的有问题,更正如6楼所示.
#8
ss剩的1%怎么处理?我早就想问下了
#9
declare @t table(id int,[type] varchar(8),[user] varchar(8),grade varchar(2))
insert into @t
select 1,'第一项','张','A'
union select 2,'第一项','王','B'
union select 3,'第一项','李','A'
union select 4,'第一项','赵','C'
union select 5,'第二项','张','C'
union select 6,'第二项','王','B'
union select 7,'第二项','李','B'
select * from @t
select [type],sum(1) as pcount,
cast(cast(round(100*sum((case grade when 'a' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeA,
cast(cast(round(100*sum((case grade when 'b' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeB,
cast(cast(round(100*sum((case grade when 'c' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeC,
cast(cast(round(100*sum((case grade when 'd' then 1 else 0 end))/sum(1.0),0) as int) as varchar)+'%' as gradeD
from @t
group by type
order by type
(7 行受影响)
id type user grade
----------- -------- -------- -----
1 第一项 张 A
2 第一项 王 B
3 第一项 李 A
4 第一项 赵 C
5 第二项 张 C
6 第二项 王 B
7 第二项 李 B
(7 行受影响)
type pcount gradeA gradeB gradeC gradeD
-------- ----------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
第二项 3 0% 67% 33% 0%
第一项 4 50% 25% 25% 0%
(2 行受影响)
#10
列 table1.grade 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
但是一加到group by 里,就变成这个样子了
type Pcount gradeA gradeB gradeC gradeD
第一项 2 50% 0% 0% 0%
第一项 1 0% 25% 0% 0%
第一项 1 0% 0% 25% 0%
但是一加到group by 里,就变成这个样子了
type Pcount gradeA gradeB gradeC gradeD
第一项 2 50% 0% 0% 0%
第一项 1 0% 25% 0% 0%
第一项 1 0% 0% 25% 0%
#11
好的,我再按最新的试试
#12
谢谢,青锋-SS
太棒了!马上结帖
太棒了!马上结帖