mysql-将外部联接应用于复杂的语句 [英] mysql - Applying an outer join to a complex statement

问题描述

我整理了一个查询以提取人们的姓名，并优先考虑使用查看页面的人的语言/书写文字写的名字的版本.

我早些时候在丢失记录方面遇到了麻烦，并在此处上发布了一个问题.我收到了一个答案，该提示建议使用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)，即每人得分最高的记录.

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

该小提琴显示了一个示例，该示例提取了所有记录及其得分: http://sqlfiddle.com/#!9/54ce8/13 -我想要做的就是为每个person_id绘制一条得分最高的记录.在此示例中，我只想将Jorge和Alejandro用作表，因为它们是两者中得分最高的名称.

解决方案

我只能想到一种实现此目的的方法.在当前工作查询中使用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;

我也确实在提琴中测试了查询 http://sqlfiddle.com/# ！9/54ce8/33

一些解释:

1. GROUP_CONCAT的名称&加上ORDER BY条件的得分-请注意，ORDER BY在两个GROUP_CONCAT中都必须相同.
2. SUBSTRING_INDEX以获得字段中第一个用逗号(，)分隔的值.

I put together a query to pull out peoples' names, giving preference to the version of that name that is written in the language/writing script of the person viewing the page.

I was having some troubles with missing records earlier, and posted a question about that here. I received an answer that suggested using a CASE statement and applying a score to rank the names based on the preferred language/script. I've gotten as far as the ranking, which has allowed the correct number of records to be returned (no missing records now). See below:

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

The trouble I'm now having is that, because I just need one result per person (hence the GROUP BY), it's simply pulling out the first record it encounters per person. I need to be able to put the "score" to work and only pull out the record with the highest available score (which could be 3, 2, 1 or 0) -- the one record per person with the highest score.

The suggestion in the original post was to use an outer query, but I'm not sure how to apply that here. Any ideas?

EDIT: This fiddle shows an example that pulls out all records with their scores: http://sqlfiddle.com/#!9/54ce8/13 -- what I want to be able to do is to just draw out the one record per person_id that has the highest score. In this example, I'd like a table with Jorge and Alejandro only as they are the highest scored names for each of the two.

解决方案

I can only think of one way to achieve this. With a combination of GROUP_CONCAT and SUBSTRING_INDEX on your current working query. Example query below:

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;

I did test the query in the fiddle as well http://sqlfiddle.com/#!9/54ce8/33

A bit of explanation:

1. GROUP_CONCAT over the name & score with addition of ORDER BY condition - note that the ORDER BY must be identical in both of the GROUP_CONCAT.
2. SUBSTRING_INDEX to get the first value separated by comma (,) in the field.

这篇关于mysql-将外部联接应用于复杂的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！