求一条查询语句

Question

create table tb(班级名称 varchar(10),         等级 varchar(10))
insert into tb values('一班' ,             'C') 
insert into tb values('一班' ,             'C') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'B') 
insert into tb values('二班' ,             'D') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'D')
goselect 班级名称 班级, 
       count(1) 参评条数,
       cast((select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'A')*100.0 / count(1) as decimal(18,2)) [A人数] ,
       (select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'A') [A人数],
       cast((select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'B')*100.0 / count(1) as decimal(18,2)) [B人数] ,
       (select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'B') [B人数],
       cast((select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'C')*100.0 / count(1) as decimal(18,2)) [C人数] ,
       (select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'C') [C人数],
       cast((select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'D')*100.0 / count(1) as decimal(18,2)) [D人数] ,
       (select count(1) from tb where 班级名称 = t.班级名称 and 等级 = 'D') [D人数]
from tb t
group by 班级名称drop table tb /*
班级         参评条数        A人数                  A人数         B人数                  B人数         C人数                  C人数         D人数                  D人数         
---------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- 
二班         6           50.00                3           16.67                1           .00                  0           33.33                2
一班         6           .00                  0           .00                  0           33.33                2           66.67                4（所影响的行数为 2 行）*/

Answer 1

select 班级名称 , 
A比例=sum(case 等级 when 'A' then 1 else 0 end)*100.0/(select count(1) from tb), 
A人数=sum(case 等级 when 'A' then 1 else 0 end),
B比例=sum(case 等级 when 'B' then 1 else 0 end)*100.0/(select count(1) from tb), 
B人数=sum(case 等级 when 'B' then 1 else 0 end),
C比例=sum(case 等级 when 'C' then 1 else 0 end)*100.0/(select count(1) from tb), 
C人数=sum(case 等级 when 'C' then 1 else 0 end),
D比例=sum(case 等级 when 'D' then 1 else 0 end)*100.0/(select count(1) from tb), 
D人数=sum(case 等级 when 'D' then 1 else 0 end)
from tb 
group by 班级名称 order by 班级名称

Answer 2

create table tb(班级名称 varchar(10),         等级 varchar(10))
insert into tb values('一班' ,             'C') 
insert into tb values('一班' ,             'C') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('一班' ,             'D') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'B') 
insert into tb values('二班' ,             'D') 
insert into tb values('二班' ,             'A') 
insert into tb values('二班' ,             'D')
go
select 班级名称 ,count(1) [参评条数（记录数）], 
A比例=cast(sum(case 等级 when 'A' then 1 else 0 end)*100.0/(select count(1) from tb where 班级名称=a.班级名称) as decimal(18,2)), 
A人数=sum(case 等级 when 'A' then 1 else 0 end),
B比例=cast(sum(case 等级 when 'B' then 1 else 0 end)*100.0/(select count(1) from tb where 班级名称=a.班级名称) as decimal(18,2)), 
B人数=sum(case 等级 when 'B' then 1 else 0 end),
C比例=cast(sum(case 等级 when 'C' then 1 else 0 end)*100.0/(select count(1) from tb where 班级名称=a.班级名称) as decimal(18,2)), 
C人数=sum(case 等级 when 'C' then 1 else 0 end),
D比例=cast(sum(case 等级 when 'D' then 1 else 0 end)*100.0/(select count(1) from tb where 班级名称=a.班级名称) as decimal(18,2)), 
D人数=sum(case 等级 when 'D' then 1 else 0 end)
from tb a
group by 班级名称 order by 班级名称
/*
班级名称       参评条数（记录数）   A比例                  A人数         B比例                  B人数         C比例                  C人数         D比例                  D人数         
---------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- 
二班         6           50.00                3           16.67                1           .00                  0           33.33                2
一班         6           .00                  0           .00                  0           33.33                2           66.67                4（所影响的行数为 2 行）
*/
drop table tb

Answer 3

IF NOT OBJECT_ID('TB') IS NULL  DROP TABLE TB
create table tb(ClassName varchar(10),[Type] varchar(10))
insert into tb values('一班','C') 
insert into tb values('一班','C') 
insert into tb values('一班','D') 
insert into tb values('一班','D') 
insert into tb values('一班','D') 
insert into tb values('一班','D') 
insert into tb values('二班','A') 
insert into tb values('二班','A') 
insert into tb values('二班','B') 
insert into tb values('二班','D') 
insert into tb values('二班','A') 
insert into tb values('二班','D')
go
--班级  参评条数（记录数）    A比例(A等级，占本班人数的百分比)  A人数    B比例  B人数    C比例  C人数    D比例  D人数 
select ClassName,count(ClassName),
sum(case when [Type]='A' then 1 else 0 end)/count(ClassName) 'A比例',
sum(case when [Type]='A' then 1 else 0 end) 'A人数',
sum(case when [Type]='B' then 1 else 0 end)/count(ClassName) 'B比例',
sum(case when [Type]='B' then 1 else 0 end) 'B人数',
sum(case when [Type]='C' then 1 else 0 end)/count(ClassName) 'C比例',
sum(case when [Type]='C' then 1 else 0 end) 'C人数',
sum(case when [Type]='D' then 1 else 0 end)/count(ClassName) 'D比例',
sum(case when [Type]='D' then 1 else 0 end) 'D人数'
from tb group by ClassName
ClassName              A比例         A人数         B比例         B人数         C比例         C人数         D比例         D人数
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
二班         6           0           3           0           1           0           0           0           2
一班         6           0           0           0           0           0           2           0           4(2 行受影响)

