Oracle SQL子查询结果合并：按NID聚合日期并去重

原查询的问题在于GROUP BY子句包含了NID之外的TYPE、HID、URL字段，导致同一NID下只要这几个字段有差异就会生成独立分组，无法合并成一行。要实现按NID合并并取全局最早/最晚日期，需要调整分组逻辑和聚合方式：

修改后的SQL查询 #

"SELECT NID, MIN(START_DATE) AS START_DATE_, MAX(END_DATE) AS END_DATE_, " +
// 若需拼接所有非空HID用LISTAGG；若要固定值直接写'idc'
"LISTAGG(DISTINCT HID, ', ') WITHIN GROUP (ORDER BY HID) AS HID, " +
// 取出现次数最多的TYPE，也可根据业务替换为其他聚合方式
"MODE() WITHIN GROUP (ORDER BY TYPE) AS TYPE, " +
// 取非空的URL，若无需求可忽略或调整聚合逻辑
"MAX(URL) AS URL " +
"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 " +
    "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 " +
    "FROM OTHER " +
    "WHERE READER = :userId AND SHOW = 1 AND TYPEREMOTE != 'OR' " +
") GROUP BY NID " + // 仅按NID分组，确保同一NID的所有记录合并为一行
"ORDER BY MAX(END_DATE) DESC " +
"OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY";

关键调整说明 #

1. 仅按NID分组：这是合并同一NID行的核心，确保所有属于同一NID的记录被归为一个组。
2. 日期聚合：用MIN(START_DATE)取全局最早开始日期，MAX(END_DATE)取全局最晚结束日期，直接满足需求。
3. HID字段处理：
   • 若需要将所有非空HID拼接成字符串（示例中的idc可视为多个HID的简化表示），使用Oracle的LISTAGG函数实现去重拼接。
   • 若业务要求HID为固定值，直接替换为'idc' AS HID即可。
4. TYPE和URL字段处理：
   • MODE() WITHIN GROUP (ORDER BY TYPE)：自动取出现次数最多的TYPE值，适配多数业务场景。
   • MAX(URL)：若同一NID下有多个URL值，取非空的最大值；若无相关需求，可根据实际情况调整或忽略该字段。
5. UNION替换为UNION ALL：原UNION会自动去重，若确认子查询无重复记录，用UNION ALL可显著提升查询性能。

验证结果 #

执行修改后的查询，同一NID的记录会合并为一行，日期取全局最早和最晚，HID按需求处理为拼接值或固定值，完全匹配你的期望结果。

内容的提问来源于stack exchange，提问作者Mangu