按组进行分组和自定义排序

Question

我已经阅读了 MySQL order by before group by的答案，但将其应用于我的查询会导致一个相当简单的情况下出现子查询中的子查询，因此我想知道是否可以简化此过程：

包含示例数据的模式

为了简洁起见，我省略了members表上的其他字段。此外，在实际应用程序中连接了更多的表，但这些表很容易连接。问题出在membership_stack表上。

CREATE TABLE members (
  id int unsigned auto_increment,
  first_name varchar(255) not null,
  PRIMARY KEY(id)
);

INSERT INTO members (id, first_name)
     VALUES (1, 'Tyler'),
            (2, 'Marissa'),
            (3, 'Alex'),
            (4, 'Parker');

CREATE TABLE membership_stack (
  id int unsigned auto_increment,
  member_id int unsigned not null,
  sequence int unsigned not null,
  team varchar(255) not null,
  `status` varchar(255) not null,
  PRIMARY KEY(id),
  FOREIGN KEY(member_id) REFERENCES members(id)
);

-- Algorithm to determine correct team:
-- 1. Only consider rows with the highest sequence number
-- 2. Order statuses and pick the first one found:
--    (active, completed, cancelled, abandoned)

INSERT INTO membership_stack (member_id, sequence, team, status)
     VALUES (1, 1, 'instinct', 'active'),
            (1, 1, 'valor', 'abandoned'),
            (2, 1, 'valor', 'active'),
            (2, 2, 'mystic', 'abandoned'),
            (2, 2, 'valor', 'completed'),
            (3, 1, 'instinct', 'completed'),
            (3, 2, 'valor', 'active');

我无法更改数据库架构，因为数据与外部数据源同步。

查询

目前我已经有了以下内容：

    SELECT m.id, m.first_name, ms.sequence, ms.team, ms.status
      FROM membership_stack AS ms
      JOIN (
    SELECT member_id, MAX(sequence) AS sequence
      FROM membership_stack
  GROUP BY member_id
           ) AS t1
        ON ms.member_id = t1.member_id
       AND ms.sequence = t1.sequence
RIGHT JOIN members AS m
        ON ms.member_id = m.id
  ORDER BY m.id, FIELD(ms.status, 'active', 'completed', 'cancelled', 'abandoned');

这个功能能够正常工作，但如果一个成员的“最近序列”涉及多个团队，则可能会出现多次。我需要再次按 id 聚合，并选择每个组中的第一行。

但是这带来了一些问题：

1. MySQL 中没有 FIRST() 函数
2. 整个结果集将成为子表（子查询），这在此处并不重要，但应用程序中的查询非常大。
3. 它需要与 ONLY_FULL_GROUP_BY mode 兼容，因为 MySQL 5.7 默认启用它。我还没有检查，但我怀疑 FIELD(ms.status, 'active', 'completed', 'cancelled', 'abandoned') 在此结果集上是否被认为是功能上相关的字段。该查询还需要与 MySQL 5.1 兼容，因为这是我们目前正在运行的版本。

目标

| id | first_name | sequence |     team |    status |
|----|------------|----------|----------|-----------|
|  1 |      Tyler |        1 | instinct |    active |
|  2 |    Marissa |        2 |    valor | completed |
|  3 |       Alex |        2 |    valor |    active |
|  4 |     Parker |     NULL |     NULL |      NULL |

我该怎么处理这个问题？

编辑：注意到一些成员不属于任何团队。这些成员应该在结果集中包含，并将这些字段的值设置为 null。问题已更新以反映新信息。

- rink.attendant.6

---

如果序列和状态都相同怎么办？例如，Tyler@valor的状态为“active”？ - Paul Spiegel

@PaulSpiegel 我也有这个问题，但数据提供者还没有回复是否可能。在同一序列中永远不应该有多个“active”，但我想如果他们在同一序列中连续完成，可能会有多个“completed”。 - rink.attendant.6

然而，您应该定义一个“独特”的顺序。在我的解决方案中，我使用membership_stack.id ASC作为ORDER BY子句中的最后一列（以防万一）。 - Paul Spiegel

Answer 1

我会使用变量来完成这个任务。

你正在寻找一个特定排序中最大的 membership_stack 行。我只关注这一点。回到 members 的 join 是微不足道的。

select ms.*
from (select ms.*,
             (@rn := if(@m = member_id, @rn + 1,
                        if(@m := member_id, 1, 1)
                       )
             ) as rn
      from membership_stack ms cross join
           (select @m := -1, @rn := 0) params
      order by member_id, sequence desc,
               field(ms.status, 'active', 'completed', 'cancelled', 'abandoned')
     ) ms
where rn = 1;

变量是逻辑实现的方式。顺序对于获得正确结果至关重要。

编辑：

MySQL在子查询中使用LIMIT非常挑剔。这可能有效：

select ms.*
from membership_stack ms
where (sequence, status) = (select ms2.sequence, ms2.status
                            from membership_stack ms2
                            where ms2.member_id = ms.member_id
                            order by ms2.member_id, ms2.sequence desc,
                                     field(ms2.status, 'active', 'completed', 'cancelled', 'abandoned')
                            limit 1
                           );

- Gordon Linoff

---

子查询中带有LIMIT的WHERE会产生错误“where clause中未知列'field'”。变量one似乎可以正常工作，您能否解释得更详细一些，以便我能够理解并向他人提出解决方案？ - rink.attendant.6

@rink.attendant.6 . . . 你似乎对窗口函数很熟悉。这在MySQL中相当于 row_number() over (partition by member_id order by field(. . .)。变量逐行跟踪状态。(第二个查询中的错误是列名打错了。) - Gordon Linoff

我从未听说过窗口函数。我只看到过row_number()，是从有经验的另一个RDBMS的人那里尝试解决这个确切问题。无论如何，我将与我的团队讨论这个SO问题，并希望在下周接受解决方案。 - rink.attendant.6

Answer 2

您可以在WHERE子句中使用带有LIMIT 1的相关子查询：

SELECT m.id, m.first_name, ms.sequence, ms.team, ms.status
FROM members AS m
JOIN membership_stack AS ms ON ms.member_id = m.id
WHERE ms.id = (
    SELECT ms1.id
    FROM membership_stack AS ms1
    WHERE ms1.member_id = ms.member_id
    ORDER BY ms1.sequence desc, 
             FIELD(ms1.status, 'active', 'completed', 'cancelled', 'abandoned'),
             ms1.id asc
    LIMIT 1
)
ORDER BY m.id;

演示：http://rextester.com/HGU18448

更新

为了包括在membership_stack表中没有条目的成员，您应该使用LEFT JOIN，并将子查询条件从WHERE子句移动到ON子句中：

SELECT m.id, m.first_name, ms.sequence, ms.team, ms.status
FROM members AS m
LEFT JOIN membership_stack AS ms 
    ON  ms.member_id = m.id
    AND ms.id = (
        SELECT ms1.id
        FROM membership_stack AS ms1
        WHERE ms1.member_id = ms.member_id
        ORDER BY ms1.sequence desc, 
                 FIELD(ms1.status, 'active', 'completed', 'cancelled', 'abandoned'),
                 ms1.id asc
        LIMIT 1
    )
ORDER BY m.id;

演示：http://rextester.com/NPI79503

- Paul Spiegel

---

我已经编辑了我的问题，包括成员可能没有任何条目的情况。有没有办法为这些情况调整此查询？或者在使用LEFT JOIN时，在ON子句中放置WHERE条件是否存在任何问题？ - rink.attendant.6

请使用 LEFT JOIN。检查更新。 - Paul Spiegel