sql - 多对多关系排序

Question

我有 3 个表 person、person_speaks_language 和 language。

• 人有 80 条记录
• 语言有2条记录

我有以下记录

• 前 10 个人会说一种语言
• 前 70 人(包括第一组)会说 2 种语言
• 最后 10 个人不会说任何语言

在示例之后，我想按语言对人员进行排序，我该如何正确地做到这一点。

我正在尝试使用以下 SQL 但看起来很奇怪

SELECT "person".*
FROM "person"
  LEFT JOIN "person_speaks_language" ON "person"."id" = "person_speaks_language"."person_id"
  LEFT JOIN "language" ON "person_speaks_language"."language_id" = "language"."id"
ORDER BY "language"."name"
  ASC

数据集

71,Catherine,Porter,male,NULL
72,Isabelle,Sharp,male,NULL
73,Scott,Chandler,male,NULL
74,Jean,Graham,male,NULL
75,Marc,Kennedy,male,NULL
76,Marion,Weaver,male,NULL
77,Melvin,Fitzgerald,male,NULL
78,Catherine,Guerrero,male,NULL
79,Linnie,Strickland,male,NULL
80,Ann,Henderson,male,NULL
11,Daniel,Boyd,female,English
12,Ora,Beck,female,English
13,Hulda,Lloyd,female,English
14,Jessie,McBride,female,English
15,Marguerite,Andrews,female,English
16,Maurice,Hamilton,female,English
17,Cecilia,Rhodes,female,English
18,Owen,Powers,female,English
19,Ivan,Butler,female,English
20,Rose,Bishop,female,English
21,Franklin,Mann,female,English
22,Martha,Hogan,female,English
23,Francis,Oliver,female,English
24,Catherine,Carlson,female,English
25,Rose,Sanchez,female,English
26,Danny,Bryant,female,English
27,Jim,Christensen,female,English
28,Eric,Banks,female,English
29,Tony,Dennis,female,English
30,Roy,Hoffman,female,English
31,Edgar,Hunter,female,English
32,Matilda,Gordon,female,English
33,Randall,Cruz,female,English
34,Allen,Brewer,female,English
35,Iva,Pittman,female,English
36,Garrett,Holland,female,English
37,Johnny,Russell,female,English
38,Nina,Richards,female,English
39,Mary,Ballard,female,English
40,Adrian,Sparks,female,English
41,Evelyn,Santos,female,English
42,Bess,Jackson,female,English
43,Nicholas,Love,female,English
44,Fred,Perkins,female,English
45,Cynthia,Dunn,female,English
46,Alan,Lamb,female,English
47,Ricardo,Sims,female,English
48,Rosie,Rogers,female,English
49,Susan,Sutton,female,English
50,Mary,Boone,female,English
51,Francis,Marshall,male,English
52,Carl,Olson,male,English
53,Mario,Becker,male,English
54,May,Hunt,male,English
55,Sophie,Neal,male,English
56,Frederick,Houston,male,English
57,Edwin,Allison,male,English
58,Florence,Wheeler,male,English
59,Julia,Rogers,male,English
60,Janie,Morgan,male,English
61,Louis,Hubbard,male,English
62,Lida,Wolfe,male,English
63,Alfred,Summers,male,English
64,Lina,Shaw,male,English
65,Landon,Carroll,male,English
66,Lilly,Harper,male,English
67,Lela,Gordon,male,English
68,Nina,Perry,male,English
69,Dean,Perez,male,English
70,Bertie,Hill,male,English
1,Nelle,Gill,female,Spanish
2,Lula,Wright,female,Spanish
3,Anthony,Jensen,female,Spanish
4,Rodney,Alvarez,female,Spanish
5,Scott,Holmes,female,Spanish
6,Daisy,Aguilar,female,Spanish
7,Elijah,Olson,female,Spanish
8,Alma,Henderson,female,Spanish
9,Willie,Barrett,female,Spanish
10,Ada,Huff,female,Spanish
11,Daniel,Boyd,female,Spanish
12,Ora,Beck,female,Spanish
13,Hulda,Lloyd,female,Spanish
14,Jessie,McBride,female,Spanish
15,Marguerite,Andrews,female,Spanish
16,Maurice,Hamilton,female,Spanish
17,Cecilia,Rhodes,female,Spanish
18,Owen,Powers,female,Spanish
19,Ivan,Butler,female,Spanish
20,Rose,Bishop,female,Spanish
21,Franklin,Mann,female,Spanish
22,Martha,Hogan,female,Spanish
23,Francis,Oliver,female,Spanish
24,Catherine,Carlson,female,Spanish
25,Rose,Sanchez,female,Spanish
26,Danny,Bryant,female,Spanish
27,Jim,Christensen,female,Spanish
28,Eric,Banks,female,Spanish
29,Tony,Dennis,female,Spanish
30,Roy,Hoffman,female,Spanish
31,Edgar,Hunter,female,Spanish
32,Matilda,Gordon,female,Spanish
33,Randall,Cruz,female,Spanish
34,Allen,Brewer,female,Spanish
35,Iva,Pittman,female,Spanish
36,Garrett,Holland,female,Spanish
37,Johnny,Russell,female,Spanish
38,Nina,Richards,female,Spanish
39,Mary,Ballard,female,Spanish
40,Adrian,Sparks,female,Spanish
41,Evelyn,Santos,female,Spanish
42,Bess,Jackson,female,Spanish
43,Nicholas,Love,female,Spanish
44,Fred,Perkins,female,Spanish
45,Cynthia,Dunn,female,Spanish
46,Alan,Lamb,female,Spanish
47,Ricardo,Sims,female,Spanish
48,Rosie,Rogers,female,Spanish
49,Susan,Sutton,female,Spanish
50,Mary,Boone,female,Spanish
51,Francis,Marshall,male,Spanish
52,Carl,Olson,male,Spanish
53,Mario,Becker,male,Spanish
54,May,Hunt,male,Spanish
55,Sophie,Neal,male,Spanish
56,Frederick,Houston,male,Spanish
57,Edwin,Allison,male,Spanish
58,Florence,Wheeler,male,Spanish
59,Julia,Rogers,male,Spanish
60,Janie,Morgan,male,Spanish
61,Louis,Hubbard,male,Spanish
62,Lida,Wolfe,male,Spanish
63,Alfred,Summers,male,Spanish
64,Lina,Shaw,male,Spanish
65,Landon,Carroll,male,Spanish
66,Lilly,Harper,male,Spanish
67,Lela,Gordon,male,Spanish
68,Nina,Perry,male,Spanish
69,Dean,Perez,male,Spanish
70,Bertie,Hill,male,Spanish

