mysql - 合并 MySQL SELECT 语句
问题描述
我有下表tbl_crps,请注意列crp出现多次,但id和starts_on在具有相同crp的记录中总是不同的。
id | crp | starts_on |
------------------
1 | 20 | 2020-02-09 |
2 | 31 | 2019-06-05 |
3 | 20 | 2018-01-10 |
4 | 31 | 2021-07-03 |
5 | 58 | 2022-01-01 |
6 | 58 | 2025-02-02 |
我需要提取具有以下条件的记录:
如果对于crp存在一个或多个具有过去starts_on的记录,则必须返回具有该crp的那些记录中具有最大 id 的记录(具有过去日期的记录)
如果对于crp不存在具有过去日期的记录,则在具有该crp的所有记录中具有最大 id 的记录
我可以成功选择与以下第一个条件匹配的记录。
SELECT * FROM tbl_crps
AND id IN (SELECT MAX(b.id) FROM tbl_crps b WHERE b.starts_on <= "2020-10-02")
将返回 ID 为 3 和 2 的记录。
我需要实现的是将那些starts_on不在过去且crp不在第一个结果集中的记录添加到结果中。
可能吗?也许这样?
SELECT * FROM tbl_crps a
WHERE id IN (
SELECT MAX(a.id) FROM tbl_crps a WHERE a.starts_on <= "2020-10-02" GROUP BY a.crp
)
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" GROUP BY e.crp)
)
)
)
我正在使用 MySQL 5.7
解决方案
我不确定我是否完全理解您的要求,但我认为这个查询可以满足您的需求,或者非常接近它:
WITH cteData AS (SELECT b.CRP, MAX(b.ID) AS ID
FROM tbl_crps b
WHERE b.starts_on <= "2020-10-02"
GROUP BY b.CRP)
SELECT *
FROM tbl_crps a
WHERE a.ID IN (SELECT ID
FROM (SELECT b.CRP, b.ID
FROM cteData b
UNION
SELECT d.CRP, d.ID
FROM tbl_crps d
WHERE d.starts_on > "2020-10-02" AND
d.CRP NOT IN (SELECT crp
FROM cteData)) c)
这返回
id crp starts_on
2 31 2019-06-05
3 20 2018-01-10
5 58 2022-01-01
6 58 2025-02-02
根据我对要求的理解,这似乎是正确的。简而言之
1 - NOT included because its date is in the past ('current' date = 2020-10-02)
and its ID is NOT the greatest among records in the past with that CRP.
2 - included because its date is in the past and its ID is the greatest
among records with that CRP which have dates in the past.
3 - date is in the past, ID is greatest for that CRP with date in the past
4 - NOT included because date is in the future, but CRP is included in recs
from first subquery.
5 - date is in the future, and CRP does not occur in first subquery
6 - date is in the future, and CRP does not occur in first subquery
推荐阅读
- ios - 从标签栏控制器推送时,为什么我的视图控制器是黑色的?
- firebase-realtime-database - Firebaseanimatedlist:如何为后续列表项(聊天)仅显示一次用户名/日期?
- monaco-editor - 无法注册条件片段
- python - Python 多处理:提取结果
- .net - 仅发布了异常 CrystalDecisions.CrystalReports.Engine.ReportDocument
- typescript - 打字稿:用其余参数组装一个联合?
- java - 读取和更新 xml 文档中某些元素的最佳方法
- azure - 给所有订阅者的消息
- charts - Flutter 包失败
- r - 使用不同的图表将来自 2 个数据帧的数据合并到一个图中