如何分段抽取Teradata表数据？求实现表子集拆分的SQL语句

场景1：按固定大小拆分数据集（100条拆为5个20条子集） #

通过给每条记录分配行号，再按行号范围过滤，即可实现固定大小的分段抽取。

生成带行号的基础查询 #

SELECT 
    *,
    -- 替换ORDER BY后的字段为实际业务排序依据（如主键、时间），保证分段稳定
    ROW_NUMBER() OVER (ORDER BY 1) AS row_num
FROM tableA;

抽取指定范围的子集 #

• 抽取第1-20条：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 1) AS row_num
    FROM tableA
) t
WHERE row_num BETWEEN 1 AND 20;

• 抽取第21-40条：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 1) AS row_num
    FROM tableA
) t
WHERE row_num BETWEEN 21 AND 40;

以此类推，调整BETWEEN的数值即可获取后续分组。

批量标记分组 #

如果需要一次性给所有记录标注所属分组，可使用整除计算组ID：

SELECT 
    *,
    -- 每20条为一组，生成1-5的组ID
    (ROW_NUMBER() OVER (ORDER BY 1) - 1) / 20 + 1 AS group_id
FROM tableA;

后续通过WHERE group_id = N即可筛选对应分组。

场景2：指定位置分段抽取（6条拆为前2、中2、后2条） #

基于行号和总记录数计算分段范围，实现精准抽取。

抽取前2条 #

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 1) AS row_num
    FROM tableA
) t
WHERE row_num <= 2;

抽取中间2条 #

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
        -- 计算总记录数
        COUNT(*) OVER () AS total_rows
    FROM tableA
) t
WHERE row_num BETWEEN (total_rows / 2) AND (total_rows / 2 + 1);

抽取最后2条 #

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM tableA
) t
WHERE row_num > total_rows - 2;

注意：所有示例中的ORDER BY 1仅为默认排序占位符，实际使用时请替换为业务明确的排序字段（如主键、创建时间等），避免因排序不稳定导致分段结果异常。

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