一个成绩分段汇总怕问题
有两个表:
1.成绩分段表(ID为自增列)
ID MaxFen MinFen
--------------------
1 510 500
2 500 490
3 490 480
2.总成绩表
ClassID StudentID SumFen(总分)
------------------------------------
000005 000001 503
000005 000003 498
000004 000006 487
000004 000003 501
3.班级表
ClassID ClassName
-----------------------
000004 104班
000005 105班
要求结果:
≥500 ≥490 ≥480
----------------------------------------
105班 1 1
104班 1 1
总分分段值是随机的,最多30个分段
[解决办法]
create table #( id int , maxfen int , minfen int)
insert into #
select 1 , 510 , 500 union
select 2 , 500 , 490 union
select 3 , 490 , 480
create table #1 (classid varchar(06) , studentid varchar(06) , sumfen int)
insert into #1
select '000005 ', '000001 ', 503 union
select '000005 ', '000003 ', 498 union
select '000004 ', '000006 ', 487 union
select '000004 ', '000003 ', 501
create table #2 (classid varchar(06) , classname varchar(10))
insert into #2
select '000004 ', '104班 ' union
select '000005 ', '105班 '
select c.classname ,
b.sumfen into #3
from #1 b , #2 c
where c.classid = b.classid
select * from #3
declare @s varchar(8000)
set @s = ' '
select @s = @s + ', ' + 'sum( case when sumfen > = ' + cast( minfen as varchar) + ' and sumfen < ' + cast( maxfen as varchar )
+ ' then 1 else 0 end ) as ' '≥ ' + cast (minfen as varchar) + ' ' ' '
from #
select @s = 'select classname ' + @s + ' from #3 group by classname '
exec(@s)
drop table #
drop table #1
drop table #2
drop table #3
[解决办法]
语句中 "QUOTENAME( '≥ ' + RTRIM(MinFen)) ",为什么使用QUOTENAME和RTRIM两个函数
-------
QUOTENAME:把≥500变换成[≥500]，以符合sql命名规则
RTRIM：数字型强行转化成字符型，可以用CAST(MinFen as varchar) 代替
还有个问题就是:
如果我的成绩分段表为:
1.成绩分段表(ID为自增列)
ID MinFen
--------------------
1 500
2 490
3 480
这样语句应该如何来写
------------------------------------------
可以这样：
-- 查询
DECLARE @s nvarchar(4000)
SET @s = N ' '
SELECT @s = @s + N ', '
+ QUOTENAME( '≥ ' + RTRIM(MinFen))
+ N '=SUM(CASE WHEN B.SumFen> = ' + RTRIM(MinFen)
+ N ' THEN 1 END) '
FROM 成绩分段表
ORDER BY MinFen desc
EXEC(N '
SELECT C.ClassName ' + @s + N '
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
')
[解决办法]
1.成绩分段表(ID为自增列)
ID MinFen
--------------------
1 500
2 490
3 480
这样语句应该如何来写
---------------
declare @s varchar(8000)
set @s = ' '
select @s = @s + ', ' + case when id = 1 then
'sum( case when sumfen > = ' + cast( minfen as varchar) +
' then 1 else 0 end ) as ' '≥ ' + cast (minfen as varchar) + ' ' ' '
else
'sum( case when sumfen > = ' + cast( minfen as varchar) + ' and sumfen < '
+ cast ( ( select minfen from # where id = a.id - 1) as varchar )
+ ' then 1 else 0 end ) as ' '≥ ' + cast (minfen as varchar) + ' ' ' '
end
from # a
select @s = 'select classname ' + @s + ' from #3 group by classname '
exec(@s)
[解决办法]
根据老大的回答,和你要的新需求.如下事例
-- 示例数据
CREATE TABLE 成绩分段表(
ID int IDENTITY, MinFen int)
INSERT 成绩分段表
SELECT 500 UNION ALL
SELECT 490 UNION ALL
SELECT 480
CREATE TABLE 总成绩表(
ClassID varchar(10), StudentID varchar(10), SumFen int)
INSERT 总成绩表
SELECT '000005 ', '000001 ', 503 UNION ALL
SELECT '000005 ', '000003 ', 498 UNION ALL
SELECT '000004 ', '000006 ', 487 UNION ALL
SELECT '000004 ', '000003 ', 501
CREATE TABLE 班级表(
ClassID varchar(10), ClassName varchar(10))
INSERT 班级表
SELECT '000004 ', '104班 ' UNION ALL
SELECT '000005 ', '105班 '
GO
-- 查询
DECLARE @var int
SET @var =1000
DECLARE @s nvarchar(4000)
SET @s = N ' '
SELECT @s = @s + N ', '
+ QUOTENAME( '≥ ' + RTRIM(MinFen))
+ N '=SUM(CASE WHEN B.SumFen> = ' + RTRIM(MinFen)
+ N ' AND SumFen < ' + RTRIM(@var)
+ N ' THEN 1 END) ',@var=MinFen
FROM 成绩分段表
ORDER BY ID
select @s
EXEC(N '
SELECT C.ClassName ' + @s + N '
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
')
-- 删除测试
DROP TABLE 成绩分段表, 总成绩表, 班级表
[解决办法]
语句中 "QUOTENAME( '≥ ' + RTRIM(MinFen)) ",为什么使用QUOTENAME和RTRIM两个函数
minFen是整数类型,当转化成字符串类型的时候,右边会加上空格,所以用RTRIM
QUOTENAME,可能是因为 "> = "是关键字哈,为了规范,最好用QUOTENAME加上 "[> =500] "这个符号.
我的个人想法,不知道对不对
