一个成绩分段汇总怕问题

Question

有两个表:
1.成绩分段表(ID为自增列)
ID  MaxFen  MinFen
--------------------
 1   510     500
 2   500     490
 3   490     4802.总成绩表
ClassID   StudentID  SumFen(总分)
------------------------------------
000005     000001      503
000005     000003      498
000004     000006      487
000004     000003      5013.班级表
ClassID   ClassName
-----------------------
000004     104班
000005     105班要求结果:
         ≥500     ≥490     ≥480
----------------------------------------
105班    1          1
104班    1                     1 总分分段值是随机的,最多30个分段

Answer 1

DECLARE @s nvarchar(4000)
SET @s = N''
SELECT @s = @s + N',' 
+ QUOTENAME('≥' + RTRIM(MinFen))
+ N'=SUM(CASE WHEN SumFen>=' + RTRIM(MinFen)
+ N' AND SumFen<' + RTRIM(MaxFen)
+ N' THEN 1 END)'
FROM 成绩分段表
ORDER BY ID
EXEC(N'
SELECT ClassName ' + @s + N'
FROM(
SELECT C.ClassName, SumFen = SUM(B.SumFen)
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
)A
GROUP BY ClassName')

Answer 2

理解上有点问题, 楼主是要先统计的, 所以得改一下: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(MaxFen)
+ N' THEN 1 END)'
FROM 成绩分段表
ORDER BY ID
EXEC(N'
SELECT C.ClassName' + @s + N'
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
')
GO

Answer 3

-- 示例数据
CREATE TABLE 成绩分段表(
ID int IDENTITY, MaxFen int, MinFen int)
INSERT 成绩分段表
SELECT 510, 500 UNION ALL
SELECT 500, 490 UNION ALL
SELECT 490, 480CREATE 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', 501CREATE TABLE 班级表(
ClassID varchar(10), ClassName varchar(10))
INSERT 班级表
SELECT '000004', '104班' UNION ALL
SELECT '000005', '105班'
GO-- 查询
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(MaxFen)
+ N' THEN 1 END)'
FROM 成绩分段表
ORDER BY ID
EXEC(N'
SELECT C.ClassName' + @s + N'
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
')
GO-- 删除测试
DROP TABLE 成绩分段表, 总成绩表, 班级表

Answer 4

-- 结果:ClassName ≥500 ≥490 ≥480
104班 1 NULL 1
105班 1 1 NULL

Answer 5

谢谢老大的回答,不过有两个问题不太明白语句中"QUOTENAME('≥' + RTRIM(MinFen))",为什么使用QUOTENAME和RTRIM两个函数

Answer 6

还有个问题就是:
如果我的成绩分段表为:1.成绩分段表(ID为自增列)
ID   MinFen
--------------------
 1    500
 2    490
 3    480
这样语句应该如何来写

Answer 7

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',      501create 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.classidselect * 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

Answer 8

语句中"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
')

Answer 9

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   # aselect @s  = 'select classname  ' + @s  +'  from #3 group by classname'exec(@s)

Answer 10

根据老大的回答,和你要的新需求.如下事例-- 示例数据
CREATE TABLE 成绩分段表(
ID int IDENTITY,   MinFen int)
INSERT 成绩分段表
SELECT  500 UNION ALL
SELECT  490 UNION ALL
SELECT  480CREATE 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', 501CREATE TABLE 班级表(
ClassID varchar(10), ClassName varchar(10))
INSERT 班级表
SELECT '000004', '104班' UNION ALL
SELECT '000005', '105班'
GO-- 查询
DECLARE @var int
SET @var =1000DECLARE @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 成绩分段表, 总成绩表, 班级表 

Answer 11

语句中"QUOTENAME('≥' + RTRIM(MinFen))",为什么使用QUOTENAME和RTRIM两个函数
minFen是整数类型,当转化成字符串类型的时候,右边会加上空格,所以用RTRIM
QUOTENAME,可能是因为">="是关键字哈,为了规范,最好用QUOTENAME加上"[>=500]"这个符号.
我的个人想法,不知道对不对

Answer 12

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 ID
EXEC(N'
SELECT C.ClassName' + @s + N'
FROM 班级表 C, 总成绩表 B
WHERE C.ClassID = B.ClassID
GROUP BY C.ClassName
')
GO

Answer 13

CASE WHEN 如果第1个条件满足, 不会再判断第2个, 故不处理上限即可.例如, 下面的查询, @a 满足>400 和>300 两个条件分支, 但>400是先满足条件的分支, 故输出结果是>400, 不会再处理>300这个条件分支DECLARE @a int
SET @a = 500
SELECT 
CASE 
WHEN @a > 500 THEN '>500'
WHEN @a > 400 THEN '>400'
WHEN @a > 300 THEN '>300'
ELSE 'other' END

Answer 14

钻石的正牌，
ORDER BY ID
貌似不妥，冒牌认为应该
ORDER BY MinFen desc

Answer 15

帮忙解决一下我这个问题
http://community.csdn.net/Expert/topic/5304/5304839.xml?temp=.9209101

Answer 16

谢谢大家我需要一个与第一种的结果的相同的答案,而条件就是:
1.成绩分段表(ID为自增列)
ID   MinFen
--------------------
 1    500
 2    490
 3    480
这样语句应该如何来写上面的答案会出很多重复的数据