Answer 4

搞错啦
更正下
select ClassName,count(ClassName),
cast(sum(case when [Type]='A' then 1 else 0 end)*100.0/(select count(1) from tb where ClassName=b.ClassName) as decimal(18,2)) 'A比例',
sum(case when [Type]='A' then 1 else 0 end) 'A人数',
cast(sum(case when [Type]='B' then 1 else 0 end)*100.0/(select count(1) from tb where ClassName=b.ClassName) as decimal(18,2)) 'B比例',
sum(case when [Type]='B' then 1 else 0 end) 'B人数',
cast(sum(case when [Type]='C' then 1 else 0 end)*100.0/(select count(1) from tb where ClassName=b.ClassName) as decimal(18,2)) 'C比例',
sum(case when [Type]='C' then 1 else 0 end) 'C人数',
cast(sum(case when [Type]='D' then 1 else 0 end)*100.0/(select count(1) from tb where ClassName=b.ClassName) as decimal(18,2)) 'D比例',
sum(case when [Type]='D' then 1 else 0 end) 'D人数'
from tb b group by ClassNameClassName              A比例                                     A人数         B比例                                     B人数         C比例                                     C人数         D比例                                     D人数
---------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- -----------
二班         6           50.00                                   3           16.67                                   1           0.00                                    0           33.33                                   2
一班         6           0.00                                    0           0.00                                    0           33.33                                   2           66.67                                   4(2 行受影响)

Answer 5

SELECT A.班级名称,总人数,
A比例=MAX(CONVERT(DECIMAL(10,2),CASE 等级 WHEN 'A' THEN 人数*1.0/总人数 ELSE 1.0*0 END)),
A人数=MAX(CASE 等级 WHEN 'A' THEN 人数 ELSE 0 END),
B比例=MAX(CONVERT(DECIMAL(10,2),CASE 等级 WHEN 'B' THEN 人数*1.0/总人数 ELSE 1.0*0 END)),
B人数=MAX(CASE 等级 WHEN 'B' THEN 人数 ELSE 0 END),
C比例=MAX(CONVERT(DECIMAL(10,2),CASE 等级 WHEN 'C' THEN 人数*1.0/总人数 ELSE 1.0*0 END)),
C人数=MAX(CASE 等级 WHEN 'C' THEN 人数 ELSE 0 END),
D比例=MAX(CONVERT(DECIMAL(10,2),CASE 等级 WHEN 'D' THEN 人数*1.0/总人数 ELSE 1.0*0 END)),
D人数=MAX(CASE 等级 WHEN 'D' THEN 人数 ELSE 0 END)
FROM 
(SELECT 班级名称,等级,人数=COUNT(1) FROM TB GROUP BY 班级名称,等级)A
LEFT JOIN (
SELECT 班级名称,总人数=COUNT(1) FROM TB GROUP BY 班级名称) B
  ON A.班级名称=B.班级名称
GROUP BY A.班级名称,B.总人数
--或　动态写法　DECLARE @s nvarchar(4000)
SET @s='SELECT A.班级名称,总人数'SELECT @s=@s+','+QUOTENAME(等级)
+N'=SUM(CASE 等级 WHEN '+QUOTENAME(等级,N'''')
+N' THEN 人数 ELSE 0 END)'+
 +','+QUOTENAME(等级+'比例')
+N'=MAX(CASE 等级 WHEN '+QUOTENAME(等级,N'''')
+N' THEN CONVERT(DECIMAL(10,2),人数*100.0/总人数) ELSE 0*1.0 END)'+CHAR(13)FROM tb
GROUP BY 班级名称,等级EXEC (@s+' FROM (
SELECT 班级名称,等级,人数=COUNT(1) FROM TB GROUP BY 班级名称,等级)A
LEFT JOIN (
SELECT 班级名称,总人数=COUNT(1) FROM TB GROUP BY 班级名称) B
  ON A.班级名称=B.班级名称
GROUP BY A.班级名称,总人数')/*班级名称 总人数         A比例          A人数         B比例          B人数         C比例          C人数         D比例          D人数         
---- ----------- ------------ ----------- ------------ ----------- ------------ ----------- ------------ ----------- 
二班   6           .50          3           .17          1           .00          0           .33          2
一班   6           .00          0           .00          0           .33          2           .67          4（所影响的行数为 2 行）
*/