22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[itemname] nvarchar(22),[itemid] int)
Insert #T1
select 0,N'温度',0 union all
select 1,N'湿度',1 union all
select 2,N'风速',2
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[value] int,[itemid] int,[rectime] DATETIME)
Insert #T2
select 1,10,0,N'2017-12-19 08:00:00' union all
select 2,80,1,N'2017-12-19 08:00:00' union all
select 3,12,2,N'2017-12-19 08:00:00' union all
select 4,15,0,N'2017-12-19 09:00:00' union all
select 5,60,1,N'2017-12-19 09:00:00' union all
select 6,21,2,N'2017-12-19 09:00:00' union all
select 7,31,0,N'2017-12-19 10:00:00' union all
select 8,45,1,N'2017-12-19 10:00:00' union all
select 9,65,2,N'2017-12-19 10:00:00'
Go
--测试数据结束
;WITH cte AS (
SELECT
itemname,
value,
rectime,
DENSE_RANK() OVER (
ORDER BY
rectime
) rn
FROM
#T1
JOIN
#T2
ON #T2.itemid = #T1.itemid
)
SELECT
rn AS id,
MAX( CASE cte.itemname
WHEN '风速 '
THEN cte.value
ELSE
0
END
) 风速 ,
MAX( CASE cte.itemname
WHEN '温度'
THEN cte.value
ELSE
0
END
) 温度,
MAX( CASE cte.itemname
WHEN '湿度'
THEN cte.value
ELSE
0
END
) 湿度,
rectime
FROM
cte
GROUP BY rn,rectime
SELECT ROW_NUMBER() OVER (ORDER BY A.RECTIME)-1 AS ID,
A.rectime,
MAX(CASE WHEN itemname='温度' THEN VALUE ELSE 0 END) AS 温度,
MAX(CASE WHEN itemname='湿度' THEN VALUE ELSE 0 END) AS 湿度,
MAX(CASE WHEN itemname='风速' THEN VALUE ELSE 0 END) AS 风速
FROM
(SELECT RECTIME,itemid,itemname
FROM
(SELECT DISTINCT RECTIME FROM TABLE_2) AS B
CROSS JOIN
TABLE_1 A) AS A
LEFT JOIN TABLE_2 B ON A.itemid=B.itemid AND A.rectime=B.rectime
GROUP BY A.rectime
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[itemname] nvarchar(22),[itemid] int)
Insert #T1
select 0,N'温度',0 union all
select 1,N'湿度',1 union all
select 2,N'风速',2
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[value] int,[itemid] int,[rectime] DATETIME)
Insert #T2
select 0,10,0,N'2017-12-19 08:00:00' union all
select 1,80,1,N'2017-12-19 08:00:00' union all
select 2,12,2,N'2017-12-19 08:00:00'
Go
--测试数据结束
;WITH cte AS (
SELECT
itemname,
value,
rectime,
DENSE_RANK() OVER (PARTITION BY
rectime
ORDER BY
rectime
) - 1 rn
FROM
#T1
JOIN
#T2
ON #T2.itemid = #T1.itemid
)
SELECT
rn AS id,
MAX( CASE cte.itemname
WHEN '风速 '
THEN cte.value
ELSE
0
END
) 风速 ,
MAX( CASE cte.itemname
WHEN '温度'
THEN cte.value
ELSE
0
END
) 温度,
MAX( CASE cte.itemname
WHEN '湿度'
THEN cte.value
ELSE
0
END
) 湿度,
rectime
FROM
cte
GROUP BY rn,rectime