mysql - MySQL —在 View 中获取不同的行

好的，给你。我试图做出一个显示如下的视图：

+---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+
| post_id | status  | title | body   | ip_address | time_stamp          | category_name | sub_category_name | post_type   |
+---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+
|       1 | enabled | test  | test 2 |            | 2010-05-20 01:22:17 | For Sale      | Computers         | transaction |
+---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+
1 row in set (0.00 sec)

执行此操作的查询是：

SELECT post.id AS post_id,
post.status AS status,
post_data.title AS title,
post_data.body AS body,
post_data.ip_address AS ip_address,
post_data.time_stamp AS time_stamp,
post_category.name AS category_name,
post_sub_category.name AS sub_category_name,
post_category.type AS post_type
FROM post,
(
SELECT * FROM post_data WHERE post_data.post_id = post_id ORDER BY post_data.post_id DESC LIMIT 1
) AS post_data,
post_sub_category,
post_category
WHERE
post.sub_category_id = post_sub_category.id AND
post_sub_category.category_id = post_category.id

但是，因为它有一个嵌套查询，所以我不能将它用作视图。目前，我能想到的最好的视图查询是：

SELECT
post.id AS post_id,
post.status AS status,
post_data.title AS title,
post_data.body AS body,
post_data.ip_address AS ip_address,
post_data.time_stamp AS time_stamp,
post_category.name AS category_name,
post_sub_category.name AS sub_category_name,
post_category.type AS post_type
FROM post,
post_data,
post_sub_category,
post_category
WHERE
post.sub_category_id = post_sub_category.id AND
post_sub_category.category_id = post_category.id
ORDER BY post_data.id DESC

但这又回来了：

+---------+---------+-------+-----------+----------------+---------------------+---------------+-------------------+-------------+
| post_id | status  | title | body      | ip_address     | time_stamp          | category_name | sub_category_name | post_type   |
+---------+---------+-------+-----------+----------------+---------------------+---------------+-------------------+-------------+
|       1 | enabled | test  | test 2    |                | 2010-05-20 01:22:17 | For Sale      | Computers         | transaction |
|       1 | enabled | TEST  | TEST BODY | 192.168.10.155 | 2010-05-19 23:09:15 | For Sale      | Computers         | transaction |
+---------+---------+-------+-----------+----------------+---------------------+---------------+-------------------+-------------+
2 rows in set (0.00 sec)

我只想要每个post_id一行，我希望它是最新的。有人有什么建议吗？在处理诸如软删除之类的事情时，我使用视图来尝试使生活变得更轻松，并且从理论上讲，从长远来看，使查询我想要的数据变得更轻松。
提前谢谢你！

最佳答案

你可以用

 GROUP BY post_data.post_id HAVING MAX(post_data.time_stamp)

只得到最新的一排。所以完整的查询可能如下所示：

SELECT
post.id AS post_id,
post.status AS status,
post_data.title AS title,
post_data.body AS body,
post_data.ip_address AS ip_address,
post_data.time_stamp AS time_stamp,
post_category.name AS category_name,
post_sub_category.name AS sub_category_name,
post_category.type AS post_type
FROM post,
post_data,
post_sub_category,
post_category
WHERE
post.sub_category_id = post_sub_category.id AND
post_sub_category.category_id = post_category.id
GROUP BY post_data.post_id HAVING MAX(post_data.time_stamp)
ORDER BY post_data.id DESC

关于mysql - MySQL —在 View 中获取不同的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2871320/