欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

联合查询(姑且称之为联合查询)的最差解

程序员文章站 2022-07-09 20:50:38
1.数据如下 TimePoint | PollutantCode | StatusName | Value | | | 2019 03 16 01:00:00.000|PM10|大气温度|11.096 2019 03 16 01:00:00.000|PM10|大气压力|102.354 2019 03 ......

1.数据如下

timepoint pollutantcode statusname value
2019-03-16 01:00:00.000 pm10 大气温度 11.096
2019-03-16 01:00:00.000 pm10 大气压力 102.354
2019-03-16 01:00:00.000 pm2.5 大气温度 14.525
2019-03-16 01:00:00.000 pm2.5 大气压力 101.358
2019-03-16 02:00:00.000 pm10 大气温度 10.134
2019-03-16 02:00:00.000 pm10 大气压力 102.312
2019-03-16 02:00:00.000 pm2.5 大气温度 13.883
2019-03-16 02:00:00.000 pm2.5 大气压力 101.3
2019-03-16 03:00:00.000 pm10 大气温度 10.368
2019-03-16 03:00:00.000 pm10 大气压力 102.249
2019-03-16 03:00:00.000 pm2.5 大气温度 14.033
2019-03-16 03:00:00.000 pm2.5 大气压力 101.258

2.要求

12条数据可以变成3条数据,并且列变成(timepoint,pm2_5大气温度,pm2_5大气压力,pm10大气温度,pm10大气压力)

3.建表

if object_id('tempdb..#testtable') is not null
    drop table #testtable;

create table #testtable
(
    id int identity(1,1),  
    timepoint datetime, 
    pollutantcode varchar(10),
    statusname nvarchar(50),
    value varchar(50)
)

insert into #testtable(timepoint,pollutantcode,statusname,value)
select       '2019-03-16 01:00:00.000','pm10', '大气温度','11.096'
union select '2019-03-16 01:00:00.000' , 'pm10','大气压力','102.354'
union select '2019-03-16 01:00:00.000' , 'pm2.5','大气温度','14.525'
union select '2019-03-16 01:00:00.000' , 'pm2.5','大气压力','101.358'
union select '2019-03-16 02:00:00.000' , 'pm10','大气温度','10.134'
union select '2019-03-16 02:00:00.000' , 'pm10','大气压力','102.312'
union select '2019-03-16 02:00:00.000' , 'pm2.5','大气温度','13.883'
union select '2019-03-16 02:00:00.000' , 'pm2.5','大气压力','101.3'
union select '2019-03-16 03:00:00.000' , 'pm10','大气温度','10.368'
union select '2019-03-16 03:00:00.000' , 'pm10','大气压力','102.249'
union select '2019-03-16 03:00:00.000' , 'pm2.5','大气温度','14.033'
union select '2019-03-16 03:00:00.000' , 'pm2.5','大气压力','101.258'

4.show your the code(最差解)

select a.timepoint,a.value pm2_5大气温度,b.value pm2_5大气压力,d.value pm10大气温度,c.value pm10大气压力
from
(
    select *
    from #testtable
    where statusname = '大气温度'
          and pollutantcode = 'pm2.5'
) a
    left join
    (
        select *
        from #testtable
        where statusname = '大气压力'
              and pollutantcode = 'pm2.5'
    ) b
        on a.timepoint = b.timepoint
     left join
    (
        select *
        from #testtable
        where statusname = '大气压力'
              and pollutantcode = 'pm10'
    ) c
        on a.timepoint = c.timepoint
     left join
    (
        select *
        from #testtable
        where statusname = '大气温度'
              and pollutantcode = 'pm10'
    ) d
        on a.timepoint = d.timepoint

5.这种不知道算不算行转列...应该有更好的解决方案...期待有缘人可以解答...