如何使用 join 编写等效的 sql 查询? [英] How can i write equivalent sql query with join?

问题描述

原始 sql 查询:

SELECT *
FROM
  (SELECT p.id,
          p.title,
          p.mark,

     (SELECT max(created)
      FROM comments c
      WHERE c.post_id=p.id
        AND c.mark=1) AS latest_at
   FROM posts p) AS Post
WHERE Post.latest_at IS NOT NULL
ORDER BY latest_at DESC LIMIT 10

我正在尝试使用 join 编写等效的 sql 查询.我该怎么做?

I'm trying to write equivalent sql query with join. How can i do it?

mysql> describe posts;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   |     | NULL    |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| body        | text         | YES  |     | NULL    |                |
| category_id | int(11)      | YES  |     | NULL    |                |
| tags        | varchar(50)  | YES  |     | NULL    |                |
| mark        | tinyint(4)   | NO   |     | 1       |                |
| created     | datetime     | YES  |     | NULL    |                |
| modified    | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> describe comments;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| post_id  | int(11)      | NO   | MUL | NULL    |                |
| name     | varchar(255) | NO   |     | NULL    |                |
| email    | varchar(255) | NO   |     | NULL    |                |
| body     | varchar(500) | NO   |     | NULL    |                |
| mark     | tinyint(4)   | NO   |     | 1       |                |
| created  | datetime     | YES  |     | NULL    |                |
| modified | datetime     | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

任何答案将不胜感激.提前致谢.

Any answer will be highly appreciated. Thanks in advance.

推荐答案

试试这个:

SELECT p.id, p.title, p.mark,
       c.latest_at     
FROM posts p
LEFT JOIN (
   SELECT post_id, MAX(created) AS latest_at
   FROM comments
   WHERE mark = 1
   GROUP BY post_id 
) AS c ON c.post_id = p.id 
WHERE c.latest_at IS NOT NULL
ORDER BY c.latest_at DESC LIMIT 10

或者只是这个:

SELECT p.id, p.title, p.mark,
       c.latest_at     
FROM posts p
INNER JOIN (
   SELECT post_id, MAX(created) AS latest_at
   FROM comments
   WHERE mark = 1
   GROUP BY post_id 
) AS c ON c.post_id = p.id 
ORDER BY c.latest_at DESC LIMIT 10

因为第一个查询的 WHERE 子句的 c.latest_at IS NOT NULL 谓词将 LEFT JOIN 变成了 INNER加入.

because the c.latest_at IS NOT NULL predicate of the WHERE clause of the first query turns LEFT JOIN into an INNER JOIN.

这篇关于如何使用 join 编写等效的 sql 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！