Question

页: 1 我试图表明这一点:

+---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+
| 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

但是,由于它有了一个nes问,我不得不把它当作一种观点。目前,我认为这项工作的最佳问题是:

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)

我只希望每个职位一行,我希望是最新的。是否有任何建议? 在处理诸如软性删除和从理论上说不及使我想要的数据更容易查询等事项时,用观点来尝试并使生活更加容易。

预言如此之多!

Answer 1

您

 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

Answer 2

这样做是否有什么用处? 我不敢肯定这一点,因为它包含一个分局,它给你带来麻烦,但在不同的地方。

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     AND
      post_data.post_id             = post_id              AND
      post_data.time_stamp = (SELECT MAX(time_stamp)
                                  FROM post_data
                                  WHERE post_data.post_id = post_id);

业绩可能不是写家事。

友情链接