我有这样的
mysql评论表.
+------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | userid | int(11) | NO | | 0 | | | comment | char(255) | NO | | NULL | | | content | int(11) | NO | MUL | 0 | | | ratings | int(11) | NO | | 0 | | | datetime | datetime | NO | | NULL | | | ip | int(10) unsigned | NO | | NULL | | | is_updated | tinyint(2) | NO | | 0 | | | record_num | int(11) | NO | PRI | NULL | auto_increment | +------------+------------------+------+-----+---------+----------------+
现在我可以使用像这样的INNER JOIN查询从另一个表中获取来自此用户名和用户名的注释.
我可以获得前3条评论ORDER BY comments.ratings DESC
SELECT comments.userid,users.username,comments.comment,comments.ratings,comments.datetime,comments.record_num,content.uploader,content.anonymous FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num WHERE comments.content = ? ORDER BY comments.ratings DESC limit 3
和
获得定期评论ORDER BY comments.datetime DESC.
SELECT comments.userid,content.anonymous FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num WHERE comments.content = ? ORDER BY comments.datetime DESC limit ?,?
我正在尝试做的是首先通过评级向用户显示前3条评论,然后通过comments.datetime DESC定期评论.
现在我怎么能将上面两个mySQL查询加入到一个？
正如另一个答案所说 – 你可以使用union来组合结果 – 这只是意味着将两个结果连接在一起.但是,重要的是要注意,你不能简单地将这两个查询直接组合在一起,因为它们使用order by,所以我们需要将它们包装在外部查询中,并使用排名变量来确保我们可以重建联合按照我们想要的顺序：
select * from (
SELECT 1 as tbl,comments.userid,content.anonymous,@rank := @rank + 1 as rank
FROM comments
LEFT JOIN users ON comments.userid = users.record_num
LEFT JOIN content ON comments.content = content.record_num
CROSS JOIN (select @rank := 0 ) q
WHERE comments.content = ? ORDER BY comments.ratings DESC limit 3
) q1
UNION ALL
select * from (
SELECT 2 as tbl,@rank := @rank + 1 as rank
FROM comments
LEFT JOIN users ON comments.userid = users.record_num
LEFT JOIN content ON comments.content = content.record_num
CROSS JOIN (select @rank := 0 ) q
WHERE comments.content = ? ORDER BY comments.datetime DESC limit ?,?
) q2
ORDER BY tbl asc,rank asc;
默认情况下,union是不同的意思,它不会复制在两个结果集中找到的行,但是也不能保证按照您期望的顺序返回行,因此需要使用自己的tbl值标记每个表,然后按该字段排序.
如果您确定不存在重复项,则可以使用union all而不是union来消除重复检查
