如何实现含NULL值的表连接？Suhu与RH表连接结果不符求助

你的问题出在仅用ID_ROOM作为LEFT JOIN的关联条件：由于两张表中ID_ROOM均为1，Suhu表的每一行都会与RH表的所有行进行匹配，导致后两行也出现RH的数值，而非预期的NULL。

要实现需求，核心是给两张表的行添加分组行号，然后同时用ID_ROOM和行号作为关联条件，让Suhu的第N行仅匹配RH的第N行，超出RH行数的Suhu行自然会得到NULL值。

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数为同一ID_ROOM下的行分配序号：

SELECT 
    S.ID_ROOM,
    S.SUHU_MIN,
    S.SUHU_MAX,
    R.RH_MIN,
    R.RH_MAX
FROM (
    -- 给Suhu表添加分组行号，按SUHU_MIN排序保证顺序（可根据实际需求调整排序字段）
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID_ROOM ORDER BY SUHU_MIN) AS row_num
    FROM SUHU
) S
LEFT JOIN (
    -- 给RH表添加分组行号，按RH_MIN排序保证顺序
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID_ROOM ORDER BY RH_MIN) AS row_num
    FROM RH
) R ON S.ID_ROOM = R.ID_ROOM AND S.row_num = R.row_num;

不支持窗口函数的旧版数据库（如MySQL 5.x） #

使用用户变量生成行号：

SELECT 
    S.ID_ROOM,
    S.SUHU_MIN,
    S.SUHU_MAX,
    R.RH_MIN,
    R.RH_MAX
FROM (
    SELECT 
        *,
        @row_suhu := IF(@prev_id = ID_ROOM, @row_suhu + 1, 1) AS row_num,
        @prev_id := ID_ROOM
    FROM SUHU, (SELECT @row_suhu := 0, @prev_id := NULL) AS vars
    ORDER BY ID_ROOM, SUHU_MIN -- 按此排序保证行号顺序与原表一致
) S
LEFT JOIN (
    SELECT 
        *,
        @row_rh := IF(@prev_id_rh = ID_ROOM, @row_rh + 1, 1) AS row_num,
        @prev_id_rh := ID_ROOM
    FROM RH, (SELECT @row_rh := 0, @prev_id_rh := NULL) AS vars
    ORDER BY ID_ROOM, RH_MIN
) R ON S.ID_ROOM = R.ID_ROOM AND S.row_num = R.row_num;

说明 #

• 排序字段（ORDER BY SUHU_MIN、ORDER BY RH_MIN）可根据你的实际数据顺序需求调整，确保行号的分配和你期望的匹配顺序一致。
• 关联时同时匹配ID_ROOM和row_num，就能让Suhu表的前两行对应RH表的两行，后续无对应行的Suhu行，RH字段自动填充为NULL，符合你的预期结果。

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