mysql - 将外部连接应用于复杂语句

Question

我组合了一个查询来提取人们的名字，优先选择以查看页面的人的语言/书写脚本书写的名字版本。

我之前遇到了丢失记录的问题，并发布了一个关于这个的问题 here .我收到了 answer建议使用 CASE 语句并应用分数根据首选语言/脚本对名称进行排名。我已经得到了排名，它允许返回正确数量的记录(现在没有丢失的记录)。见下文:

SELECT 
    people.person_id,
    names.name,
CASE 
    WHEN people.person_default_name_id=names.name_id AND language_scripts.script_id = :user_script_id THEN 3
    WHEN names.language_id = :user_language_id THEN 2
    WHEN language_scripts.script_id = :user_script_id THEN 1
    ELSE 0
    END as score
FROM 
    `people` 
LEFT JOIN
    `names` ON names.person_id=people.person_id
LEFT JOIN
    `languages` ON names.language_id = languages.language_id
LEFT JOIN
    `language_scripts` ON languages.language_id = language_scripts.language_id
GROUP BY 
    people.person_id 
ORDER BY 
    names.name ASC

我现在遇到的麻烦是，因为我只需要每个人一个结果(因此 GROUP BY)，它只是拉出它遇到的每个人的第一条记录。我需要能够让“分数”发挥作用，并且只提取最高可用分数(可能是 3、2、1 或 0)的记录——每个人获得最高分数的记录。

原帖中的建议是使用外部查询，但我不确定如何在此处应用它。有什么想法吗？

编辑:这个 fiddle 展示了一个例子，它提取了所有记录及其分数:http://sqlfiddle.com/#!9/54ce8/13 -- 我想要做的是只为每个 person_id 抽出一个得分最高的记录。在此示例中，我只想要一个包含 Jorge 和 Alejandro 的表格，因为他们是这两个名字中得分最高的名字。

Answer 1

我只能想到一种方法来实现这一点。在您当前的工作查询中结合使用 GROUP_CONCAT 和 SUBSTRING_INDEX。下面的示例查询:

SELECT    person_id,
          SUBSTRING_INDEX(GROUP_CONCAT(a.name ORDER BY a.score DESC),',',1) sname, 
          SUBSTRING_INDEX(GROUP_CONCAT(a.score ORDER BY a.score DESC),',',1) max_score FROM 
(SELECT   people.person_id,
          names.name,
          CASE 
          WHEN people.person_default_name_id=names.name_id AND language_scripts.script_id = '1' THEN 3
          WHEN names.language_id = '1' THEN 2
          WHEN language_scripts.script_id = '1' THEN 1
          ELSE 0
          END AS score
FROM      `people` 
LEFT JOIN `names` ON names.person_id=people.person_id
LEFT JOIN `languages` ON names.language_id = languages.language_id
LEFT JOIN `language_scripts` ON languages.language_id = language_scripts.language_id) a 
GROUP BY person_id;

我也在 fiddle 中测试了查询 http://sqlfiddle.com/#!9/54ce8/33

一些解释:

1. GROUP_CONCAT 在名称和分数上添加 ORDER BY 条件 - 请注意 ORDER BY 在 GROUP_CONCAT。
2. SUBSTRING_INDEX 获取字段中以逗号 (,) 分隔的第一个值。