我已经阅读了 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
聚合,并选择每个组中的第一行。但是这带来了一些问题:
- MySQL 中没有
FIRST()
函数 - 整个结果集将成为子表(子查询),这在此处并不重要,但应用程序中的查询非常大。
- 它需要与 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。问题已更新以反映新信息。
membership_stack.id ASC
作为ORDER BY子句中的最后一列(以防万一)。 - Paul Spiegel