求SQL查询语句:由成绩表得出总分分数段
难点要求:其中fsd表中的项目个数要根据cjb中涉及到的班级个数不同而变,这样才有通用性嘛成绩表(cjb)
xh bj xm zf
1 一01 a 320
2 一01 b 430
3 一02 c 540
4 一02 d 630
5 一01 e 550
6 一01 f 340
7 一01 g 180
8 一01 h 290总分分数段(fsd)
段次 一01 一02 合计
p600 0 1 1
p500 1 1 2
p400 1 0 1
p300 2 0 2
p0 2 0 2
为了让大侠们省点精力,我先写好了建表和插入数据的代码,您只需攻克难点!
create table tb(xh int, bj varchar(4), xm varchar(8),zf float)
insert into cjb select 1,'一01','a',320 union
select 2,'一01','b',430 union
select 3,'一02','c',540 union
select 4,'一02','d',630 union
select 5,'一01','e',550 union
select 6,'一01','f',340
难点要求:其中fsd表中的项目个数要根据cjb中涉及到的班级个数不同而变,这样才有通用性嘛成绩表(cjb)
xh bj xm zf
1 一01 a 320
2 一01 b 430
3 一02 c 540
4 一02 d 630
5 一01 e 550
6 一01 f 340
7 一01 g 180
8 一01 h 290总分分数段(fsd)
段次 一01 一02 合计
p600 0 1 1
p500 1 1 2
p400 1 0 1
p300 2 0 2
p0 2 0 2
为了让大侠们省点精力,我先写好了建表和插入数据的代码,您只需攻克难点!
create table tb(xh int, bj varchar(4), xm varchar(8),zf float)
insert into cjb select 1,'一01','a',320 union
select 2,'一01','b',430 union
select 3,'一02','c',540 union
select 4,'一02','d',630 union
select 5,'一01','e',550 union
select 6,'一01','f',340
p600表示:zf大于等于500,小于600
insert into cjb select 1,'一01','a',320 union
select 2,'一01','b',430 union
select 3,'一02','c',540 union
select 4,'一02','d',630 union
select 5,'一01','e',550 union
select 6,'一01','f',340
godeclare @s varchar(8000)
select @s = isnull(@s+',','')+'['+bj+']=sum(case when bj = '''+bj+''' then 1 else 0 end)'
from ( select distinct bj from cjb) a
set @s = 'select ''P''+ltrim(round(zf,-2)) as 段次,'+@s+ ',count(1) as [sum]
from cjb
group by ''P''+ltrim(round(zf,-2)) order by 段次 desc'
exec( @s)/*
段次 一01 一02 sum
----------------------- ----------- ----------- -----------
P600 1 1 2
P500 0 1 1
P400 1 0 1
P300 2 0 2*/drop table cjb
老乌龟,动态SQL,行转列
insert into cjb select 1,'一01','a',320 union
select 2,'一01','b',430 union
select 3,'一02','c',540 union
select 4,'一02','d',630 union
select 5,'一01','e',550 union
select 6,'一01','f',340 union
select 7,'一01','g',180 union
select 8,'一01','h',290
godeclare @s varchar(8000)
select @s = isnull(@s+',','')+'['+bj+']=sum(case when bj = '''+bj+''' then 1 else 0 end)'
from ( select distinct bj from cjb) a
set @s = 'select ''P''+ltrim(round((case when zf >= 300 then zf else 0 end),-2)) as 段次,'+@s+ ',count(1) as [sum]
from cjb
group by ''P''+ltrim(round((case when zf >= 300 then zf else 0 end),-2)) order by 段次 desc'
exec( @s)/*
段次 一01 一02 sum
----------------------- ----------- ----------- -----------
P600 1 1 2
P500 0 1 1
P400 1 0 1
P300 2 0 2
P0 2 0 2*/drop table cjb
--ltrim(round((case when zf >= 300 then zf else 0 end),-2))
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (xh int,bj varchar(4),xm varchar(1),zf int)
insert into #T
select 1,'一01','a',320 union all
select 2,'一01','b',430 union all
select 3,'一02','c',540 union all
select 4,'一02','d',630 union all
select 5,'一01','e',550 union all
select 6,'一01','f',340 union all
select 7,'一01','g',180 union all
select 8,'一01','h',290declare @SQL nvarchar(4000)set @SQL='select 段次=case when zf<300 then ''P0'' else ''P''+ltrim(zf/100)+''00'' end'
select @SQL=@SQL+',['+bj+']=sum(case bj when '''+bj+''' then 1 else 0 end)' from #T group by bj
set @SQL=@SQL+',合计=count(1) from #T group by case when zf<300 then ''P0'' else ''P''+ltrim(zf/100)+''00'' end order by 1 desc'exec (@SQL)/*
段次 一01 一02 合计
--------------- ----------- ----------- -----------
P600 0 1 1
P500 1 1 2
P400 1 0 1
P300 2 0 2
P0 2 0 2
*/
create table tb(xh int, bj varchar(4), xm varchar(8),zf float)
insert into tb select 1,'一01','a',320 union
select 2,'一01','b',430 union
select 3,'一02','c',540 union
select 4,'一02','d',630 union
select 5,'一01','e',550 union
select 6,'一01','f',340create table part(id int)
insert into part select 0
union select 300
union select 400
union select 500
union select 600
union select 700
select bj,p1.id BigThan,count(1) Num
from tb a
join part p1 on a.zf>=p1.id
join part p2 on a.zf<p2.id
where p1.id=p2.id-100 or (p1.id=0 and p2.id=300)
group by bj,p1.id
order by p1.id
谢谢热心帮助
我测了下发现项目真的是动态的
但是结果集第一行为什么显示有两个600分段的?
/*
段次 一01 一02 sum
----------------------- ----------- ----------- -----------
P600 1 1 2
P500 0 1 1
P400 1 0 1
P300 2 0 2
P0 2 0 2*/
非常感谢,成功了!
可以追问一句么?可不可以把这个作成视图?