Oracle SQL：如何先限制结果集，再展示剩余数据？

看起来你遇到了LISTAGG函数的字符串长度截断问题（从ID=2的结果里Years字段显示到1997后变成1...就能看出来），同时想要实现「先限制结果集，再展示剩余数据」的效果——不管是控制每个ID展示的年份数量，还是完整展示所有年份但避免截断，下面给你两种实用的解决方案：

如果你希望每个ID只展示前N个年份，同时标注剩余的数量，可以结合窗口函数ROW_NUMBER()先筛选出前N条，再统计总数量，最后拼接结果：

WITH year_data AS (
    SELECT DISTINCT tbl2.id "ID", tbl1.year "Year"
    FROM table1 tbl1 JOIN table2 tbl2 ON(tbl1.tbl2id = tbl2.id)
),
ranked_years AS (
    SELECT 
        "ID",
        "Year",
        ROW_NUMBER() OVER(PARTITION BY "ID" ORDER BY "Year") AS rn,
        COUNT(*) OVER(PARTITION BY "ID") AS total_count
    FROM year_data
)
SELECT 
    "ID",
    total_count AS "Count",
    CASE 
        WHEN total_count <= 3 THEN LISTAGG("Year", ', ') WITHIN GROUP(ORDER BY "Year")
        ELSE LISTAGG(CASE WHEN rn <=3 THEN "Year" END, ', ') WITHIN GROUP(ORDER BY "Year") || '... 等' || (total_count - 3) || '个'
    END AS "Years"
FROM ranked_years
GROUP BY "ID", total_count;

示例里设置的是只显示前3个年份，你可以把代码中的3改成任意你想要的数量。

如果你的需求是完整展示所有年份，只是默认的LISTAGG因为返回值长度限制（默认是VARCHAR2，最大4000字节）被截断了，可以根据Oracle版本选择不同的处理方式：

适用于Oracle 12cR2及以上版本 #

用LISTAGG的ON OVERFLOW子句，既可以选择截断并提示剩余数量，也可以直接返回CLOB类型：

SELECT 
    "ID", 
    COUNT("Year") "Count", 
    -- 截断并显示剩余数量
    LISTAGG("Year", ', ') WITHIN GROUP(ORDER BY "Year") ON OVERFLOW TRUNCATE '...' WITH COUNT AS "Years"
    -- 或者直接返回CLOB避免截断：
    -- LISTAGG("Year", ', ') WITHIN GROUP(ORDER BY "Year") ON OVERFLOW RETURN NULL AS CLOB AS "Years"
FROM (
    SELECT DISTINCT tbl2.id "ID", tbl1.year "Year"
    FROM table1 tbl1 JOIN table2 tbl2 ON(tbl1.tbl2id = tbl2.id)
)
GROUP BY "ID";

适用于Oracle 12cR2以下版本 #

用XMLAGG拼接成CLOB类型，绕过VARCHAR2的长度限制：

SELECT 
    "ID",
    COUNT("Year") "Count",
    RTRIM(XMLAGG(XMLELEMENT(E, "Year", ', ') ORDER BY "Year").EXTRACT('//text()').GETCLOBVAL(), ', ') AS "Years"
FROM (
    SELECT DISTINCT tbl2.id "ID", tbl1.year "Year"
    FROM table1 tbl1 JOIN table2 tbl2 ON(tbl1.tbl2id = tbl2.id)
)
GROUP BY "ID";

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