mysql - 根据记录的条目查找用户列表

Question

对我来说，这个场景很难解释，但我想获得一份准备好参加解雇 session 的用户(实际上是“患者”)列表。

情况

我有一张病床:

PATIENT
id   (int)
name (varchar)

患者遵循一个特殊的过程，我在其中定义了阶段:

PHASE
id   (int)
name (varchar)

然后我记录了患者的所有阶段:

STATUS
id         (int)
phase_id   (int)
patient_id (int)
timestamp  (datetime)

术语“准备开会”是指 session 已计划好。此类计划的 session 记录在消息表中。计划 session 的消息具有“meeting_planned”类型，例如，当 session 结束时，有一条新消息“meeting_done”。

MESSAGE
id         (int)
patient_id (int)
type       (enum)
value      (varchar)
timestamp  (datetime)

我的查询

我现在正在寻找一个我真的想不出来的查询。我的逻辑是:

1. 选择所有患者；
2. 最后状态(基于时间戳)来自阶段名称“C”或“D”；
3. 没有类型 meeting_planned 的消息。

广告 2:患者可以处于两个阶段之一。当患者处于 A、B、E 或 F 阶段时，则无法计划会面。当患者处于 C 阶段时，护士可能已经决定计划会面，但如果没有发生，它将在 D 阶段发生(D 总是在 C 之后，即 A>B>C>D>E>F)。

广告 3:可能还有其他消息，但绝不会是这种类型。这里有一个关于 type+patient_id 的唯一约束。如果 session 已计划并已举行，则有两条消息(类型 1 = meeting_planned，类型 2 = meeting_done)。如果没有计划的 session ，则不会出现 meeting_planned 类型，但可能已经设置了其他消息。

我尝试了什么

我现在想到了这个查询:

SELECT p.id, p.name, ph.name

FROM patient p

LEFT JOIN status s  ON p.id=s.patient_id
LEFT JOIN phase ph  ON ph.id=s.phase_id
LEFT JOIN message m ON p.id=m.patient_id

WHERE (ph.name='C' OR ph.name='D')
AND (ph.name != 'E')
AND (m.type != 'meeting_planned' OR m.type IS NULL)

我的问题

在上面的查询中，我有两件事无法处理:

1. 我有两个病人在名单上。 D 阶段的任何患者也作为 C 阶段的患者出现(所以双重)。我尝试了 SELECT DISTINCT 但没有成功
2. 我现在根据名称排除阶段，但我想根据状态的时间戳对阶段进行排序，并且只从那里查看最后的状态

广告 1:现在看起来像这样:

ID  NAME  PHASE
1   John  C
2   Jane  C
3   Tom   C
4   Pete  C
5   Anna  C
1   John  D
2   Jane  D
3   Tom   D

显然我想要这个:

ID  NAME  PHASE
4   Pete  C
5   Anna  C
1   John  D
2   Jane  D
3   Tom   D

我现在不能在这里使用 GROUP BY，因为我必须使用 COUNT 或类似的分组函数。

这里有没有 SQL 专家可以帮助我？

Answer 1

SELECT p.id, p.name, max(ph.name) as phase

FROM patient p

    LEFT JOIN status s  ON p.id=s.patient_id
    LEFT JOIN phase ph  ON ph.id=s.phase_id
    LEFT JOIN message m ON p.id=m.patient_id

WHERE (ph.name='C' OR ph.name='D')
    AND (m.type != 'meeting_planned' OR m.type IS NULL)
    AND p.id not in (
        select p.id
        from 
            patient p
            inner join
            status s on p.id = s.patient_id
            inner join
            phase ph on ph.id = s.phase_id
        where ph.name > 'D'
        ) s
group by p.id, p.name