求SQL语句：建源表代码已写好

Question

求SQL语句：由下面的头两个表fgb,cjb得出后两表krzf,bjzf,建立头两个表的代码已为大硖们准备好，附在后面。分工表(fgb)
bj km kr
一01 语文 张三
一02 语文 张三
一03 语文 张三
一01 数学 李四
一02 数学 李四
一03 数学 王五成绩表(cjb)
bj xm km fs
一01 子 语文 53
一01 丑 语文 95
一02 寅 语文 88
一02 卯 语文 21
一03 辰 语文 92
一03 巳 语文 0
一01 午 数学 99
一01 未 数学 88
一02 申 数学 11
一02 酉 数学 56
一03 戌 数学 77
一03 亥 数学 62科任质分(krzf)
km kr bj zf rs pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0
语文 张三 一01,一02,一03 349 5 69.8  3 0.60  95 21 2 1 0 0 1 0 0 1
数学 李四 一01,一02 254 4 63.5  2 0.50  99 11 1 1 0 0 1 0 0 1
数学 王五 一03 139 2 69.5  2 1.00  77 62 0 0 1 1 0 0 0 0班级质分(bjzf)
bj km kr zf rs pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0
一01 语文 张三 148 2 74.0  1 0.50  95 53 1 0 0 0 1 0 0 0
一02 语文 张三 109 2 54.5  1 0.50  88 21 0 1 0 0 0 0 0 1
一03 语文 张三 92 1 92.0  1 1.00  92 92 1 0 0 0 0 0 0 0
合计 语文 349 5 69.8  3 0.60  95 21 2 1 0 0 1 0 0 1
一01 数学 李四 187 2 93.5  2 1.00  99 88 1 1 0 0 0 0 0 0
一02 数学 李四 67 2 33.5  0 0.00  56 11 0 0 0 0 1 0 0 1
一03 数学 王五 139 2 69.5  2 1.00  77 62 0 0 1 1 0 0 0 0
合计 数学 393 6 65.5  4 0.67  99 11 1 1 1 1 1 0 0 1
附：
create table fgb (bj varchar(4),km varchar(4),kr varchar(6))
insert fgb
select '一01', '语文', '张三' union
select '一02', '语文', '张三' union
select '一03', '语文', '张三' union
select '一01', '数学', '李四' union
select '一02', '数学', '李四' union
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float)
insert cjb
select '一01', '子', '语文', 53 union
select '一01', '丑', '语文', 95 union
select '一02', '寅', '语文', 88 union
select '一02', '卯', '语文', 21 union
select '一03', '辰', '语文', 92 union
select '一03', '巳', '语文', 0  union
select '一01', '午', '数学', 99 union
select '一01', '未', '数学', 88 union
select '一02', '申', '数学', 11 union
select '一02', '酉', '数学', 56 union
select '一03', '戌', '数学', 77 union
select '一03', '亥', '数学', 62

Answer 1

appendix:
rs：零分者不计入人数
zg:科最高分
zd: 最低分定义为大于0的最低分(一科不可能全0)
班级和科目都是动态可增减的，应能适应变化
p90: 分数>=90的人数
p80: 分数>=80 and 分数<90的人数
p0: 分数>0 and 分数<30的人数
kr: 科任姓名
jgs: 及格人数
jgl: 及格率
zf: 总分
rs: 科分数非0人数
pj: 平均分
pj=zf/rs
jgl=jgs/rs

Answer 2

先帮你贴一个行列转 换，有空帮你看看。
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华 
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb原表： 地区             初级人数             中级人数             高级人数          ... 
北京               0                    12                   15 
上海               2                    5                    7 
... 转为： 级别             北京          上海              ... 
初级人数           0             2 
中级人数           12            5 
高级人数           15            7 
... 基本要求：地区动态，级别不变 
高级要求：地区动态，级别动态 有点难度，请教高高手create table tb(地区 varchar(20),初级人数 int,中级人数 int,高级人数 int)
insert tb select '北京',0,12,15
insert tb select '上海',2,5,7declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)select @f1='',@f2='',@f3=''select @f1=@f1+',['+地区+']='''+ltrim([初级人数])+''''
    ,@f2=@f2+','''+ltrim([中级人数])+''''
    ,@f3=@f3+','''+ltrim([高级人数])+''''
from tb
exec('select 地区=''初级人数'''+@f1
    +' union all select ''中级人数'''+@f2
    +' union all select ''高级人数'''+@f3)
