本文讲解如何通过调整 group by 子句和聚合函数，将来自多个表（historical、visualizations、other）的记录按 nid 合并，统一取最小 start_date 和最大 end_date，并正确处理 hid/type/url 等非聚合字段。
本文讲解如何通过调整 group by 子句和聚合函数，将来自多个表（historical、visualizations、other）的记录按 nid 合并，统一取最小 start_date 和最大 end_date，并正确处理 hid/type/url 等非聚合字段。
原始查询的问题在于：GROUP BY NID, TYPE, HID, URL 导致相同 NID 但不同 TYPE 或 HID 的记录被拆分为多行，无法实现跨类型的时间范围合并。而需求本质是「以 NID 为唯一分组键，全局聚合其所有来源的时间区间」——即每个 NID 最终只保留一行，其中 START_DATE_ = MIN(所有该 NID 的 START_DATE)，END_DATE_ = MAX(所有该 NID 的 END_DATE)。
✅ 正确写法：仅按 NID 分组 + 聚合非分组字段
以下是优化后的完整 SQL（兼容 Oracle 12c+）：
SELECT NID, MIN(START_DATE) AS START_DATE_, MAX(END_DATE) AS END_DATE_, -- 使用聚合函数处理非分组字段：任选一个 HID（如 MIN/HID）、拼接 TYPE、取首个非空 URL MIN(HID) AS HID, -- 若 HID 为数值型；若为字符串，可用 MAX(HID) 或 LISTAGG(DISTINCT HID, ',') WITHIN GROUP (ORDER BY HID) LISTAGG(DISTINCT TYPE, ', ') WITHIN GROUP (ORDER BY TYPE) AS TYPES, MAX(URL) AS URL -- 假设 URL 为 VARCHAR2，且最多一个非空值；否则建议用 FIRST_VALUE(URL IGNORE NULLS) OVER (PARTITION BY NID ORDER BY CASE WHEN URL IS NOT NULL THEN 0 ELSE 1 END) FROM ( SELECT NID, DATESTART AS START_DATE, DATEEND AS END_DATE, RID AS HID, 'HISTORICAL' AS TYPE, URL FROM HISTORICAL WHERE READER = :userId AND DISPLAY = 1 UNION ALL -- 推荐用 UNION ALL 替代 UNION（无重复去重开销，语义更清晰） SELECT NID, DATE AS START_DATE, DATE AS END_DATE, NULL AS HID, 'VISUALIZATION' AS TYPE, NULL AS URL FROM VISUALIZATIONS WHERE READER = :userId AND SHOW = 1 UNION ALL SELECT NID, DATE AS START_DATE, DATE AS END_DATE, NULL AS HID, 'OTHER' AS TYPE, NULL AS URL FROM OTHER WHERE READER = :userId AND SHOW = 1 AND TYPEREMOTE != 'OR' ) GROUP BY NID ORDER BY MAX(END_DATE) DESC OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY;
⚠️ 关键注意事项
- UNION → UNION ALL：原始查询中 UNION 会隐式去重，但子查询各分支数据天然不重叠（TYPE 不同），使用 UNION ALL 可显著提升性能。
- GROUP BY 必须精简：仅保留 NID —— 这是实现“跨类型合并”的前提。任何额外字段（如 TYPE, HID）都必须通过聚合函数包裹，否则 Oracle 将报错 ORA-00979: not a GROUP BY expression。
-
非数值字段的聚合策略：
- HID：若允许任选一个，用 MIN(HID) 或 MAX(HID)；若需全部展示，改用 LISTAGG(HID, ', ')；
- TYPE：用 LISTAGG(DISTINCT TYPE, ', ') 汇总所有类型，避免冗余；
- URL：若业务要求取首个有效值，推荐 FIRST_VALUE(URL IGNORE NULLS) OVER (PARTITION BY NID ORDER BY ...)（需配合窗口函数重写）；简单场景下 MAX(URL) 可接受（因 NULL
- Java 参数安全：:userId、:offset、:size 仍需通过 PreparedStatement 绑定，防止 SQL 注入。
✅ 效果验证（示例）
输入（原始多行）：
NID | START_DATE | END_DATE | HID | TYPE | URL 5 | 2021-04-30 | 2021-05-10 | 60 | HISTORICAL | https://a 5 | 2021-04-23 | 2021-05-09 | 70 | VISUALIZATION| null 5 | 2021-01-01 | 2021-02-01 | 80 | OTHER | null
输出（聚合后单行）：
NID | START_DATE_ | END_DATE_ | HID | TYPES | URL 5 | 2021-01-01 | 2021-05-10 | 60 | HISTORICAL,OTHER,VISUALIZATION | https://a
此方案彻底解决重复 NID 的碎片化问题，在保证准确性的前提下，兼顾可读性与执行效率。
