MySQL LEFT JOIN 订单和限价
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7788212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT JOIN order and limit
提问by Cyclone
This is my query:
这是我的查询：
SELECT `p`.`name` AS 'postauthor', `a`.`name` AS 'authorname',
`fr`.`pid`, `fp`.`post_topic` AS 'threadname', `fr`.`reason`
FROM `z_forum_reports` `fr`
LEFT JOIN `forums` `f` ON (`f`.`id` = `fr`.`pid`)
LEFT JOIN `forums` `fp` ON (`f`.`first_post` = `fp`.`id`)
LEFT JOIN `ps` `p` ON (`p`.`id` = `f`.`author_guid`)
LEFT JOIN `ps` `a` ON (`a`.`account_id` = `fr`.`author`)
My problem is this left join:
我的问题是这个左连接：
SELECT `a`.`name`, `a`.`level`
[..]
LEFT JOIN `ps` `a` ON (`a`.`account_id` = `fr`.`author`)
Since, in case ahas MANY rows and it'll return like in my case:
因为，如果a有很多行，它会像我的情况一样返回：
NAME | LEVEL
Test1 | 1
Test2 | 120
Test3 | 2
Test4 | 1
I want it to select a.namewith orderof level descand limit 1, so it'll return the name of higher levelwhere (a.account_id = fr.author).
我希望它选择a.namewith orderof leveldesc和 limit 1，所以它会返回更高的名称levelwhere (a.account_id = fr.author)。
Hope you got me. If not, feel free to post a comment.
希望你得到我。如果没有，请随时发表评论。
回答by ypercube??
Try replacing:
尝试更换：
LEFT JOIN ps a ON a.account_id = fr.author
with:
和：
LEFT JOIN ps a
ON a.PrimaryKey --- the Primary Key of ps
= ( SELECT b.PrimaryKey
FROM ps AS b
WHERE b.account_id = fr.author
ORDER BY b.level DESC
LIMIT 1
)
回答by Jonathan Leffler
Replace the LEFT JOIN clause with something like:
将 LEFT JOIN 子句替换为以下内容：
...
LEFT JOIN (SELECT b.account_id, b.name
FROM (SELECT c.account_id, MAX(c.level) AS level
FROM ps AS c
GROUP BY c.account_id) AS d
JOIN ps AS b ON b.account_id = d.account_id AND b.level = d.level
) AS a
ON (a.account_id = fr.author)
...
This will still return multiple rows if there were several rows in pswith the same account ID and the same level and that level was the maximum level:
如果有几行ps具有相同的帐户 ID 和相同的级别，并且该级别是最大级别，这仍然会返回多行：
NAME | LEVEL
Test1 | 1
Test2 | 120
Test3 | 2
Test4 | 1
Test5 | 120
If this situation can arise, then you have to decide what you want to do - and tune the query appropriately. For example, you might decide to use MAX(b.name)with a GROUP BY clause to arbitrarily select the alphabetically later of the two names.
如果可能出现这种情况，那么您必须决定要做什么 - 并适当地调整查询。例如，您可能决定MAX(b.name)与 GROUP BY 子句一起使用，以任意选择两个名称中字母顺序较晚的那个。
