由表 create table cj (xh int, bj varchar(8), xm varchar(8), zf float)
查询出如下总分分数段
段次 一班人数 二班人数 ... n班人数 总人数
700 ..................................
690 ..................................
680 5.
.
.
300 ..................................
其余 ........................
如红色5表示一班680<=zf<690有5名学生
其余表示0分以上300分以下的人数
要求能动态适应不定的班级数。
查询出如下总分分数段
段次 一班人数 二班人数 ... n班人数 总人数
700 ..................................
690 ..................................
680 5.
.
.
300 ..................................
其余 ........................
如红色5表示一班680<=zf<690有5名学生
其余表示0分以上300分以下的人数
要求能动态适应不定的班级数。
select
isnull(max(case bj when '一班' then num else 0 end),0) as 一班,
isnull(max(case bj when '二班' then num else 0 end),0) as 二班
from
(select bj,count(xh) as num from cj where zf >= 700 group by bj) as a
union
select
isnull(max(case bj when '一班' then num else 0 end),0) as 一班,
isnull(max(case bj when '二班' then num else 0 end),0) as 二班
from
(select bj,count(xh) as num from cj where zf >= 690 and zf < 700 group by bj) 拼SQL过程:
declare @class_name varchar(8);
declare @sql varchar(8000);set @sql = 'select 'DECLARE selectname CURSOR FOR
select distinct bj from cj
OPEN selectname FETCH NEXT FROM selectname into @class_name
WHILE @@FETCH_STATUS = 0
begin
set @sql += 'isnull(max(case bj when @class_name then num else 0 end),0) as @class_name, '
FETCH NEXT FROM selectname
INTO @class_name
CLOSE selectname
DEALLOCATE selectnameset @sql += 'from '
set @sql += '(select bj,count(xh) as num from cj where zf >= 700 group by bj) as a '
set @sql += 'union '
下再重复使用上面的游标循环,直道把所有的分数段拼完