我如何连接这些表？
提问于 2011-12-19 06:50:09
我有许多设备在不同的时间记录不同的数据，并希望在单个查询中获得所有数据，按时间排序。下面是我所拥有的各种表的一个示例：
CREATE TABLE location(
device_id INT, -- REFERENCES device(id)
timestamp DATETIME2 NOT NULL,
position GEOMETRY NOT NULL
)
CREATE TABLE temperature(
device_id INT, -- REFERENCES device(id)
timestamp DATETIME2 NOT NULL,
temp FLOAT NOT NULL
)我希望有一个单独的查询来连接device_id和时间戳上的表，当时间戳不匹配时，该查询包含空值。我正在寻找的输出格式的一个示例是：
device_id, timestamp, location, temperature
1, 2011/12/1 10:00:00, (35.1, 139.2), NULL
1, 2011/12/1 10:00:01, NULL, 9.0
1, 2011/12/1 10:00:02, (35.1, 139.2), 9.1我尝试过执行完全连接，但是不知道如何在没有大型CASE语句的情况下执行timestamp列(请记住，虽然我只显示了两个表，但这个表可以有更多)。
SELECT
location.device_id,
CASE WHEN location.timestamp IS NOT NULL THEN
location.timestamp
ELSE
temperature.timestamp
END as timestamp,
location,
temp
FROM
location
FULL JOIN temperature ON location.device_id = temperature.device_id
AND location.timestamp = temperature.timestamp
ORDER BY
timestamp有没有一种更简单的方法来编写这种查询？
回答 4
Stack Overflow用户
回答已采纳
发布于 2011-12-19 06:54:43
您可以使用COALESCE表达式。
SELECT
location.device_id,
COALESCE(location.timestamp, temperature.timestamp) as timestamp,
position,
temp
FROM
location
FULL JOIN temperature ON location.device_id = temperature.device_id
AND location.timestamp = temperature.timestamp
ORDER BY
timestamp;Stack Overflow用户
发布于 2011-12-19 06:56:48
可以，您可以使用温度表的外部连接。这将在温度表中没有匹配行的情况下返回空值。
Stack Overflow用户
发布于 2011-12-19 07:01:30
您需要一个COALESCE来获取设备id/时间戳，如下所示：
SELECT
COALESCE(l.device_id, t.device_id) as device_id,
COALESCE(l.timestamp, t.timestamp) as timestamp,
l.position as location,
t.temp as temperature
FROM location l
FULL JOIN temperature t ON l.device_id = t.device_id
AND l.timestamp = t.timestamp
ORDER BY 2还要注意，通过使用非常短的名称(l和t)为表添加别名，提高了可读性。
您可能希望检查您的订购-也许您想要ORDER BY 1, 2
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8558019
复制
腾讯云开发者
