本文介绍了LEFT JOIN 订单和限价的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
这是我的查询:
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`)
因为，如果 a 有很多行，它会像我的情况一样返回:
Since, in case a has MANY rows and it'll return like in my case:
NAME | LEVEL
Test1 | 1
Test2 | 120
Test3 | 2
Test4 | 1
我希望它选择 a.name 和 order 级别 desc 并限制为 1，所以它会返回更高的名称level where (a.account_id = fr.author).
I want it to select a.name with order of level desc and limit 1, so it'll return the name of higher level where (a.account_id = fr.author).
希望你能抓住我.如果没有，请随时发表评论.
Hope you got me. If not, feel free to post a comment.
推荐答案
尝试替换:
LEFT JOIN ps a ON a.account_id = fr.author
与:
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
)
这篇关于LEFT JOIN 订单和限价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！