sql - 帮助将 SQL 嵌套子查询转换为 mysql 4.0 兼容性的连接查询

Question

我有一个嵌套的 SQL 查询:

SELECT DISTINCT(topic_id)
FROM bb_posts 
WHERE topic_id NOT IN ( 
  SELECT topic_id FROM bb_posts 
  WHERE poster_id = $user_id AND post_status = 0 ) 
ORDER BY post_time DESC

我的网络服务器运行 MySQL 4.0，它不允许嵌套查询。一些 SQL 大师可以使用 JOIN 术语建议相同的查询吗？我试了又试了……但我想不通。感觉很傻。

Answer 1

假设 topic_id 不可为空，您可以:

SELECT DISTINCT(T1.topic_id)
FROM bb_posts As T1
    Left Join bb_posts As T2
        On T2.topic_id = T1.topic_id
            And T2.poster_id = $user_id
            And T2.post_status = 0
Where T2.topic_id Is Null
ORDER BY T1.post_time DESC