LEFT JOIN顺序和限制 [英] LEFT JOIN order and limit

问题描述

这是我的查询:

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 

我希望它选择级别为desc的order并限制为1的a.name，因此它将返回较高的level的名称，其中(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顺序和限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！