横纵表引起的排序问题

Question

这个可以排序啊，之前不是处理好了吗
如果subject 是varchar，且符合 科目1 科目2 用以下语句
declare @sql varchar(8000)
DECLARE @TOTALSQL VARCHAR(8000)
set @sql = 'select Name as ' + '姓名'
SET @TOTALSQL = ' UNION ALL select ''合计'''
;WITH tb(NAME,SUBJECT,Result) AS(
    SELECT'a','科目1',90
    UNION ALL SELECT'a','科目12',90
    UNION ALL SELECT'a','科目3',90
)
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
, @TOTALSQL = @TOTALSQL + ' , sum(case Subject when ''' + Subject + ''' then Result else 0 end)'
from (SELECT ROW_NUMBER()OVER(ORDER BY CAST(SUBSTRING(Subject,3,LEN(Subject)) AS INT))RN, Subject from tb GROUP BY Subject) as a ORDER BY RN
set @sql = @sql + ' , ISNULL(sum(Result),0)[学生总分] from tb group by name '
set @TOTALSQL = @TOTALSQL + ' , ISNULL(sum(Result),0) from tb'
SET @sql=@sql+@TOTALSQL
SET @SQL=';WITH tb(NAME,SUBJECT,Result) AS(
    SELECT''a'',''语文'',90
)'+@sql
PRINT @sql
exec(@sql)如果subject 是int,用以下语句
declare @sql varchar(8000)
DECLARE @TOTALSQL VARCHAR(8000)
set @sql = 'select No as [学号], Name as [姓名]'
SET @TOTALSQL = ' UNION ALL select '''',''合计'''
select @sql = @sql + ' , sum(case Subject when ''' + CAST(Subject AS VARCHAR(10)) + ''' then Result else 0 end) [' + CAST(Subject AS VARCHAR(10)) + ']'
, @TOTALSQL = @TOTALSQL + ' , sum(case Subject when ''' + CAST(Subject AS VARCHAR(10)) + ''' then Result else 0 end)'
from (select Subject from tb GROUP BY Subject) as a
set @sql = @sql + ' , ISNULL(sum(Result),0)[学生总分] from tb group by No,name '
set @TOTALSQL = @TOTALSQL + ' , ISNULL(sum(Result),0) from tb'
SET @sql=@sql+@TOTALSQL
--PRINT @sql
exec(@sql)

Answer 1

use Tempdb
go
--> --> 
 
if not object_id(N'tb') is null
drop table tb
Go
Create table tb([No] nvarchar(2),[Name] nvarchar(2),[Subject] int,[Result] int)
Insert tb
select N'01',N'张三',1,74 union all
select N'01',N'张三',2,83 union all
select N'01',N'张三',3,93 union all
select N'02',N'李四',1,74 union all
select N'02',N'李四',2,84 union all
select N'02',N'李四',3,94
Go
declare @sql nvarchar(4000)
set @sql=''
Select     @sql=@sql+','+quotename(N'科目'+RTRIM([Subject]))+'=max(case when [Subject]='+RTRIM([Subject])+' then [Result] else 0 end)'
FROM  tb 
group BY [Subject]
exec('select [No]=isnull([No],''''),[Name]=isnull([Name],N''合计'')'+@sql+'  from tb group by [No],[Name] with rollup having grouping([No])=1 or grouping([Name])=0')
/*
No Name 科目1 科目2 科目3
01 张三 74 83 93
02 李四 74 84 94
合计 74 84 94
*/

Answer 2

用group by + with rollup方法实现，不需要union all计算拼凑结果集

Answer 3

本帖最后由 roy_88 于 2014-11-01 11:22:41 编辑

  

Answer 4

回复2楼，我用的是SQL 2k,subject 为int时不能排序，但是在SQL 2005上可以排序。

Answer 5

回复2楼，你好像用
select distinct subject from tb 更换掉了
select Subject from tb GROUP BY Subject，其它没有变，我周五试过，是排不了序的。数据库不在我这里，我周一再试试吧。
多谢你的耐心解答。

Answer 6

2000怎么会不能排序呢~
你试下这个语句，看是不是正常的显示  1  2  3
以下语句单独执行
SELECT * FROM(SELECT 3 INTID  UNION ALL SELECT 1 UNION ALL SELECT 2)T1 ORDER BY INTID

Answer 7

是指生成语句没序？加上Order by 就行了
Select     @sql=@sql+','+quotename(N'科目'+RTRIM([Subject]))+'=sum(case when [Subject]='+RTRIM([Subject])+' then [Result] else 0 end)'
FROM  tb 
group BY [Subject]
order by [Subject]

Answer 8

本帖最后由 roy_88 于 2014-11-01 12:05:20 编辑

  

Answer 9

select Subject from tb GROUP BY Subject也不行，也不能排序，我用的是SQL 2000,SQL 2005 是可以排序的。
上面这句group by subject 是归类，也没有排序呀，如果加上 order by subject 又报错。
2楼提供的语句是可以排序
SELECT * FROM(SELECT 3 INTID  UNION ALL SELECT 1 UNION ALL SELECT 2)T1 ORDER BY INTID

Answer 10

group by subject 是归类，同时也有排序的效果，不确定2000有没有，应该有的

Answer 11

不能排序时？没有楼主对应的环境，是测不出来的。
在语句上直接加Order By就行了use Tempdb
go
--> --> 
  
if not object_id(N'tb') is null
    drop table tb
Go
Create table tb([No] nvarchar(2),[Name] nvarchar(2),[Subject] int,[Result] int)
Insert tb
select N'01',N'张三',1,74 union all
select N'01',N'张三',2,83 union all
select N'01',N'张三',3,93 union all
select N'02',N'李四',1,74 union all
select N'02',N'李四',2,84 union all
select N'02',N'李四',3,94
Go
declare @sql nvarchar(4000)
set @sql=''
Select     @sql=@sql+','+quotename(N'科目'+RTRIM([Subject]))+'=sum(case when [Subject]='+RTRIM([Subject])+' then [Result] else 0 end)'
FROM  tb 
group BY [Subject]
exec('select [No]=isnull([No],''''),[Name]=isnull([Name],N''合计'')'+@sql+',[总成绩]=sum([Result])  from tb group by [No],[Name] with rollup having grouping([No])=1 or grouping([Name])=0 order by grouping([No]) asc,[总成绩] desc')
/*
No Name 科目1 科目2 科目3 总成绩
02 李四 74 84 94 252
01 张三 74 83 93 250
合计 148 167 187 502
    */