如何基于可为NULL的不同列键实现SQL表自连接并关联数据

原始数据集 #

如上表，前两行、中间两行可通过key1与key2的字符串关联，最后一行无匹配项。需要合并为以下结果：

期望结果 #

你尝试的SQL #

SELECT 
    t1.start_date_time, t2.start_date_time AS end_date_time, t1.number1, t1.number2
FROM details_all AS t1
    JOIN details_all AS t2
      ON t1.key1 = t2.key2

问题所在 #

1. 连接类型错误：使用的是内连接（JOIN，即INNER JOIN），这种连接只会保留两边都有匹配的记录，直接过滤掉了最后一行无匹配项的记录，不符合保留无匹配行的需求。
2. 字段名错误：原始数据集中日期和时间是分开的Start Date和Time字段，但SQL中使用了不存在的start_date_time字段，导致无法正确获取数据。
3. 输出格式不符：需求需要分别输出开始日期、开始时间、结束日期、结束时间四个字段，而当前SQL将日期时间合并为一个字段输出，不符合预期结构。

修正后的SQL #

SELECT 
    t1.`Start Date` AS `Start Date`,
    t1.`Time` AS `Start Time`,
    t2.`Start Date` AS `End Date`,
    t2.`Time` AS `End Time`,
    t1.number1,
    t1.number2
FROM details_all AS t1
LEFT JOIN details_all AS t2
    ON t1.key1 = t2.key2
WHERE t1.key1 IS NOT NULL AND t1.key1 != ''

说明 #

• 改用LEFT JOIN，确保保留t1中所有带有效key1的记录，即使t2中没有匹配的key2。
• 正确拆分并输出Start Date、Time字段，对应开始和结束的日期时间。
• 添加WHERE条件过滤掉t1中无key1的记录（即那些作为结束标记的行，比如时间0:02、0:04的行，无需作为主行输出）。

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