sql 理解

àì夳堔傛蜴生んèń 2022-08-06 05:12 87阅读 0赞

select b.\*,  
b.model\_ent\_name+cast(m.year as varchar)as modelname,  
m.index\_value as val  
  
into \#tb  
from  
(  
select a.\*,erm.model\_ent\_name  
from  
(select w.uuid,w.indexdef\_id,  
i.index\_name, i.index\_def,  
case when w.years=2014 then w.actual\_value else 0 end as k1,  
case when w.years=2013 then w.actual\_value else 0 end as k2,  
case when w.years=2012 then w.actual\_value else 0 end as k3,  
case when w.years=2011 then w.actual\_value else 0 end as k4,  
case when w.years=2010 then w.actual\_value else 0 end as k5  
from bm\_work w,bm\_index i  
where w.indexdef\_id=i.uuid  
and w.ent\_id='2c90e4da49514c750149515eb56f0003' and w.summary\_state=2  
and years=2014  
)a left join bm\_ent\_rel\_me erm on a.uuid=erm.bmwork\_id  
  
)b  
  
  
left join bm\_model\_ent m on b.indexdef\_id=m.index\_def\_id and b.model\_ent\_name=m.model\_ent\_name  
  
  
declare @sql varchar(8000)  
select @sql=isnull(@sql+',','')+modelname from \#tb group by modelname  
set @sql = 'select \* from \#tb pivot (sum(val) for modelname in ('+@sql+') )a'  
exec (@sql)

drop table \#tb

![Center][]

需要将上图变为下图结果。

![Center 1][]

实现行转列，及把第一张图片中红圈的部分的内容放置到 列上，作为列的标题。。。

declare @sql varchar(8000)  
select @sql=isnull(@sql+',','')+modelname from \#tb group by modelname  
set @sql = 'select \* from \#tb pivot (sum(val) for modelname in ('+@sql+') )a' //val 为值 modelname为需要将行转列  
exec (@sql)

//写这种sql的思路就是，先自己写出类似图一 的结果，确定那一个需要行转列。。这里我们就是需要modelname 这一列的值，都放过去作为列的属性名。。。看图两个圈的描述吧。具体本人也不是很好描述。

[Center]: /images/20220806/41e73f968686453db1a4c9c01afa68e1.png
[Center 1]: /images/20220806/d33827d121fa41f1bed13efa7d895b94.png