有3个表1、学生表 结构
学号
姓名
……
2、成绩表
学号
科目
成绩
3、补考成绩表
学号
科目
成绩
要求查询的结果
学号 姓名 科目1的成绩 科目2的成绩 科目3的成绩 ……并且要求如果某人某科有补考成绩的就显示此科目补考的成绩
学号
姓名
……
2、成绩表
学号
科目
成绩
3、补考成绩表
学号
科目
成绩
要求查询的结果
学号 姓名 科目1的成绩 科目2的成绩 科目3的成绩 ……并且要求如果某人某科有补考成绩的就显示此科目补考的成绩
常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题
下面就讨论这个问题:/*-- 数据测试环境 --*/
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GOcreate table tb(单位名称 varchar(10),日期 datetime,销售额 int)
insert into tb
select 'A单位','2001-01-01',100
union all select 'B单位','2001-01-02',101
union all select 'C单位','2001-01-03',102
union all select 'D单位','2001-01-04',103
union all select 'E单位','2001-01-05',104
union all select 'F单位','2001-01-06',105
union all select 'G单位','2001-01-07',106
union all select 'H单位','2001-01-08',107
union all select 'I单位','2001-01-09',108
union all select 'J单位','2001-01-11',109/*-- 要求结果
日期 A单位 B单位 C单位 D单位 E单位 F单位 G单位 H单位 I单位 J单位
---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*//*-- 常规处理方法*/
declare @sql varchar(8000)
set @sql='select 日期=convert(varchar(10),日期,120)'
select @sql=@sql+',['+单位名称
+']=sum(case 单位名称 when '''+单位名称+''' then 销售额 else 0 end)'
from(select distinct 单位名称 from tb) a
exec(@sql+' from tb group by convert(varchar(10),日期,120)')
/*-- 问题: 如果单位很多,这时,@SQL的值就会被截断,从而出错.*//*--下面给出三种解决办法:--*/--/*-- 方法1. 多个变量处理--定义变量,估计需要多少个变量才能保存完所有数据
declare @sql0 varchar(8000),@sql1 varchar(8000)
--,...@sqln varchar(8000)--生成数据处理临时表
select id=identity(int,0,1),groupid=0
,值=',['+单位名称 +']=sum(case 单位名称 when '''
+单位名称+''' then 销售额 else 0 end)'
into #temp from(select distinct 单位名称 from tb) a--分组临时表,判断慨最多多少个单位可以组合成一个不超过8000的字符串,这里取假设为5个
update #temp set groupid=id/5 --5为每组的单位个数--生成SQL语句处理字符串
--初始化
select @sql0=''
,@sql1=''
-- ...
-- ,@sqln --得到处理字符串
select @sql0=@sql0+值 from #temp where groupid=0 --第一个变量
select @sql1=@sql1+值 from #temp where groupid=1 --第二个变量
--select @sqln=@sqln+值 from #temp where groupid=n --第n个变量--查询
exec('select 日期=convert(varchar(10),日期,120)'
+@sql0+@sql1
-- ...+@sqln
+' from tb group by convert(varchar(10),日期,120)
')--删除临时表
drop table #temp/*
优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分
缺点:要自行估计处理的数据,估计不足就会出错
*/
--*/
--/*--方法2. bcp+isql--因为要用到bcp+isql,所以需要这些信息
declare @servername varchar(250),@username varchar(250),@pwd varchar(250)
select @servername='zj' --服务器名
,@username='' --用户名
,@pwd='' --密码declare @tbname varchar(50),@sql varchar(8000)--创建数据处理临时表
set @tbname='[##temp_'+convert(varchar(40),newid())+']'
set @sql='create table '+@tbname+'(值 varchar(8000))
insert into '+@tbname+' values(''create view '
+stuff(@tbname,2,2,'')+' as
select 日期=convert(varchar(10),日期,120)'')'
exec(@sql)set @sql='insert into '+@tbname+'
select '',[''+单位名称+'']=sum(case 单位名称 when ''''''
+单位名称+'''''' then 销售额 else 0 end)''
from(select distinct 单位名称 from tb) a'
exec(@sql)set @sql='insert into '+@tbname+'
values(''from tb group by convert(varchar(10),日期,120)'')'
exec(@sql)--生成创建视图的文件,注意使用了文件:c:\temp.txt
set @sql='bcp "'+@tbname+'" out "c:\temp.txt" /S"'
+@servername+'" /U"'+@username+'" /P"'+@pwd+'" /c'
exec master..xp_cmdshell @sql--删除临时表
set @sql='drop table '+@tbname
exec(@sql)--调用isql生成数据处理视图
set @tbname=stuff(@tbname,2,2,'')
set @sql='isql /S"'+@servername
+case @username when '' then '" /E' else '" /U"'+@username+'" /P"'+@pwd+'"' end
+' /d"'+db_name()+'" /i"c:\temp.txt"'exec master..xp_cmdshell @sql--调用视图,显示处理结果
set @sql='select * from '+@tbname+'
drop view '+@tbname
exec(@sql)/*
优点:程序自动处理,不存在判断错误的问题
缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限
*/
--*/
--/*-- 方法3. 多个变量处理,综合了方法1及方法2的优点, 解决了方法1中需要人为判断的问题,自动根据要处理的数据量进行变量定义,同时又避免了方法2的繁琐declare @sqlhead varchar(8000),@sqlend varchar(8000)
,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
,@i int,@ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a=',['+单位名称 +']=sum(case 单位名称 when '''
+单位名称+''' then 销售额 else 0 end)'
into # from(select distinct 单位名称 from tb) a--判断需要多少个变量来处理
select @i=max(len(a)) from #
print @i
set @i=7800/@i--分组临时表
update # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead='''select 日期=convert(varchar(10),日期,120)'''
,@sqlend=''' from tb group by convert(varchar(10),日期,120)'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,8000)--执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)--删除临时表
drop table #
--*/
方法3中,关键要做修改的是下面两句,其他基本上不用做改变:--生成数据处理临时表,修改a=后面的内容为相应的处理语句
select id=identity(int,0,1),gid=0
,a=',['+code+']=sum(case b.c_code when '''
+code+''' then b.value else 0 end)'
into # from #Class--生成数据处理语句,将@sqlhead,@sqlend赋值为相应的处理语句头和尾
select @sqlhead='''select a.id,a.name,a.code'''
,@sqlend=''' from #Depart a,#Value b where a.Code=b.d_Code group by a.id,a.code,a.name'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
set @sql='select a.学号,a.姓名'
select @sql=@sql+',['+科目
+'的成绩]=sum(case b.科目 when '''+科目+''' then case when c.学号 is not null then c.成绩 else b.成绩 end else 0 end)'
from 成绩表
group by 科目exec(@sql+' from 学生表 a inner join 成绩表 b on a.学号=b.学号 left join 补考成绩表 c on a.学号=c.学号 and b.科目=c.科目 group by a.学号,a.姓名')
(id int,name varchar(10))
insert into #stu
select '01', '驪' union all
select '02', '李' union all
select '03', '王'create table #chenji
(id int,kemu varchar(10),chenji int)
insert into #chenji
select 01, '數學', 80 union all
select 01, '語文', 70 union all
select 01, '物理', 50 union all
select 02, '數學', 80 union all
select 02, '語文', 70 union all
select 02, '物理', 88 union all
select 03, '語文', 100 union all
select 03, '數學', 100 union all
select 03, '物理', 100
create table #bukao
( id int,kemu varchar(10),chenji int)
insert into #bukao
select 01, '物理', 66
select id,name,sum(case when kemu='數學' then chenji else 0 end)as '數學',
sum(case when kemu='語文' then chenji else 0 end)as '語文',
sum(case when kemu='物理' then chenji else 0 end)as '物理'
from
(
select c.name, a.id,a.kemu,chenji=case when a.id=b.id and a.kemu=b.kemu then b.chenji else a.chenji end
from #chenji a left join #bukao b on a.id=b.id and a.kemu=b.kemu
left join #stu c on a.id=c.id
)a
group by id,name
order by idid name 數學 語文 物理
----------- ---------- ----------- ----------- -----------
1 驪 80 70 66
2 李 80 70 88
3 王 100 100 100(3 row(s) affected)
select c.name, a.id,a.kemu,chenji=case when a.id=b.id and a.kemu=b.kemu then b.chenji else a.chenji end
into #t
from #chenji a left join #bukao b on a.id=b.id and a.kemu=b.kemu
left join #stu c on a.id=c.id declare @s varchar(1000)
set @s='select id,name '
select @s=@s+',sum(case when kemu='''+kemu+''' then chenji else 0 end)as '''+kemu+''''
from #t group by kemu
select @s=@s+' from #t group by id,name order by id'
exec(@s)id name 物理 語文 數學
----------- ---------- ----------- ----------- -----------
1 驪 66 70 80
2 李 88 70 80
3 王 100 100 100
select A.学号,a.姓名,(case when b2.成绩 is null then b.成绩 else b2.成绩 end) 数学,
(case when c2.成绩 is null then c.成绩 else c2.成绩 end) 语文,
(case when d2.成绩 is null then d.成绩 else d2.成绩 end) 物理 from 学生表 a
left join (select * from 成绩表 where 科目='数学') b on a.学号=b.学号
left join (select * from 成绩表 where 科目='语文') c on a.学号=c.学号
left join (select * from 成绩表 where 科目='物理') d on a.学号=d.学号
left join (select * from 补考成绩表 where 科目='数学') b2 on a.学号=b2.学号
left join (select * from 补考成绩表 where 科目='语文') c2 on a.学号=c2.学号
left join (select * from 补考成绩表 where 科目='物理') d2 on a.学号=d2.学号
select A.学号,a.姓名,isnull(b2.成绩,b.成绩) 数学,isnull(c2.成绩,c.成绩) 语文,
isnull(d2.成绩,d.成绩) 物理 from #学生表 a
left join (select * from #成绩表 where 科目='数学') b on a.学号=b.学号
left join (select * from #成绩表 where 科目='语文') c on a.学号=c.学号
left join (select * from #成绩表 where 科目='物理') d on a.学号=d.学号
left join (select * from #补考成绩表 where 科目='数学') b2 on a.学号=b2.学号
left join (select * from #补考成绩表 where 科目='语文') c2 on a.学号=c2.学号
left join (select * from #补考成绩表 where 科目='物理') d2 on a.学号=d2.学号
用动态的吧
大家都有一点忽视了，科目不是死的，是灵活的，只要在表中就可以
-------------
這樣的話，就要用到我上面寫的動態語句了
set @str=''
select @str=@str+'sum (case when Course = ' + ''''+Course+'''' +'then Grade else 0 end )'+''''+Course +''''+ ',' from (select distinct Course from grade)a
select @sql ='select No,'+left(@str,len(@str)-1) +' from grade group by No'
exec(@sql)