如何在 3 个表的外连接上获取唯一记录和排序？

Question

我在获取记录的某些顺序和唯一性时遇到问题。我有以下表格设置：

person
groups
group_has_person

这里的想法是将人员分组。 （想想 Google+ 圈子）

首先，我只有一个人员表，其中有 2 个 ID 字段链接到组记录，但后来我总是被限制为只能在 2 个组中包含一个人。现在我添加了一个链接表，以便可以将人员放入无限数量的组中。每个组都有一个排序索引字段 (group.order_index)。这是一个定义组排序顺序的整数。这个想法是显示按组分组的所有人员，并且每个人仅显示一次，但也不按名称或 ID 排序，而是按组的额外索引字段排序。所以实际上我想向所有人展示，即使他们不在一个组中。但是，当它们位于一个或多个组中时，我想按 group.order_index 的顺序显示它们，并且仅显示一次。

查询结果应如下所示：

person.name   person.person_id   group.group_id   group.order_index 
Rick          1                  1                1
Tom           2                  1                1
Jan           4                  3                2
Kees          3                  3                2
Piet          5                  NULL             NULL

请注意，group_id 不是排序依据的列，但该表有一个额外的字段，因此可以在创建组后更改顺序。

我得到了一些接近我预期结果的查询，但我还没有到达：

以下查询给出了预期的排序结果，但当它位于多个组中时，它仍然多次给出一个人：

SELECT
  person.person_id AS 'person.person_id',
  person.name AS 'person.name',
  group.order_index AS 'group.order_index',
FROM
  `person`
LEFT OUTER JOIN `group_has_person` ON person.person_id = group_has_person.person_id
LEFT OUTER JOIN `group` ON group_has_person.group_id = group.group_id
ORDER BY
  3 ASC , 
  2 ASC

关键字 DISTINCT 也没有帮助。

以下查询给出了唯一的人员，但 order by group_order_index 不起作用，甚至显示错误的数字：

SELECT
  person.person_id AS 'person.person_id',
  person.name AS 'person.name',
  group.order_index AS 'group.order_index',
FROM
  `person`
LEFT OUTER JOIN `group_has_person` ON person.person_id = group_has_person.person_id
LEFT OUTER JOIN `group` ON group_has_person.group_id = group.workgroup_id
GROUP BY
  group_has_person.person_id
ORDER BY
  3 ASC , 
  2 ASC

此外，按 person.person_id 分组也无助于获得正确的顺序。

内部联接也不起作用，因为它们不会显示不在组中的人员。是否可以在没有代码的情况下仅通过查询而无需子选择来获得我想要的东西？

Answer 1

您已经快完成了，但是您需要 order_index 上的聚合函数：

SELECT
person.person_id AS 'person.person_id',
person.name AS 'person.name',
MIN(group.order_index) AS 'group.order_index',
FROM person
LEFT OUTER JOIN group_has_person ON person.person_id = group_has_person.person_id
LEFT OUTER JOIN group ON group_has_person.group_id = group.workgroup_id
GROUP BY
person.person_id, person.name
ORDER BY
3 ASC, 2 ASC

换句话说，如果一个人属于多个组，则无法避免重复，除非您选择哪个 order_index > 使用最小值、最大值、平均值或总和进行排序。

Answer 2

如果一个人位于多个组中，则会有不同的与之关联的group.order_index。您想按照哪一项对人进行排序？你可以只选择其中之一，即最小的或最大的，但这有什么意义呢？此外，当组在列表中不可见时，看到按组排序的人员列表不会令人不安吗？对于不知道列表如何排序的人来说，很难弄清楚应用了哪种排序逻辑。