SQL语句实现定制化查询

最近在看一本讲技术的书，讲到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这么强大，应该还有更便捷的实现方式，正在学习中！