联合查询(姑且称之为联合查询)的最差解与较优解
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
![]()
作者:丹麦的面包不单卖
出处: http://www.cnblogs.com/MySdm/
微信公众账号:丹麦的面包不单卖
扫一扫左侧的二维码(或者长按识别二维码),关注本人微信公共号,获取更多资源。(其实啥都没有,我只是单纯的占个位置)
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。