mysql外部结合,mysql-将外部联接应用于复杂的语句

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:

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.

SUBSTRING_INDEX to get the first value separated by comma (,) in the field.