如何在SQL中实现：行数超10万时取前60%，否则返回全部行

根据需求，当查询结果行数超过10万时取前60%，否则返回全部记录，以下是主流数据库的具体实现方式：

MySQL（8.0+ 支持窗口函数） #

WITH total_cte AS (
    -- 替换为你的实际查询，统计总记录数
    SELECT COUNT(*) AS total_rows FROM your_table WHERE your_condition
),
ranked_data AS (
    SELECT 
        t.*,
        ROW_NUMBER() OVER () AS row_num,
        tc.total_rows
    -- 替换为你的实际查询
    FROM your_table t 
    WHERE your_condition
    CROSS JOIN total_cte tc
)
SELECT * EXCEPT(row_num, total_rows)
FROM ranked_data
WHERE row_num <= CASE 
    WHEN total_rows > 100000 THEN FLOOR(total_rows * 0.6)
    ELSE total_rows
END;

PostgreSQL #

WITH total_cte AS (
    -- 替换为你的实际查询，统计总记录数
    SELECT COUNT(*) AS total_rows FROM your_query
),
ranked_data AS (
    SELECT 
        q.*,
        ROW_NUMBER() OVER () AS row_num,
        tc.total_rows
    -- 替换为你的实际查询
    FROM your_query q
    CROSS JOIN total_cte tc
)
SELECT q.*
FROM ranked_data
WHERE row_num <= CASE 
    WHEN total_rows > 100000 THEN FLOOR(total_rows * 0.6)
    ELSE total_rows
END;

SQL Server #

方式一：使用变量 #

-- 统计总记录数，替换为你的实际查询
DECLARE @total_rows INT = (SELECT COUNT(*) FROM your_query);
-- 计算需要返回的行数
DECLARE @take_rows INT = CASE WHEN @total_rows > 100000 THEN FLOOR(@total_rows * 0.6) ELSE @total_rows END;

-- 返回结果，替换为你的实际查询
SELECT TOP (@take_rows) * FROM your_query;

方式二：使用CTE窗口函数 #

WITH total_cte AS (
    -- 替换为你的实际查询，统计总记录数
    SELECT COUNT(*) AS total_rows FROM your_query
),
ranked_data AS (
    SELECT 
        q.*,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num,
        tc.total_rows
    -- 替换为你的实际查询
    FROM your_query q
    CROSS JOIN total_cte tc
)
SELECT *
FROM ranked_data
WHERE row_num <= CASE 
    WHEN total_rows > 100000 THEN FLOOR(total_rows * 0.6)
    ELSE total_rows
END;

Oracle #

WITH total_cte AS (
    -- 替换为你的实际查询，统计总记录数
    SELECT COUNT(*) AS total_rows FROM your_query
),
ranked_data AS (
    SELECT 
        q.*,
        ROWNUM AS row_num,
        tc.total_rows
    -- 替换为你的实际查询
    FROM your_query q, total_cte tc
)
SELECT *
FROM ranked_data
WHERE row_num <= CASE 
    WHEN total_rows > 100000 THEN FLOOR(total_rows * 0.6)
    ELSE total_rows
END;

注意事项 #

• 请将上述代码中的your_query或your_table WHERE your_condition替换为你的实际查询语句。
• 如果查询需要固定排序，需在窗口函数的OVER()子句中添加对应的ORDER BY规则，确保结果顺序稳定。

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