22,210
社区成员
发帖
与我相关
我的任务
分享
--建立测试数据
Declare @TmpTable table(ID smallint,level varchar(20),Remark Varchar(20))
Insert Into @TmpTable(ID,level,Remark)
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
Declare @TmpBill Table(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
--算法:@TmpTable 用来维护结果表分数区间断的动态列,可自由添加
-- @TmpBill 为学生分数表
-- @TmpReslut 为最终结果表,即需要的动态样式
--处理后的结果
Declare @TmpReslut Table(ID smallint,Student varchar(20),aa smallint,bb smallint,cc smallint,dd smallint,ee smallint)
Insert Into @TmpReslut(ID,Student,aa,bb,cc,dd,ee)
Select 1,'张三',10,0,0,0,0
Union All Select 2,'李四',0,0,0,34,0
Union All Select 3,'王五',0,0,24,0,0
Union All Select 4,'阿扁',0,18,0,0,0
Union All Select 5,'花花',0,0,0,0,98
Union All Select 6,'黄河',0,0,0,45,0
Select * From @TmpReslut
IF OBJECT_ID('[TmpTable]') IS NOT NULL DROP TABLE [TmpTable]
GO
create table TmpTable(ID smallint,level varchar(20),Remark Varchar(20))
Insert Into TmpTable(ID,level,Remark)
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(Remark,charindex('-',Remark)-1)*1 and right(Remark,len(Remark)-charindex('-',Remark))*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 行受影响)
*/
可以--建立测试数据
IF OBJECT_ID('[TmpTable]') IS NOT NULL DROP TABLE [TmpTable]
GO
create table TmpTable(ID smallint,level varchar(20),Remark Varchar(20))
Insert Into TmpTable(ID,level,Remark)
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(Remark,charindex(''-'',Remark)-1)*1 and right(Remark,len(Remark)-charindex(''-'',Remark))*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 行受影响)
*/
--建立测试数据
create table TmpTable(ID smallint,level varchar(20),Remark Varchar(20))
Insert Into TmpTable(ID,level,Remark)
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
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 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
**/