原始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查询。我该怎么做?
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 | |
+----------+--------------+------+-----+---------+----------------+
任何答案将不胜感激。提前致谢。
试试这个:
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
因为第一个查询c.latest_at IS NOT NULL
的WHERE
子句的谓词LEFT JOIN
变成了INNER JOIN
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句