同一个视图中有多个不同条件的count（*），应怎么做？

Question

比如有表xs_ks 
年级   班级   文理科  学号     总分   语文成绩   数学成绩  英语成绩 总分名次  语文名次 数学名次 英语名次 
2005   1      1   050101   260    88        90       82      1        1       1      2   
2005   1      1   050102   250    82        80       88      2        11      2      1         
2005   2      1   050201   210    70        70       70      11       20      3      3 
2005   3      2   050301   240    80        80       80      1        2       1      1   
2005   3      2   050302   230    81        70       79      2        1       12     2   
2005   4      2   050401   200    70        66       64      15       3       13      3   表中1代表文科，2代表理科   ，总分名次、语文名次、数学名次、英语名次都是   文理科   分开排的，想求一视图，如下年级     班级    文理科   总分前10名人数   语文前10名人数   数学前10名人数   英语前10名人数 
2005     1      1           2              1              2              2 
2005     2      1           0              0              1              1 
2005     3      2           2              2              1              2 
2005     4      2           0              1              0              1 请问视图脚本应该怎么写？ 

Answer 1

declare @xs_ks table(年级 int,班级 int,文理科 int,学号 char(6),总分 int,
语文成绩 int,数学成绩 int,英语成绩 int,总分名次 int, 
语文名次 int,数学名次 int,英语名次 int)
insert into @xs_ks
select 2005,1,1,'050101',260,88,90,82,1,1,1,2 union all
select 2005,1,1,'050102',250,82,80,88,2,11,2,1 union all
select 2005,2,1,'050201',210,70,70,70,11,20,3,3 union all
select 2005,3,2,'050301',240,80,80,80,1,2,1,1 union all
select 2005,3,2,'050302',230,81,70,79,2,1,12,2 union all
select 2005,4,2,'050401',200,70,66,64,15,3,13,3select 年级,班级,文理科
,sum(case when 总分名次<10 then 1 else 0 end) 总分名次人数
,sum(case when 语文名次<10 then 1 else 0 end) 语文名次人数
,sum(case when 数学名次<10 then 1 else 0 end) 数学名次人数
,sum(case when 英语名次<10 then 1 else 0 end) 英语名次人数
from @xs_ks
group by 年级,班级,文理科/*
年级        班级        文理科      总分名次人数 语文名次人数 数学名次人数 英语名次人数
----------- ----------- ----------- -----------  -----------  -----------  -----------
2005        1           1           2            1            2            2
2005        2           1           0            0            1            1
2005        3           2           2            2            1            2
2005        4           2           0            1            0            1(4 row(s) affected)
*/

Answer 2

--测试环境
create table student --学生列表
(
s_id int,
s_name char(10)
)
create  table class --课程列表
(
c_id int,
c_name char(10)
)create table grade --学生成绩表
(
s_id int,
c_id int,
cj int
)--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')insert class values(1,'历史')
insert class values(2,'数学')insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)
select a.c_id,a.s_id,a.cj from grade a 
join grade b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc
--处理重复分数
select a.c_id,a.s_id,a.cj 
from grade a join 
( 
  select c_id,cj
  from grade
  group by c_id,cj
) b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc--你可以以动态的修改: n  <= n 来获得
--每门课程的前n个最高分的
declare @ta table(n1 float,n2 float)
insert @ta values(2,3)
insert @ta values(8,5)
insert @ta values(8,5)
insert @ta values(9,3)
insert @ta values(3,1)
insert @ta values(10,4)select n1,n2,cast((n2-n1)*100/n1 as decimal(10,2)) [(n2-n1)*100/n1] ,identity(int,1,1) id
into #temp
from @taselect a.n1,a.n2,a.[(n2-n1)*100/n1],
count(case when a.[(n2-n1)*100/n1] <= b.[(n2-n1)*100/n1] 
      then 1 else null end) 名次
from #temp a cross join 
( 
  select [(n2-n1)*100/n1]
  from #temp
  group by [(n2-n1)*100/n1]
) b
group by a.n1,a.n2,a.[(n2-n1)*100/n1],a.id
order by 名次 drop table #tempn1      n2   (n2-n1)*100/n1 名次    
------ ----- -------------- --------
2.0    3.0   50.00          1
8.0    5.0   -37.50         2
8.0    5.0   -37.50         2
10.0   4.0   -60.00         3
9.0    3.0   -66.67         4
3.0    1.0   -66.67         4（所影响的行数为 6 行）

Answer 3

declare @xs_ks table(年级 int,班级 int,文理科 int,学号 char(6),总分 int,
        语文成绩 int,数学成绩 int,英语成绩 int,总分名次 int, 
        语文名次 int,数学名次 int,英语名次 int)
insert into @xs_ks
select 2005,1,1,'050101',260,88,90,82,1,1,1,2 union all
select 2005,1,1,'050102',250,82,80,88,2,11,2,1 union all
select 2005,2,1,'050201',210,70,70,70,11,20,3,3 union all
select 2005,3,2,'050301',240,80,80,80,1,2,1,1 union all
select 2005,3,2,'050302',230,81,70,79,2,1,12,2 union all
select 2005,4,2,'050401',200,70,66,64,15,3,13,3select 年级,班级,文理科
        ,sum(case 文理科 
when 1 then case when 总分名次<=10 then 1 else 0 end
when 2 then case when 总分名次<=8 then 1 else 0 end
end) 总分人数
        ,sum(case 文理科 
when 1 then case when 语文名次<=10 then 1 else 0 end
when 2 then case when 语文名次<=8 then 1 else 0 end
end) 语文人数
        ,sum(case 文理科 
when 1 then case when 数学名次<=10 then 1 else 0 end
when 2 then case when 数学名次<=8 then 1 else 0 end
end) 数学人数
        ,sum(case 文理科 
when 1 then case when 英语名次<=10 then 1 else 0 end
when 2 then case when 英语名次<=8 then 1 else 0 end
end) 英语人数
from @xs_ks
group by 年级,班级,文理科/*
年级          班级          文理科         总分人数        语文人数        数学人数        英语人数
----------- ----------- ----------- ----------- ----------- ----------- -----------
2005        1           1           2           1           2           2
2005        2           1           0           0           1           1
2005        3           2           2           2           1           2
2005        4           2           0           1           0           1(4 row(s) affected)*/