主表tbJbxx:nsID,xm,xxmc//nsID是主键,xm姓名,xxmc 学校名称子表一tbKmOne:nsID,cjDC,Kssj//cjDc成绩等次,只有合格和不合格两种取值,Kssj考试时间
子表二tbKmTwo:nsID,cjDc,kssj
子表三tbThree:nsID,cjDc,kssj
求:
按xxmc(学校名称),在指定Kssj(考试时间)的合格率.2010年1月各学校考试成绩合格率排名: xxmc KmOne KmTwo KmThree 平均合格率
第二中学 89% 88% 93% 90%
城效中学 .. .. .. 88%
适存中学 .. .. .. 83%
平均合格率系指该校总合格人数/总参考人数
子表二tbKmTwo:nsID,cjDc,kssj
子表三tbThree:nsID,cjDc,kssj
求:
按xxmc(学校名称),在指定Kssj(考试时间)的合格率.2010年1月各学校考试成绩合格率排名: xxmc KmOne KmTwo KmThree 平均合格率
第二中学 89% 88% 93% 90%
城效中学 .. .. .. 88%
适存中学 .. .. .. 83%
平均合格率系指该校总合格人数/总参考人数
/*
标题：普通行列转换(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)
a.xxmc,
ltrim(cast(sum(case b.cjDC when '合格' then 1 esle 0 end)*100.0/count(1) as dec(18,2))+'%' as KmOne,
ltrim(cast(sum(case c.cjDC when '合格' then 1 esle 0 end)*100.0/count(1) as dec(18,2))+'%' as KmTwo,
ltrim(cast(sum(case d.cjDC when '合格' then 1 esle 0 end)*100.0/count(1) as dec(18,2))+'%' as KmThree
from
tbJbxx a,tbKmOne b,tbKmTwo c,tbKmThree d
where
a.nsID=b.nsID and a.nsID=c.nsID and a.nsID=d.nsID
group by
a.xxmc
子表二tbKmTwo:nsID,cjDc,kssj
子表三tbThree:nsID,cjDc,kssj ---
select
xxmc,
kmone = kmone*1.0/km1,
kmtwo = kmtwo*1.0/km2,
kmthree = kmthree*1.0/km3,
平均合格率=nullif(isnull(kmone,0)+isnull(kmtwo,0)+isnull(kmthree,0))/COALESCE (nullif(isnull(km1,0)+isnull(km2,0)+isnull(km3,0),0),1)
from tbJbxx a
left join (
select
nsid,
sum(case when cjDC = '合格' then 1 else 0 end) as kmone,count(1) as km1
from tbkmone
group by nsid)b on a.nsid = b.nsid
left join (
select
nsid,
sum(case when cjDC = '合格' then 1 else 0 end) as kmtwo,count(1) as km2
from tbkmtwo
group by nsid)c on on a.nsid = c.nsid
left join(
select
nsid,
sum(case when cjDC = '合格' then 1 else 0 end) as kmthree,count(1) as km3
from tbkmthree
group by nsid) d on a.nsid = d.nsid
happyflystone提供的语句选出全是null
tt.xxmc,t.KmOne,r.KmTwo,h.KmThree
from tbJbxx tt left join
(
select
a.xxmc,ltrim(cast(sum(case b.cjDC when '合格' then 1 else 0 end)/count(1)*100.0 as dec(9,0)))+'%' as KmOne
from tbJbxx a join tbKmOne b
on a.nsID = b.nsID
group by a.xxmc
) t on tt.xxmc=t.xxmc
join
(
select
c.xxmc,ltrim(cast(sum(case d.cjDC when '合格' then 1 else 0 end)/count(1)*100.0 as dec(9,0)))+'%' as KmTwo
from tbJbxx c join tbKmOne d
on c.nsID = d.nsID
group by c.xxmc
) r on t.xxmc=r.xxmc join
(
select
e.xxmc,ltrim(cast(sum(case f.cjDC when '合格' then 1 else 0 end)/count(1)*100.0 as dec(9,0)))+'%' as KmThree
from tbJbxx e join tbKmOne f
on e.nsID = f.nsID
group by e.xxmc
) h on t.xxmc = h.xxmc
if object_id('tbm') is not null drop table tbmcreate table tbm(nsID int identity(1,1),xm varchar(20),xxmc varchar(20) )
insert into tbm values('张三1' , '第二中学')
insert into tbm values('网安1' , '城效中学')
insert into tbm values('黑子1' , '城效中学')
insert into tbm values('李四1' , '第二中学')
insert into tbm values('刘六1' , '适存中学')
insert into tbm values('秋喜1' , '适存中学')
insert into tbm values('张大方2' , '第二中学')
insert into tbm values('卫安2' , '城效中学')
insert into tbm values('于子2' , '城效中学')
insert into tbm values('格拉汉姆2' , '第二中学')
insert into tbm values('工特2' , '适存中学')
insert into tbm values('斯蒂芬2' , '适存中学')
insert into tbm values('秋喜3' , '适存中学')
insert into tbm values('张大方3' , '第二中学')
insert into tbm values('卫安3' , '城效中学')
insert into tbm values('于子3' , '城效中学')
insert into tbm values('格拉汉姆3' , '第二中学')
insert into tbm values('工特3' , '适存中学')
insert into tbm values('斯蒂芬3' , '适存中学')
insert into tbm values('wangwang' , '恒安中学')
goif object_id('tb1') is not null drop table tb1
create table tb1(nsID int,cjDC varchar(20),Kssj datetime)
insert into tb1 values('1' , '合格','2010-3-2')
insert into tb1 values('2' , '合格','2010-3-1')
insert into tb1 values('3' , '不合格','2010-3-1')
insert into tb1 values('4' , '合格','2010-3-3')
insert into tb1 values('5' , '合格','2010-3-3')
insert into tb1 values('6' , '合格','2010-3-1')
go
if object_id('tb2') is not null drop table tb2
create table tb2(nsID int,cjDC varchar(20),Kssj datetime)
insert into tb2 values('7' , '合格','2010-3-2')
insert into tb2 values('8' , '不合格','2010-3-1')
insert into tb2 values('9' , '合格','2010-3-1')
insert into tb2 values('10' , '合格','2010-3-3')
insert into tb2 values('11' , '合格','2010-3-3')
insert into tb2 values('12' , '合格','2010-3-1')
insert into tb2 values('20' , '合格','2010-3-1')
goif object_id('tb3') is not null drop table tb3
create table tb3(nsID int,cjDC varchar(20),Kssj datetime)
insert into tb3 values('13' , '不合格','2010-3-2')
insert into tb3 values('14' , '合格','2010-3-1')
insert into tb3 values('15' , '合格','2010-3-1')
insert into tb3 values('16' , '合格','2010-3-3')
insert into tb3 values('17' , '合格','2010-3-3')
insert into tb3 values('18' , '不合格','2010-3-1')
insert into tb3 values('19' , '合格','2010-3-1')
go--查询
select distinct tbm.xxmc
,k1=isnull((convert(varchar(20),cast(k1*100 as dec(18,0) ))+'%'),0)
,k2=isnull((convert(varchar(20),cast(k2*100 as dec(18,0) ))+'%'),0)
,k3=isnull((convert(varchar(20),cast(k3*100 as dec(18,0) ))+'%'),0)
,平均合格率=convert(varchar(20),cast((isnull(k1,0)+isnull(k2,0)+isnull(k3,0))*100/3 as dec(18,0)))+'%'
from
tbm
left join
(
select xxmc,sum(case when cjDC='合格' then 1 else 0 end)*1.0/count(1) as k1 from tb1
left join
tbm on tbm.nsid=tb1.nsid
group by xxmc
)a on tbm.xxmc=a.xxmc
left join(
select xxmc,sum(case when cjDC='合格' then 1 else 0 end)*1.0/count(1) as k2 from tb2
left join
tbm on tbm.nsid=tb2.nsid
group by xxmc
)b on tbm.xxmc=b.xxmc
left join(
select xxmc,sum(case when cjDC='合格' then 1 else 0 end)*1.0/count(1) as k3 from tb3
left join
tbm on tbm.nsid=tb3.nsid
group by xxmc
)c on tbm.xxmc=c.xxmc
--结果
/*
xxmc k1 k2 k3 平均合格率
城效中学 50% 50% 100% 67%
第二中学 100% 100% 100% 100%
恒安中学 0 100% 0 33%
适存中学 100% 100% 33% 78%*/未能看到楼主的数据，所以代码考虑欠缺，
比如有了KmFour，KmFive，KmSix
比如平均合格率
楼主看着办哈
其它的高手结果不正确
真想多给你一些分。