MySQL 合并 2 个结果集，不包括第一个中的记录 [英] MySQL merge 2 result sets excluding records in the first

问题描述

我有下表tbl_crps，注意列crp出现不止一次，但是id和starts_on 在具有相同 crp 的记录中总是不同的.

I have the following table tbl_crps, note that the column crp occurs more than one time, but id and starts_on will always differ in records with the same crp.

id |  crp | starts_on  |
------------------
1  |  20  | 2020-02-09 |
2  |  31  | 2019-06-30 |
3  |  20  | 2018-01-10 |
4  |  31  | 2021-07-28 |
5  |  58  | 2022-01-15 |
6  |  58  | 2025-02-19 |

我需要提取具有以下条件的记录:

I need to exctract the records with the following conditions:

1. 如果对于 crp 存在一个或多个具有过去 starts_on 的记录，那么在那些具有该 crp 的记录中具有最大 id 的记录em>(过去日期的)必须返回

1. If for crp one or more records with past starts_on exist, then the one with the greatest id among those records with that crp (the ones with past date) must be returned

如果对于 crp 没有过去日期的记录，那么在所有具有该 crp

If for crp no records exist with past date, then the one with the greatest id among all the records with that crp

我可以成功选择符合第一个条件和以下条件的记录.

I can successfully select records matching the first condition with the following.

SELECT * FROM tbl_crpsAND id IN (SELECT MAX(b.id) FROM tbl_crps b WHERE b.starts_on <= "2020-10-02")

将返回 ID 为 3 和 2 的记录.

Will return records with ids 3 and 2.

我需要实现的是将那些 starts_on 不在过去和 crp 不在第一个结果集中的记录添加到结果中.

What I need to achieve is adding to the result those records which starts_on is not in the past and which crp is not in the first result set.

有可能吗?这是一种干净的方式吗?

Is it possible? Would this be a clean way?

SELECT * FROM tbl_crps a
WHERE id IN (
     SELECT MAX(a.id) FROM tbl_crps a WHERE a.starts_on <= "2020-10-02"
 )
UNION
(
    SELECT * FROM tbl_crps b
        WHERE id IN (
             SELECT MAX(c.id) FROM tbl_crps c WHERE c.starts_on > "2020-10-02"
             AND c.crp NOT IN (
                  SELECT d.crp FROM tbl_crps d
                  WHERE id IN (
                       SELECT MAX(e.id) FROM tbl_crps e WHERE e.starts_on <= "2020-10-02")
                   )
        )
)

我使用的是 MySQL 5.7

I'm using MySQL 5.7

推荐答案

测试

SELECT id, crp, starts_on
FROM tbl_crps
NATURAL JOIN (SELECT crp, MAX(id) id
              FROM tbl_crps
              WHERE starts_on < CURRENT_DATE
              GROUP BY crp) x
UNION ALL
SELECT id, crp, starts_on
FROM tbl_crps
NATURAL JOIN (SELECT crp, MAX(id) id
              FROM tbl_crps
              GROUP BY crp
              HAVING MIN(starts_on) >= CURRENT_DATE) x;

小提琴

这篇关于MySQL 合并 2 个结果集，不包括第一个中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！