最近在看一本讲技术的书,讲到sql时,有一个比较有趣的例子,在数据库中的material表存储格式如下,mclass是材料类型,mdosage是材料用量
Id mclass mdosage
1 A 66
2 B 56
3 C 46
4 D 36
5 E 26
现在想查询出如下形式的结果
Id A B C D E
1 66
2 56
3 46
4 36
5 26
在sqlserver中可以利用case语句来实现上述的效果,代码如下
select id,
case mclass when 'A' then cast(mdosage as varchar) else ' ' end as 'A',
case mclass when 'B' then cast(mdosage as varchar) else ' ' end as 'B ',
case mclass when 'C' then cast(mdosage as varchar) else ' ' end as 'C ',
case mclass when 'D' then cast(mdosage as varchar) else ' ' end as 'D ',
case mclass when 'E' then cast(mdosage as varchar) else ' ' end as 'E '
from material
领略了一下sql语句的强大,但是感觉有点不对劲,实际应用中要是同一个id用到了多种材料,而且材料的种类也是未知的话,又该怎么办呢?也就是对如下的查询结果
Id A B C D E
1 66
2 56
3 46
4 36
5 26
5 26
应该到得如下结果,才算合理
Id A B C D E
1 66
2 56
3 46
4 36
5 26 26
我想到的思路是,首先查询出一共有多少种类的材料,然后构建形似(Id A B C D E)结构的临时表#tempInfo,然后用游标遍历material表,根据实际情况判断当前记录的ID在#tempInfo中是否存在,如果不存在则插入,否则更新,sql语句如下所示,共分两个大部分,第一部分构建临时表,第二部分从material表中读取数据到临时表中
create table #tempInfo (id varchar(10))--创建临时表
declare @curClass cursor; --遍历材料类别的游标,类别将作为#tempInfo表的列
declare @class varchar(10) ; --存储遍历到的当前项的类别
declare @sqlAlt nvarchar(200) -- 存储数据表修改语句
declare @sqlAltSum nvarchar(500) -- 存储数据表修改语句综合
declare @dosDefault nvarchar(10) -- 对应列的默认值
set @dosDefault = '0'
set @sqlAlt = 'alter table #tempinfo add '
set @curClass = Cursor for select distinct
mclass from material
--将类别作为临时表的列添加到表中
open @curClass
fetch from @curClass into @class
while @@fetch_status = 0
begin
set @sqlAltSum = @sqlAlt + '['+@class +']'+'varchar(10) default('+@dosDefault+')'
print @sqlAltSum
exec(@sqlAltSum)
fetch from @curClass into @class
end
close @curClass;
deallocate @curClass;
--依次循环material表的各行,将其添加到临时表对应的行中
declare @curMaterial cursor;
declare @id varchar(10)--material中id的存贮字段
declare @mClass varchar(10)--material中mclass的存贮字段
declare @mDosage int --material中msdosage的存贮字段
declare @sqlUpdate nvarchar(500)
set @sqlUpdate = ' '
set @curMaterial = Cursor for select * from material
open @curMaterial
fetch from @curMaterial into @id,@mClass,@mDosage
while @@fetch_status = 0
begin
if exists(select id from#tempInfo where id=@id)
begin
set @sqlUpdate= ' update #tempInfoset '
+'['+@mClass+'] ='+cast(@mDosage as varchar)+' where id='+ @id
exec (@sqlUpdate)
end
else
begin
set @sqlUpdate= ' insert into#tempInfo (id,'+
'['+@mClass+']) values('+@id+','+cast(@mDosage as varchar)+')'
exec (@sqlUpdate)
end
fetch from @curMaterial into @id,@mClass,@mDosage
end
close @curMaterial
deallocate @curMaterial
执行完上述语句后,查询#tempInfo表格,得到如下结果
select * from #tempInfo
Id A B C D E
1 66 0 0 0 0
2 0 56 0 0 0
3 0 0 46 0 0
4 0 0 0 36 0
5 0 0 0 26 26
有点遗憾,试了好多种方式,无法实现默认值是””,只能用0。个人感觉上述是最基本的做法,sql这么强大,应该还有更便捷的实现方式,正在学习中!