如何将多结果表连接为新增列？固定位置场景下的SQL实现

需求 #

一个reading可关联多个temp_reading，需要将同一reading下不同位置的温度读数及对应位置信息合并到同一行的不同列中（已知固定有2个位置），最终结果格式如下：

id, created_at, made_by, reading_location1, reading_location1_foreign_key, reading_location2, reading_location2_foreign_key

表结构 #

CREATE TABLE readings (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
made_by VARCHAR(30),
PRIMARY KEY (id)
);

CREATE TABLE temp_reading (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
reading FLOAT,
location_ID INT(11),
reading_id INT(11),
PRIMARY KEY (id),
FOREIGN KEY (location_id) REFERENCES location (id) ON DELETE SET NULL,
FOREIGN KEY (reading_id) REFERENCES readings (id) ON DELETE SET NULL
);

CREATE TABLE location (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY (id)
);

现有问题 #

你当前的查询会为每个reading对应的每个温度读数生成单独一行，无法实现多位置数据合并到同一行的需求：

SELECT readings.*, temp_reading.reading AS temp_reading, temp_reading.location_ID  AS reading_location1_foreign_key 
FROM readings 
RIGHT JOIN temp_reading ON temp_reading.readings_id = readings.id;

解决方案 #

由于位置数量固定为2个，可通过条件聚合实现行转列，同时关联location表获取位置信息：

SELECT
    r.id,
    r.created_at,
    r.made_by,
    -- 第一个位置的读数与位置ID
    MAX(CASE WHEN l.name = 'location1' THEN tr.reading END) AS reading_location1,
    MAX(CASE WHEN l.name = 'location1' THEN tr.location_ID END) AS reading_location1_foreign_key,
    -- 第二个位置的读数与位置ID
    MAX(CASE WHEN l.name = 'location2' THEN tr.reading END) AS reading_location2,
    MAX(CASE WHEN l.name = 'location2' THEN tr.location_ID END) AS reading_location2_foreign_key
FROM readings r
LEFT JOIN temp_reading tr ON r.id = tr.reading_id
LEFT JOIN location l ON tr.location_ID = l.id
GROUP BY r.id, r.created_at, r.made_by;

关键说明 #

• 使用LEFT JOIN保证即使某个reading没有对应位置的读数，也能保留该行记录（对应列值为NULL）
• CASE WHEN用于筛选指定位置的读数和位置ID，MAX()聚合函数将同一reading的多行数据合并为一行
• 请将'location1'、'location2'替换为实际的位置名称，也可以改用位置ID（如l.id = 1）进行筛选

内容的提问来源于stack exchange，提问作者Aaron