gpt4 book ai didi

mysql - 按连接表的多行排序连接查询

转载 作者:太空宇宙 更新时间:2023-11-03 12:35:53 27 4
gpt4 key购买 nike

我有一个posts 表和一个包含meta_keymeta_value 列的postmeta 表。我在下面包含了一个简化的表格结构(带有一些演示数据)。

CREATE TABLE `posts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `posts` (`id`, `title`) VALUES
(1,'First post'),
(2,'Second post'),
(3,'Third post'),
(4,'Fourth post');

CREATE TABLE `postmeta` (
`meta_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`meta_key` varchar(200) NOT NULL DEFAULT '',
`meta_value` text NOT NULL,
PRIMARY KEY (`meta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(1,1,'rating','80'),
(2,1,'total_votes','500'),
(3,2,'rating','80'),
(4,2,'total_votes','501'),
(5,3,'rating','95'),
(6,3,'total_votes','200');

帖子需要按评分排序。没有任何评级的帖子也需要包括在内,因此 LEFT JOIN。没问题:

SELECT *
FROM posts p
LEFT JOIN postmeta m ON p.id = m.post_id AND m.meta_key = 'rating'
ORDER BY m.meta_value DESC

但是,对于具有相同评级的帖子,我想根据存储在 postmeta 表的另一行中的总票数进一步排序。在上面的示例中,第一个和第二个帖子的评分为 80,但由于第二个帖子的投票更多,因此它应该排在第一个帖子之前。您将如何在单个查询中实现这一点?

最佳答案

SELECT * <-- you'll want to specify columns here
FROM posts p
LEFT JOIN postmeta m ON p.id = m.post_id AND m.meta_key = 'rating'
LEFT JOIN postmeta mv ON p.id = mv.post_id AND mv.meta_key = 'total_votes'
ORDER BY m.meta_value DESC, mv.meta_value DESC

SQL Fiddle Example

输出:

| ID |       TITLE | META_ID | POST_ID | META_KEY | META_VALUE |
----------------------------------------------------------------
| 3 | Third post | 5 | 3 | rating | 95 |
| 2 | Second post | 3 | 2 | rating | 80 |
| 1 | First post | 1 | 1 | rating | 80 |
| 4 | Fourth post | (null) | (null) | (null) | (null) |

关于mysql - 按连接表的多行排序连接查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13239485/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com