LEFT JOIN 顺序和限制

Question

这是我的查询：

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`)

我的问题是这个左连接：

SELECT `a`.`name`, `a`.`level`
[..]
LEFT JOIN `ps` `a` ON (`a`.`account_id` = `fr`.`author`)

因为，如果 a 有很多行，它会像我的情况一样返回：

NAME  | LEVEL
Test1 | 1
Test2 | 120
Test3 | 2
Test4 | 1 

我希望它选择 a.name 的 order 级别为 desc 且限制为 1，因此它将返回更高 level 的名称，其中 (a.account_id = fr.author）。

希望你能抓住我。如果没有，请随时发表评论。

Answer 1

尝试将： 替换

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
       )

Answer 2

将 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)
...

如果 ps 中有多个行具有相同的帐户 ID 和相同的级别，并且该级别是最大级别，则仍会返回多行：

NAME  | LEVEL
Test1 | 1
Test2 | 120
Test3 | 2
Test4 | 1
Test5 | 120

如果这种情况可以出现，那么您必须决定要做什么 - 并适当地调整查询。例如，您可能决定将 MAX(b.name) 与 GROUP BY 子句一起使用，以任意选择两个名称中按字母顺序排列靠后的名称。