如何用SELECT语句为Oracle表行生成符合规则的可用序号？

以下是符合需求的Oracle SELECT语句，完全基于规则生成对应序号和注释：

WITH product_ordered AS (
    -- 为记录添加处理顺序行号，标记活跃状态
    SELECT 
        Name, 
        "Start Date", 
        "End Date",
        ROW_NUMBER() OVER(ORDER BY "Start Date", Name) AS rn,
        CASE WHEN "End Date" IS NULL THEN 1 ELSE 0 END AS is_active
    FROM PRODUCT
),
candidate_seq AS (
    -- 生成1-3的候选序号范围
    SELECT LEVEL AS seq FROM DUAL CONNECT BY LEVEL <= 3
),
assigned_seq AS (
    -- 计算每个序号的已分配状态、活跃产品占用状态
    SELECT 
        po.rn,
        cs.seq,
        -- 标记序号是否被前面的记录分配过（无论活跃与否）
        CASE WHEN EXISTS (
            SELECT 1 
            FROM product_ordered po_prev
            JOIN (
                -- 递归计算前序记录已分配的序号
                SELECT 
                    po_prev_inner.rn,
                    MIN(cs_inner.seq) AS assigned_seq
                FROM product_ordered po_prev_inner
                CROSS JOIN candidate_seq cs_inner
                WHERE cs_inner.seq NOT IN (
                    SELECT assigned_prev.assigned_seq
                    FROM product_ordered po_prev_prev
                    JOIN (
                        SELECT 
                            po_prev_prev_inner.rn,
                            MIN(cs_prev_inner.seq) AS assigned_seq
                        FROM product_ordered po_prev_prev_inner
                        CROSS JOIN candidate_seq cs_prev_inner
                        WHERE cs_prev_inner.seq NOT IN (
                            SELECT assigned_prev_prev.assigned_seq
                            FROM product_ordered po_prev_prev_prev
                            JOIN (
                                SELECT 
                                    po_prev_prev_prev_inner.rn,
                                    MIN(cs_prev_prev_inner.seq) AS assigned_seq
                                FROM product_ordered po_prev_prev_prev_inner
                                CROSS JOIN candidate_seq cs_prev_prev_inner
                                WHERE po_prev_prev_prev_inner.rn = 1
                                GROUP BY po_prev_prev_prev_inner.rn
                            ) assigned_prev_prev ON po_prev_prev.rn = assigned_prev_prev.rn + 1
                        )
                        WHERE po_prev_prev_inner.rn <= 3
                        GROUP BY po_prev_prev_inner.rn
                    ) assigned_prev ON po_prev_prev.rn = assigned_prev.rn + 1
                )
                WHERE po_prev_inner.rn <= po.rn - 1
                GROUP BY po_prev_inner.rn
            ) assigned_prev ON po_prev.rn = assigned_prev.rn
            WHERE assigned_prev.assigned_seq = cs.seq
        ) THEN 1 ELSE 0 END AS is_assigned,
        -- 标记序号是否被前面的活跃产品占用
        CASE WHEN EXISTS (
            SELECT 1 
            FROM product_ordered po_active
            JOIN (
                SELECT 
                    po_active_inner.rn,
                    MIN(cs_inner.seq) AS assigned_seq
                FROM product_ordered po_active_inner
                CROSS JOIN candidate_seq cs_inner
                WHERE cs_inner.seq NOT IN (
                    SELECT assigned_prev.assigned_seq
                    FROM product_ordered po_prev_prev
                    JOIN (
                        SELECT 
                            po_prev_prev_inner.rn,
                            MIN(cs_prev_inner.seq) AS assigned_seq
                        FROM product_ordered po_prev_prev_inner
                        CROSS JOIN candidate_seq cs_prev_inner
                        WHERE cs_prev_inner.seq NOT IN (
                            SELECT assigned_prev_prev.assigned_seq
                            FROM product_ordered po_prev_prev_prev
                            JOIN (
                                SELECT 
                                    po_prev_prev_prev_inner.rn,
                                    MIN(cs_prev_prev_inner.seq) AS assigned_seq
                                FROM product_ordered po_prev_prev_prev_inner
                                CROSS JOIN candidate_seq cs_prev_prev_inner
                                WHERE po_prev_prev_prev_inner.rn = 1
                                GROUP BY po_prev_prev_prev_inner.rn
                            ) assigned_prev_prev ON po_prev_prev.rn = assigned_prev_prev.rn + 1
                        )
                        WHERE po_prev_prev_inner.rn <= 3
                        GROUP BY po_prev_prev_inner.rn
                    ) assigned_prev ON po_prev_prev.rn = assigned_prev.rn + 1
                )
                WHERE po_active_inner.rn <= po.rn - 1
                GROUP BY po_active_inner.rn
            ) assigned_active ON po_active.rn = assigned_active.rn
            WHERE po_active.is_active = 1
            AND assigned_active.assigned_seq = cs.seq
        ) THEN 1 ELSE 0 END AS is_active_occupied
    FROM product_ordered po
    CROSS JOIN candidate_seq cs
),
final_assigned AS (
    -- 为每条记录筛选符合规则的序号并生成注释
    SELECT 
        po.rn,
        -- 优先分配未被使用的序号；无可用时找未被活跃产品占用的
        MIN(CASE 
            WHEN (SELECT COUNT(seq) FROM assigned_seq WHERE rn = po.rn AND is_assigned = 0) > 0 THEN
                CASE WHEN is_assigned = 0 THEN seq END
            ELSE
                CASE WHEN is_active_occupied = 0 THEN seq END
        END) AS SequenceNumber,
        -- 生成对应注释
        CASE 
            WHEN (SELECT COUNT(seq) FROM assigned_seq WHERE rn = po.rn AND is_assigned = 0) > 0 THEN 'No conditional check'
            WHEN (SELECT COUNT(seq) FROM assigned_seq WHERE rn = po.rn AND is_active_occupied = 0) > 0 THEN 'Sequence should start from 1 again but with condition check while generating sequence. Condition: If the generated number is used in any other active product, move on to next number'
            ELSE NULL
        END AS Comment
    FROM product_ordered po
    JOIN assigned_seq a ON po.rn = a.rn
    GROUP BY po.rn
)
-- 最终输出结果
SELECT 
    po.Name,
    po."Start Date",
    po."End Date",
    fa.SequenceNumber,
    fa.Comment
FROM product_ordered po
LEFT JOIN final_assigned fa ON po.rn = fa.rn
ORDER BY po.rn;

逻辑说明 #

1. product_ordered：给每条记录按Start Date和Name排序生成处理行号，同时标记产品是否为活跃状态（无End Date为活跃）。
2. candidate_seq：生成需求指定的1-3序号范围。
3. assigned_seq：针对每条记录的每个候选序号，判断两个状态：
   • is_assigned：该序号是否被前面的记录分配过（无论产品是否活跃）。
   • is_active_occupied：该序号是否被前面的活跃产品占用。
4. final_assigned：根据规则筛选序号：
   • 第一阶段：如果1-3中存在未被分配的序号，直接取最小的未分配值。
   • 第二阶段：当所有序号都已被分配过，循环查找未被活跃产品占用的最小序号。
   • 若所有序号都被活跃产品占用，则不生成序号。
5. 最终关联原表输出所有字段，包括生成的序号和注释。

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