更新

期望的结果是:每个人必须使用语言顺序只出现一次

为了进一步解释这个案例，我将采用一个新的小型数据集，仅使用人员 ID 和语言名称

1,English
2,English
3,English
4,English
19,English
1,Spanish
2,Spanish
3,Spanish
4,Spanish
5,Spanish
14,Spanish
15,Spanish
16,Spanish
19,Spanish
21,Spanish
25,Spanish

我使用相同的顺序，但如果我使用限制，例如 LIMIT 8，结果将是

1,English
2,English
3,English
4,English
19,English
1,Spanish
2,Spanish
3,Spanish

预期的结果是

1,English
2,English
3,English
4,English
19,English
5,Spanish
14,Spanish
15,Spanish

我想做什么

我想做的是对可能与 Y 具有多对多关系的 X 列表进行排序、分页和过滤，在这种情况下，X 是一个人，Y 是一种语言。我需要以一般方式进行。如果我想按一些 Y 属性对列表进行排序，我发现了一个麻烦。

列表将以这种方式显示:

firstname, lastname, gender  , languages
Daniel   , Boyd    , female  , English Spanish
Ora      , Beck    , female  , English
Anthony  , Jensen  , female  , Spanish
....

我只需要按正确的顺序返回一个包含 ID 的数组

这是我需要结果只出现一次的主要原因，因为 ORM(我正在使用)尝试对每个结果进行水合，如果我使用偏移量和限制对结果进行分页。结果可能不是预期的。我正在做多对多关系的假设

我不能使用 string_agg 或 group_concat 因为我不知道真实的数据，我不知道是整数还是字符串

Answer 1

如果你希望每个人只出现一次，那么你需要按那个人聚合。如果您随后想要语言列表，则需要以某种方式组合它们，想到串联。

双引号的使用向我推荐了 Postgres 或 Oracle。这是 Postgres 语法:

SELECT p.id, string_agg(l.name) as languages
FROM person p LEFT JOIN 
     person_speaks_language psl
     ON p.id = psl.person_id LEFT JOIN
     language l
     ON psl.language_id = l.id
GROUP BY p.id
ORDER BY COUNT(l.name) DESC, languages;

大多数数据库中都存在与 string_agg() 类似的功能。