如何编写SQL查询指定individualid下非最新的未加载record_id

原有SQL错误点 #

• NULL值判断语法错误：SQL中判断值是否为NULL不能使用=运算符，必须用IS NULL，原有语句record_id = NULL永远返回假，导致无查询结果。
• 关联字段歧义：WHERE子句中未指定record_id所属的表，你需要筛选的是右表t2中没有匹配到的记录，应明确写t2.record_id IS NULL。
• 额外优化建议：精准匹配场景下直接用=代替LIKE，查询效率更高。

修复后可正常运行的SQL（保留原逻辑） #

SELECT t1.record_id AS recid, t1.individualid AS ind, t1.load_status AS load
  FROM tbl t1
  LEFT JOIN (SELECT record_id, individualid , load_status, created_at
               FROM tbl
              INNER JOIN( SELECT individualid AS indid,
                                 MAX( created_at) AS max_create
                            FROM tbl
                           GROUP BY individualid) ms ON tbl.individualid = indid 
                                                     AND tbl.created_at = max_create
              WHERE tbl.individualid = 'nest22'
                AND tbl.load_status != 'LOADED') t2 ON t1.record_id = t2.record_id
 WHERE t1.load_status != 'LOADED'
   AND t1.individualid = 'nest22'
   AND t2.record_id IS NULL;

更简洁的实现方案 #

不用多层关联，直接用子查询取指定用户的最大创建时间做筛选即可，逻辑更清晰性能更好：

SELECT record_id
FROM tbl
WHERE individualid = 'nest22'
  AND load_status != 'LOADED'
  AND created_at < (SELECT MAX(created_at) FROM tbl WHERE individualid = 'nest22');

结果验证 #

针对你提供的测试数据，上述语句返回的record_id为2、3，完全符合预期。

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