godrop table tb/*
地区       北京   上海   
-------- ---- ---- 
初级人数     0    2
中级人数     12   5
高级人数     15   7
*/

Answer 3

1.科任质分(krzf) CREATE FUNCTION dbo.f_str(@id varchar(50))RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + bj    FROM fgb    WHERE km=@id    RETURN STUFF(@r, 1, 1, '')ENDselect cjb.km,fgb.kr,dbo.f_str(cjb.km) as bj,sum(fs) as zf,count(fs) as rs,
sum(fs)/count(fs) as pjf,sum(case when fs>=60 then 1 else 0 end) as jgs,
sum(case when fs>=60 then 1 else 0 end)/count(fs) as jgl,
max(fs) as zg,min(fs) as zd,sum(case when fs>=90 then fs else 0 end) as p90,
sum(case when fs>=80 and fs<90 then fs else 0 end) as p80,
sum(case when fs>=70 and fs<80 then fs else 0 end) as p70,
sum(case when fs>=60 and fs<70 then fs else 0 end) as p60,
sum(case when fs>=50 and fs<60 then fs else 0 end) as p50,
sum(case when fs>=40 and fs<50 then fs else 0 end) as p40,
sum(case when fs>=30 and fs<40 then fs else 0 end) as p30,
sum(case when fs>=20 and fs<30 then fs else 0 end) as p20,
sum(case when fs>=10 and fs<20 then fs else 0 end) as p10,
sum(case when fs>=0 and fs<10 then fs else 0 end) as p0
 from cjb left join fgb on cjb.bj=fgb.bj and cjb.km=fgb.km
where cjb.fs<>0 
group by cjb.km,fgb.kr

Answer 4

2.班级质分(bjzf) 
select * from(
select cjb.bj,cjb.km,fgb.kr,sum(fs) as zf,count(fs) as rs,
sum(fs)/count(fs) as pjf,sum(case when fs>=60 then 1 else 0 end) as jgs,
sum(case when fs>=60 then 1 else 0 end)/count(fs) as jgl,
max(fs) as zg,min(fs) as zd,sum(case when fs>=90 then fs else 0 end) as p90,
sum(case when fs>=80 and fs<90 then fs else 0 end) as p80,
sum(case when fs>=70 and fs<80 then fs else 0 end) as p70,
sum(case when fs>=60 and fs<70 then fs else 0 end) as p60,
sum(case when fs>=50 and fs<60 then fs else 0 end) as p50,
sum(case when fs>=40 and fs<50 then fs else 0 end) as p40,
sum(case when fs>=30 and fs<40 then fs else 0 end) as p30,
sum(case when fs>=20 and fs<30 then fs else 0 end) as p20,
sum(case when fs>=10 and fs<20 then fs else 0 end) as p10,
sum(case when fs>=0 and fs<10 then fs else 0 end) as p0
 from cjb left join fgb on cjb.bj=fgb.bj and cjb.km=fgb.km
where cjb.fs<>0 
group by cjb.km,fgb.kr,cjb.bj
union all
select '合计',cjb.km,'',sum(fs) as zf,count(fs) as rs,
sum(fs)/count(fs) as pjf,sum(case when fs>=60 then 1 else 0 end) as jgs,
sum(case when fs>=60 then 1 else 0 end)/count(fs) as jgl,
max(fs) as zg,min(fs) as zd,sum(case when fs>=90 then fs else 0 end) as p90,
sum(case when fs>=80 and fs<90 then fs else 0 end) as p80,
sum(case when fs>=70 and fs<80 then fs else 0 end) as p70,
sum(case when fs>=60 and fs<70 then fs else 0 end) as p60,
sum(case when fs>=50 and fs<60 then fs else 0 end) as p50,
sum(case when fs>=40 and fs<50 then fs else 0 end) as p40,
sum(case when fs>=30 and fs<40 then fs else 0 end) as p30,
sum(case when fs>=20 and fs<30 then fs else 0 end) as p20,
sum(case when fs>=10 and fs<20 then fs else 0 end) as p10,
sum(case when fs>=0 and fs<10 then fs else 0 end) as p0
 from cjb left join fgb on cjb.bj=fgb.bj and cjb.km=fgb.km
where cjb.fs<>0 
group by cjb.km) as aa
order by km,bj asc

