如果status不为空，则选择不同的记录

Question

我有一个休假申请表，其中有重复的记录。

请假申请表:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
1           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
2           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
3           5        2021-08-07     2021-08-09   Sick Leave   1       9
4           10       2021-09-02     2021-09-05   Medical      NULL    NULL
5           10       2021-09-02     2021-09-05   Medical      NULL    NULL

使用这个查询获得不同的记录:

SELECT
MIN(leave_app_id) leave_app_id,
a.user_id,
a.from_date,
a.to_date,
a.reason,
a.status,
(SELECT CONCAT_WS(' ', first_name, last_name) FROM tbl_users WHERE user_id = a.approved_by) approved_by
FROM
tbl_leave_applications a
INNER JOIN tbl_users u ON u.user_id = a.user_id
GROUP BY
a.user_id,
a.from_date,
a.to_date,
a.reason,
a.status,
a.approved_by

查询结果:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
1           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
3           5        2021-08-07     2021-08-09   Sick Leave   1       John Doe
4           10       2021-09-02     2021-09-05   Medical      NULL    NULL

然而，我想，如果状态不是null，那么它应该只显示记录。

:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
3           5        2021-08-07     2021-08-09   Sick Leave   1       John Doe
4           10       2021-09-02     2021-09-05   Medical      NULL    NULL

Answer 1

您可以在这里尝试聚合:

SELECT
COALESCE(MAX(CASE WHEN a.status IS NOT NULL THEN a.leave_app_id END), MIN(a.leave_app_id)) AS leave_app_id,
a.user_id,
a.from_date,
a.to_date,
a.reason,
MAX(a.status) AS status,
MAX(CONCAT(u.first_name, ' ', u.last_name)) AS approved_by
FROL tbl_leave_applications a
INNER JOIN tbl_users u ON u.user_id = a.user_id
GROUP BY
a.user_id,
a.from_date,
a.to_date,
a.reason;