【SQL】高级SQL处理，根据基本表，动态设置显示表列的样式

Question

可能要用到动态SQL!!!不过不清楚根据REMARK怎么弄成那样的???不明白意思

Answer 1

--建立测试数据
create table TmpTable(ID smallint,level varchar(20),Re Varchar(20))
Insert Into TmpTable(ID,level,Re)
Select 1,'aa','1-10'
Union All Select 2,'bb','11-20'
Union All Select 3,'cc','21-30'
Union All Select 4,'dd','31-50'
Union All Select 5,'ee','51-9999'
Select * From TmpTablecreate table TmpBill(ID smallint,Student varchar(20),Grade smallint)
Insert Into TmpBill(ID,Student,Grade)
Select 1,'张三',10
Union All Select 2,'李四',34
Union All Select 3,'王五',24
Union All Select 4,'阿扁',18
Union All Select 5,'花花',98
Union All Select 6,'黄河',45
Select * From TmpBill
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([level])+'=max(case when [level]='+quotename([level],'''')+' then [GRADE] else 0 end)'
from 
  (SELECT A.*,B.GRADE FROM TmpTable A,TmpBill B WHERE B.GRADE BETWEEN CAST(LEFT(REMARK,CHARINDEX('-',REMARK)-1) AS INT) AND CAST(RIGHT(REMARK,LEN(REMARK)-CHARINDEX('-',REMARK)) AS INT)) T 
group by [level]
exec('select [ID],[Student]'+@s+' from (SELECT A.[level],B.* FROM TmpTable A,TmpBill B WHERE B.GRADE BETWEEN CAST(LEFT(REMARK,CHARINDEX(''-'',REMARK)-1) AS INT) AND CAST(RIGHT(REMARK,LEN(REMARK)-CHARINDEX(''-'',REMARK)) AS INT)) T group by [ID],[Student] order by id')/**
ID     Student              aa          bb          cc          dd          ee          
------ -------------------- ----------- ----------- ----------- ----------- ----------- 
1      张三                   10          0           0           0           0
2      李四                   0           0           0           34          0
3      王五                   0           0           24          0           0
4      阿扁                   0           18          0           0           0
5      花花                   0           0           0           0           98
6      黄河                   0           0           0           45          0
**/

Answer 2

--建立测试数据
IF OBJECT_ID('[TmpTable]') IS NOT NULL DROP TABLE [TmpTable] 
 GO 
create table TmpTable(ID smallint,level varchar(20),Re Varchar(20))
Insert Into TmpTable(ID,level,Re)
Select 1,'aa','1-10'
Union All Select 2,'bb','11-20'
Union All Select 3,'cc','21-30'
Union All Select 4,'dd','31-50'
Union All Select 5,'ee','51-9999'
Select * From TmpTable
IF OBJECT_ID('[TmpBill]') IS NOT NULL DROP TABLE [TmpBill] 
 GO 
create table  TmpBill(ID smallint,Student varchar(20),Grade smallint)
Insert Into TmpBill(ID,Student,Grade)
Select 1,'张三',10
Union All Select 2,'李四',34
Union All Select 3,'王五',24
Union All Select 4,'阿扁',18
Union All Select 5,'花花',98
Union All Select 6,'黄河',45
Select * From TmpBill--查询
declare @s varchar(8000)
select @s='select a.id,a.student'
select @s=@s+',max(case when b.[level]='''+[level]+''' then Grade else 0 end) ['+[level]+']'
from TmpTable
select @s=@s+'
from TmpBill a 
  join TmpTable b 
    on a.Grade between left(Re,charindex(''-'',Re)-1)*1 and right(Re,len(Re)-charindex(''-'',Re))*1
group by a.id,a.student
order by id'
exec(@s)--结果
/*
id     student              aa          bb          cc          dd          ee
------ -------------------- ----------- ----------- ----------- ----------- -----------
1      张三                   10          0           0           0           0
2      李四                   0           0           0           34          0
3      王五                   0           0           24          0           0
4      阿扁                   0           18          0           0           0
5      花花                   0           0           0           0           98
6      黄河                   0           0           0           45          0(6 行受影响)
*/

Answer 3

问高手josy 和htl258 问什么都要动态sql我改成普通sql怎么不行呢完全一样的写法

Answer 4

IF OBJECT_ID('[TmpTable]') IS NOT NULL DROP TABLE [TmpTable] 
 GO 
create table TmpTable(ID smallint,level varchar(20),Re Varchar(20))
Insert Into TmpTable(ID,level,Re)
Select 1,'aa','1-10'
Union All Select 2,'bb','11-20'
Union All Select 3,'cc','21-30'
Union All Select 4,'dd','31-50'
Union All Select 5,'ee','51-9999'IF OBJECT_ID('[TmpBill]') IS NOT NULL DROP TABLE [TmpBill] 
 GO 
create table  TmpBill(ID smallint,Student varchar(20),Grade smallint)
Insert Into TmpBill(ID,Student,Grade)
Select 1,'张三',10
Union All Select 2,'李四',34
Union All Select 3,'王五',24
Union All Select 4,'阿扁',18
Union All Select 5,'花花',98
Union All Select 6,'黄河',45
--查询
select a.id,a.student,
  max(case when b.[level]='aa' then Grade else 0 end) [aa],
  max(case when b.[level]='bb' then Grade else 0 end) [bb],
  max(case when b.[level]='cc' then Grade else 0 end) [cc],
  max(case when b.[level]='dd' then Grade else 0 end) [dd],
  max(case when b.[level]='ee' then Grade else 0 end) [ee]
from TmpBill a 
  join TmpTable b 
    on a.Grade between left(Re,charindex('-',Re)-1)*1 and right(Re,len(Re)-charindex('-',Re))*1
group by a.id,a.student
order by id
/*
id     student              aa          bb          cc          dd          ee
------ -------------------- ----------- ----------- ----------- ----------- -----------
1      张三                   10          0           0           0           0
2      李四                   0           0           0           34          0
3      王五                   0           0           24          0           0
4      阿扁                   0           18          0           0           0
5      花花                   0           0           0           0           98
6      黄河                   0           0           0           45          0(6 行受影响)
*/可以

Answer 5

谢谢htl258 还是动态的好后面的查询时枚举出来达不到实际的效果

Answer 6

谢谢josy 和htl258 方法都是正确的，
因为私下问了htl258一些问题，所以这样结贴给分了