Answer 5

--1,没有合并你的bj
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62select km , kr , 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from (select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 60) m where m.km = n.km and m.kr = n.kr),
  jgl = cast((select count(*) from cjb where fs > 60 and km = n.km and kr = n.kr)*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = (select count(*) from cjb where fs = 0 and km = n.km and kr = n.kr)
from
(
  select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 0
) n
group by km , kr
order by km , krdrop table fgb,cjb/*
km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
数学   李四     254.0                                                 4           63.5                 2           1.00                 99.0                                                  11.0                                                  1           1           0           0           1           0           0           0
数学   王五     139.0                                                 2           69.5                 2           2.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
语文   张三     349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 3 行）
*/

Answer 6

--1
--1,没有合并你的bj
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62select km , kr , 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from (select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 60) m where m.km = n.km and m.kr = n.kr),
  jgl = cast((select count(*) from ((select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 60)) m where km = n.km and kr = n.kr)*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = (select count(*) from cjb where fs = 0 and km = n.km and kr = n.kr)
from
(
  select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 0
) n
group by km , kr
order by km , krdrop table fgb,cjb/*
km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
数学   李四     254.0                                                 4           63.5                 2           .50                  99.0                                                  11.0                                                  1           1           0           0           1           0           0           0
数学   王五     139.0                                                 2           69.5                 2           1.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
语文   张三     349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 3 行）
*/

Answer 7

--2--1,没有合并你的bj
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62select * from 
(
select bj , km , kr , 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from (select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs >= 60) m where m.bj = n.bj and m.km = n.km and m.kr = n.kr),
  jgl = cast((select count(*) from ((select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs >= 60)) m where m.bj = n.bj and km = n.km and kr = n.kr)*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = (select count(*) from cjb where fs = 0 and bj = n.bj and km = n.km and kr = n.kr)
from
(
  select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 0
) n
group by bj , km , kr
union all
select '合计' bj, km , ' ' as kr, 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from cjb where fs >= 60 and km = n.km),
  jgl = cast((select count(*) from cjb where fs >= 60 and km = n.km )*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = (select count(*) from cjb where fs = 0 and km = n.km)
from cjb n where fs > 0
group by km
) t
order by km , case bj when '合计' then 2 else 1 end , krdrop table fgb,cjb/*
bj   km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
一01  数学   李四     187.0                                                 2           93.5                 2           1.00                 99.0                                                  88.0                                                  1           1           0           0           0           0           0           0
一02  数学   李四     67.0                                                  2           33.5                 0           .00                  56.0                                                  11.0                                                  0           0           0           0           1           0           0           0
一03  数学   王五     139.0                                                 2           69.5                 2           1.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
合计   数学          393.0                                                 6           65.5                 4           .67                  99.0                                                  11.0                                                  1           1           1           1           1           0           0           0
一03  语文   张三     92.0                                                  1           92.0                 1           1.00                 92.0                                                  92.0                                                  1           0           0           0           0           0           0           1
一02  语文   张三     109.0                                                 2           54.5                 1           .50                  88.0                                                  21.0                                                  0           1           0           0           0           0           0           0
一01  语文   张三     148.0                                                 2           74.0                 1           .50                  95.0                                                  53.0                                                  1           0           0           0           1           0           0           0
合计   语文          349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 8 行）
*/

Answer 8

合并列值
原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳表结构，数据如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc(2 行受影响)
*/--SQL2005中的方法2(csdn dobear_0922 提供)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id/*
id          values
----------- --------------------
1           aa,bb
2           aaa,bbb,ccc(2 row(s) affected)*/drop table tb

Answer 9

非常感谢各位热心
测了下
dawugui 的第一个答案班级质分乍一看对了细一看各段人数(p0-p90)之和与rs有不相等的
fuanwei 的两个都有点近似,但是都不对，有严重错误

Answer 10

对不起大家，一定是我没把问题表述明白,表对的不齐。这回我对齐一些：
求SQL语句：由下面的头两个表fgb,cjb得出后两表krzf,bjzf,建立头两个表的代码已为大硖们准备好，附在后面。 分工表(fgb) 
bj   km  kr 
一01 语文 张三 
一02 语文 张三 
一03 语文 张三 
一01 数学 李四 
一02 数学 李四 
一03 数学 王五 成绩表(cjb) 
bj  xm km   fs 
一01 子 语文 53 
一01 丑 语文 95 
一02 寅 语文 88 
一02 卯 语文 21 
一03 辰 语文 92 
一03 巳 语文 0 
一01 午 数学 99 
一01 未 数学 88 
一02 申 数学 11 
一02 酉 数学 56 
一03 戌 数学 77 
一03 亥 数学 62 科任质分(krzf) 
km  kr   bj              zf  rs  pj    jgs jgl  zg  zd p90 p80 p70 p60 p50 p40 p30 p0 
语文 张三 一01,一02,一03    349 5  69.8   3  0.60  95  21 2   1   0   0   1   0   0   1 
数学 李四 一01,一02         254 4  63.5   2  0.50  99  11 1   1   0   0   1   0   0   1 
数学 王五 一03              139 2  69.5   2  1.00  77  62 0   0   1   1   0   0   0   0 班级质分(bjzf) 
bj   km  kr    zf rs pj  jgs  jgl  zg zd p90 p80 p70 p60 p50 p40 p30 p0 
一01 语文 张三  148 2 74.0  1  0.50  95 53 1   0   0   0   1   0   0   0 
一02 语文 张三  109 2 54.5  1  0.50  88 21 0   1   0   0   0   0   0   1 
一03 语文 张三   92 1 92.0  1  1.00  92 92 1   0   0   0   0   0   0   0 
合计 语文       349 5 69.8  3  0.60  95 21 2   1   0   0   1   0   0   1 
一01 数学 李四  187 2 93.5  2  1.00  99 88 1   1   0   0   0   0   0   0 
一02 数学 李四   67 2 33.5  0  0.00  56 11 0   0   0   0   1   0   0   1 
一03 数学 王五  139 2 69.5  2  1.00  77 62 0   0   1  1    0   0   0   0 
合计 数学        393 6 65.5  4  0.67  99 11 1  1   1   1    1   0   0   1 
附： 
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62解释：
所有统计数据中0分者不算一个人
rs：零分者不计入人数 
zg:科最高分 
zd: 最低分定义为大于0的最低分(一科不可能全0) 
班级和科目都是动态可增减的，应能适应变化 
p90: 分数>=90的人数 
p80: 分数>=80 and 分数 <90的人数 
p0: 分数>0 and 分数 <30的人数 
kr: 科任姓名 
jgs: 及格人数 
jgl: 及格率 
zf: 总分 
rs: 科分数非0人数 
pj: 平均分 
pj=zf/rs 
jgl=jgs/rs

Answer 11

晕死！！！哪位高手能告诉我如何对齐？这里不支持TAB啊

Answer 12

再试试能不能用图形方式发上来：求SQL语句：由下面的头两个表fgb,cjb得出后两表krzf,bjzf,建立头两个表的代码已为大硖们准备好，附在后面。 附建表代码： 
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62

Answer 13

对第一个做了修改,开始理解错了,以为P0是为0分的,其实是<30.--1
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62select km , kr , 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from (select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 60) m where m.km = n.km and m.kr = n.kr),
  jgl = cast((select count(*) from cjb where fs > 60 and km = n.km and kr = n.kr)*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = sum(case when fs < 30 then 1 else 0 end)
from
(
  select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 0
) n
group by km , kr
order by km , krdrop table fgb,cjb/*
km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
数学   李四     254.0                                                 4           63.5                 2           1.00                 99.0                                                  11.0                                                  1           1           0           0           1           0           0           1
数学   王五     139.0                                                 2           69.5                 2           2.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
语文   张三     349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 3 行）
*/

Answer 14

--2
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62select * from 
(
select bj , km , kr , 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from (select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs >= 60) m where m.bj = n.bj and m.km = n.km and m.kr = n.kr),
  jgl = cast((select count(*) from ((select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs >= 60)) m where m.bj = n.bj and km = n.km and kr = n.kr)*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = sum(case when fs < 30 then 1 else 0 end)
from
(
  select a.* , b.fs from fgb a , cjb b where a.bj = b.bj and a.km = b.km and b.fs > 0
) n
group by bj , km , kr
union all
select '合计' bj, km , ' ' as kr, 
  zf  = sum(fs) ,
  rs  = count(*) , 
  pj  = cast(avg(fs) as decimal(18,1)) , 
  jgs = (select count(*) from cjb where fs >= 60 and km = n.km),
  jgl = cast((select count(*) from cjb where fs >= 60 and km = n.km )*1.0 / count(*) as decimal(18,2)),
  zg  = max(fs),
  zd  = min(fs),
  p90 = sum(case when fs >= 90 then 1 else 0 end) , 
  p80 = sum(case when fs >= 80 and fs < 90 then 1 else 0 end) , 
  p70 = sum(case when fs >= 70 and fs < 80 then 1 else 0 end) , 
  p60 = sum(case when fs >= 60 and fs < 70 then 1 else 0 end) , 
  p50 = sum(case when fs >= 50 and fs < 60 then 1 else 0 end) , 
  p40 = sum(case when fs >= 40 and fs < 50 then 1 else 0 end) , 
  p30 = sum(case when fs >= 30 and fs < 40 then 1 else 0 end) , 
  p0  = sum(case when fs < 30 then 1 else 0 end)
from cjb n where fs > 0
group by km
) t
order by km , case bj when '合计' then 2 else 1 end , krdrop table fgb,cjb/*
bj   km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
一01  数学   李四     187.0                                                 2           93.5                 2           1.00                 99.0                                                  88.0                                                  1           1           0           0           0           0           0           0
一02  数学   李四     67.0                                                  2           33.5                 0           .00                  56.0                                                  11.0                                                  0           0           0           0           1           0           0           1
一03  数学   王五     139.0                                                 2           69.5                 2           1.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
合计   数学          393.0                                                 6           65.5                 4           .67                  99.0                                                  11.0                                                  1           1           1           1           1           0           0           1
一03  语文   张三     92.0                                                  1           92.0                 1           1.00                 92.0                                                  92.0                                                  1           0           0           0           0           0           0           0
一02  语文   张三     109.0                                                 2           54.5                 1           .50                  88.0                                                  21.0                                                  0           1           0           0           0           0           0           1
一01  语文   张三     148.0                                                 2           74.0                 1           .50                  95.0                                                  53.0                                                  1           0           0           0           1           0           0           0
合计   语文          349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 8 行）
*/

Answer 15

dawugui 兄弟，谢谢
您最后发的两贴中，第二帧正确，第一帖执行结果如下：
/*
km   kr     zf                                                    rs          pj                   jgs         jgl                  zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ------ ----------------------------------------------------- ----------- -------------------- ----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
数学   李四     254.0                                                 4           63.5                 2           1.00                 99.0                                                  11.0                                                  1           1           0           0           1           0           0           1
数学   王五     139.0                                                 2           69.5                 2           2.00                 77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
语文   张三     349.0                                                 5           69.8                 3           .60                  95.0                                                  21.0                                                  2           1           0           0           1           0           0           1（所影响的行数为 3 行）
*/头两行jgl(及格率)不正确，其中一个竟然是2(200%)，这是明显的错误，及格率是个百分数：
jgl=jgs/rs
别外这个帖子中有一个表项是bj(列出该行科任所教的所有班级如'一01,一02,一03')，不知您是不小心忽略了，还是觉得有些难，没有加上，实际上这是我感到最难的地方。
无论如何，对于您和其他朋友花费自己宝贵的时间给以回复，我都十分感激！
希望看到更完美的回复。

Answer 16

1.
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62
go
create function dbo.getclass(@kr varchar(4),@km varchar(4))
returns varchar(20)
as
begin
 declare @bj varchar(20)
 select @bj=isnull(@bj+',','')+bj from fgb where kr = @kr and km = @km
 return @bj
end
go--pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0 
select 
 kr  = a.kr
,km  = a.km
,bj  = dbo.getclass(a.kr,a.km)
,zf  = sum(b.fs)
,rs  = count(*)
,pj  = cast(avg(b.fs) as dec(5,2))
,jgs = sum(case when fs >=60 then 1 else 0 end) 
,jgl = cast(sum(case when fs >=60 then 1 else 0 end)*1.00/count(*) as dec(5,2))
,zg  = max(fs) 
,zd  = min(fs) 
,p90 = sum(case when fs >=90 then 1 else 0 end)  
,p80 = sum(case when fs >=80 and fs < 90 then 1 else 0 end) 
,p70 = sum(case when fs >=70 and fs < 80 then 1 else 0 end) 
,p60 = sum(case when fs >=60 and fs < 70 then 1 else 0 end) 
,p50 = sum(case when fs >=50 and fs < 60 then 1 else 0 end) 
,p40 = sum(case when fs >=40 and fs < 50 then 1 else 0 end) 
,p30 = sum(case when fs >=30 and fs < 40 then 1 else 0 end) 
,p0  = sum(case when fs <30 then 1 else 0 end) 
from fgb a left join cjb b on a.bj = b.bj and a.km = b.km where fs >0
group by a.kr,a.km
go
drop function dbo.getclass
go
drop table cjb,fgb

Answer 17

1.
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62
go
create function dbo.getclass(@kr varchar(4),@km varchar(4))
returns varchar(20)
as
begin
 declare @bj varchar(20)
 select @bj=isnull(@bj+',','')+bj from fgb where kr = @kr and km = @km
 return @bj
end
go--pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0 
select 
 km  = a.km
,kr  = a.kr
,bj  = dbo.getclass(a.kr,a.km)
,zf  = sum(b.fs)
,rs  = count(*)
,pj  = cast(avg(b.fs) as dec(5,2))
,jgs = sum(case when fs >=60 then 1 else 0 end) 
,jgl = cast(sum(case when fs >=60 then 1 else 0 end)*1.00/count(*) as dec(5,2))
,zg  = max(fs) 
,zd  = min(fs) 
,p90 = sum(case when fs >=90 then 1 else 0 end)  
,p80 = sum(case when fs >=80 and fs < 90 then 1 else 0 end) 
,p70 = sum(case when fs >=70 and fs < 80 then 1 else 0 end) 
,p60 = sum(case when fs >=60 and fs < 70 then 1 else 0 end) 
,p50 = sum(case when fs >=50 and fs < 60 then 1 else 0 end) 
,p40 = sum(case when fs >=40 and fs < 50 then 1 else 0 end) 
,p30 = sum(case when fs >=30 and fs < 40 then 1 else 0 end) 
,p0  = sum(case when fs <30 then 1 else 0 end) 
from fgb a left join cjb b on a.bj = b.bj and a.km = b.km where fs >0
group by a.kr,a.km
order by km desc
go
drop function dbo.getclass
go
drop table cjb,fgb
/*
km   kr     bj                   zf                                                    rs          pj      jgs         jgl     zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ------ -------------------- ----------------------------------------------------- ----------- ------- ----------- ------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
语文   张三     一01,一02,一03          349.0                                                 5           69.80   3           .60     95.0                                                  21.0                                                  2           1           0           0           1           0           0           1
数学   李四     一01,一02              254.0                                                 4           63.50   2           .50     99.0                                                  11.0                                                  1           1           0           0           1           0           0           1
数学   王五     一03                  139.0                                                 2           69.50   2           1.00    77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
*/

Answer 18

2.
create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62
go
create function dbo.getclass(@kr varchar(4),@km varchar(4))
returns varchar(20)
as
begin
 declare @bj varchar(20)
 select @bj=isnull(@bj+',','')+bj from fgb where kr = @kr and km = @km
 return @bj
end
go--bj km kr zf rs pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0 
select
 bj  = case when grouping(a.kr)+grouping(a.bj)= 2 then '合计' else a.bj end 
,km  = a.km
,kr  = a.kr
,zf  = sum(b.fs)
,rs  = count(*)
,pj  = cast(avg(b.fs) as dec(5,2))
,jgs = sum(case when fs >=60 then 1 else 0 end) 
,jgl = cast(sum(case when fs >=60 then 1 else 0 end)*1.00/count(*) as dec(5,2))
,zg  = max(fs) 
,zd  = min(fs) 
,p90 = sum(case when fs >=90 then 1 else 0 end)  
,p80 = sum(case when fs >=80 and fs < 90 then 1 else 0 end) 
,p70 = sum(case when fs >=70 and fs < 80 then 1 else 0 end) 
,p60 = sum(case when fs >=60 and fs < 70 then 1 else 0 end) 
,p50 = sum(case when fs >=50 and fs < 60 then 1 else 0 end) 
,p40 = sum(case when fs >=40 and fs < 50 then 1 else 0 end) 
,p30 = sum(case when fs >=30 and fs < 40 then 1 else 0 end) 
,p0  = sum(case when fs <30 then 1 else 0 end) 
from fgb a left join cjb b on a.bj = b.bj and a.km = b.km where fs >0
group by a.km,a.kr,a.bj with rollup
having grouping(a.kr)+ grouping(a.bj)+grouping(a.km) in(0,2)
go
drop function dbo.getclass
go
drop table cjb,fgb
/*
bj   km   kr     zf                                                    rs          pj      jgs         jgl     zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ---- ------ ----------------------------------------------------- ----------- ------- ----------- ------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
一01  数学   李四     187.0                                                 2           93.50   2           1.00    99.0                                                  88.0                                                  1           1           0           0           0           0           0           0
一02  数学   李四     67.0                                                  2           33.50   0           .00     56.0                                                  11.0                                                  0           0           0           0           1           0           0           1
一03  数学   王五     139.0                                                 2           69.50   2           1.00    77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
合计   数学   NULL   393.0                                                 6           65.50   4           .67     99.0                                                  11.0                                                  1           1           1           1           1           0           0           1
一01  语文   张三     148.0                                                 2           74.00   1           .50     95.0                                                  53.0                                                  1           0           0           0           1           0           0           0
一02  语文   张三     109.0                                                 2           54.50   1           .50     88.0                                                  21.0                                                  0           1           0           0           0           0           0           1
一03  语文   张三     92.0                                                  1           92.00   1           1.00    92.0                                                  92.0                                                  1           0           0           0           0           0           0           0
合计   语文   NULL   349.0                                                 5           69.80   3           .60     95.0                                                  21.0                                                  2           1           0           0           1           0           0           1
*/

Answer 19

create table fgb (bj varchar(4),km varchar(4),kr varchar(6)) 
insert fgb 
select '一01', '语文', '张三' union 
select '一02', '语文', '张三' union 
select '一03', '语文', '张三' union 
select '一01', '数学', '李四' union 
select '一02', '数学', '李四' union 
select '一03', '数学', '王五' create table cjb (bj varchar(4),xm varchar(6),km varchar(4),fs float) 
insert cjb 
select '一01', '子', '语文', 53 union 
select '一01', '丑', '语文', 95 union 
select '一02', '寅', '语文', 88 union 
select '一02', '卯', '语文', 21 union 
select '一03', '辰', '语文', 92 union 
select '一03', '巳', '语文', 0  union 
select '一01', '午', '数学', 99 union 
select '一01', '未', '数学', 88 union 
select '一02', '申', '数学', 11 union 
select '一02', '酉', '数学', 56 union 
select '一03', '戌', '数学', 77 union 
select '一03', '亥', '数学', 62
go
create function dbo.getclass(@kr varchar(4),@km varchar(4))
returns varchar(20)
as
begin
 declare @bj varchar(20)
 select @bj=isnull(@bj+',','')+bj from fgb where kr = @kr and km = @km
 return @bj
end
go--bj km kr zf rs pj jgs jgl zg zd p90 p80 p70 p60 p50 p40 p30 p0 
select
 bj  = case when grouping(a.kr)+grouping(a.bj)= 2 then '合计' else a.bj end 
,km  = a.km
,kr  = a.kr
,zf  = sum(b.fs)
,rs  = count(*)
,pj  = cast(avg(b.fs) as dec(5,2))
,jgs = sum(case when fs >=60 then 1 else 0 end) 
,jgl = cast(sum(case when fs >=60 then 1 else 0 end)*1.00/count(*) as dec(5,2))
,zg  = max(fs) 
,zd  = min(fs) 
,p90 = sum(case when fs >=90 then 1 else 0 end)  
,p80 = sum(case when fs >=80 and fs < 90 then 1 else 0 end) 
,p70 = sum(case when fs >=70 and fs < 80 then 1 else 0 end) 
,p60 = sum(case when fs >=60 and fs < 70 then 1 else 0 end) 
,p50 = sum(case when fs >=50 and fs < 60 then 1 else 0 end) 
,p40 = sum(case when fs >=40 and fs < 50 then 1 else 0 end) 
,p30 = sum(case when fs >=30 and fs < 40 then 1 else 0 end) 
,p0  = sum(case when fs <30 then 1 else 0 end) 
from fgb a left join cjb b on a.bj = b.bj and a.km = b.km where fs >0
group by a.km,a.kr,a.bj with rollup
having grouping(a.kr)+ grouping(a.bj)+grouping(a.km) in(0,2)
go
drop function dbo.getclass
go
drop table cjb,fgb
/*
bj   km   kr     zf                                                    rs          pj      jgs         jgl     zg                                                    zd                                                    p90         p80         p70         p60         p50         p40         p30         p0          
---- ---- ------ ----------------------------------------------------- ----------- ------- ----------- ------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
一01  数学   李四     187.0                                                 2           93.50   2           1.00    99.0                                                  88.0                                                  1           1           0           0           0           0           0           0
一02  数学   李四     67.0                                                  2           33.50   0           .00     56.0                                                  11.0                                                  0           0           0           0           1           0           0           1
一03  数学   王五     139.0                                                 2           69.50   2           1.00    77.0                                                  62.0                                                  0           0           1           1           0           0           0           0
合计   数学   NULL   393.0                                                 6           65.50   4           .67     99.0                                                  11.0                                                  1           1           1           1           1           0           0           1
一01  语文   张三     148.0                                                 2           74.00   1           .50     95.0                                                  53.0                                                  1           0           0           0           1           0           0           0
一02  语文   张三     109.0                                                 2           54.50   1           .50     88.0                                                  21.0                                                  0           1           0           0           0           0           0           1
一03  语文   张三     92.0                                                  1           92.00   1           1.00    92.0                                                  92.0                                                  1           0           0           0           0           0           0           0
合计   语文   NULL   349.0                                                 5           69.80   3           .60     95.0                                                  21.0                                                  2           1           0           0           1           0           0           1
*/

Answer 20

dawugui
csoncson
两位大侠，非常感谢！！！
由于dawugui先搞掂了班级质分，分数各一半。