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

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 NUMERIC(12,3) 
)

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.更好的解决方案

SELECT 
    TimePoint,
    MAX(CASE WHEN PollutantCode = 'PM2.5' AND StatusName ='大气温度' THEN Value ELSE -99 END) PM2_5大气温度,
    MAX(CASE WHEN PollutantCode = 'PM2.5' AND StatusName ='大气压力' THEN Value ELSE -99 END) PM2_5大气温度,
    MAX(CASE WHEN PollutantCode = 'PM10' AND StatusName ='大气温度' THEN Value ELSE -99 END) PM10大气温度,
    MAX(CASE WHEN PollutantCode = 'PM10' AND StatusName ='大气压力' THEN Value ELSE -99 END) PM10大气压力
FROM #TestTable GROUP BY TimePoint

转载于:https://www.cnblogs.com/Mysdm/p/11096790.html