时间戳

首页 > 解决方案 > 如何合并来自两个不同来源的数据

sql - 如何合并来自两个不同来源的数据

问题描述

为我的“愚蠢”问题道歉，但我根本不熟悉 SQL。

我有以下两个查询，我只想返回这两者之间的区别：

SELECT [TIME],Consumption, temperature, speed
FROM database1 
WHERE Rev = '1' AND genDate = '2018-09-20' AND runDate = '2018-09-19' 
ORDER BY [TIME]

SELECT [TIME],Consumption, temperature, speed
FROM database1 
WHERE Rev = '1' AND genDate = '2018-09-19' AND runDate = '2018-09-18' 
ORDER BY [TIME]

我正在尝试这样的事情：

SELECT [TIME],Consumption, temperatureDiff, speedDiff

我想我应该使用INNER JOIN，但是这两个查询可能没有相同的行数。最终的目标是保留所有行，如果一个查询中没有一行，则在减法中使用 0 代替。

表格样本：

在此处输入图像描述

标签： sqlsql-server

解决方案

尽管OUTER JOINand ISNULL()orCOALESCE()答案有效，但有一个更复杂的选项，不需要JOIN.

SELECT
  [TIME],
  SUM(CASE WHEN genDate = '2018-09-19' THEN -consumption ELSE consumption END)   AS ConsumptionDiff,
  SUM(CASE WHEN genDate = '2018-09-19' THEN -temperature ELSE temperature END)   AS TemperatureDiff,
  SUM(CASE WHEN genDate = '2018-09-19' THEN -speed       ELSE speed       END)   AS SpeedDiff
FROM
  database1
WHERE
     (Rev = '1' AND genDate = '2018-09-20' AND runDate = '2018-09-19')
  OR (Rev = '1' AND genDate = '2018-09-19' AND runDate = '2018-09-18')
GROUP BY
  [Time]

有些人可能认为更整洁的另一种选择......

SELECT
  [TIME],
  SUM(relative_data.consumption)   AS ConsumptionDiff,
  SUM(relative_data.temperature)   AS TemperatureDiff,
  SUM(relative_data.speed      )   AS SpeedDiff
FROM
  database1
CROSS APPLY
(
  SELECT  consumption,  temperature,  speed WHERE genDate = '2018-09-20' AND runDate = '2018-09-19'
  UNION ALL
  SELECT -consumption, -temperature, -speed WHERE genDate = '2018-09-19' AND runDate = '2018-09-18'
)
  relative_data
WHERE
  Rev = '1'
GROUP BY
  [Time]

推荐阅读