基于分类实现Rank4A/Rank4B列排序（禁用CROSS APPLY/CURSOR/CTE）

1 A
2 A
3 B
5 A
6 B
9 B

需要生成两个排名列：
- **Rank4A**：A类行按`RowNum`升序执行`DENSE_RANK()`逻辑；B类行取当前行之前最近的A类排名。
- **Rank4B**：B类行按`RowNum`降序执行`DENSE_RANK()`逻辑；A类行取当前行之后最近的B类排名（即降序视角下最近的B类排名）。

预期结果里的`W`表示无有效值时的占位符，咱们可以用`COALESCE`处理成这个标识。

# 高效解决方案
因为你限制了不能用`CROSS APPLY`、游标、CTE，那纯窗口函数就是最优解——没有关联、没有循环，完全利用数据库的列式计算优化，大数据量下性能极佳。以下是兼容多数现代数据库（SQL Server 2022+、PostgreSQL、Oracle）的写法：

```sql
SELECT 
    RowNum,
    category,
    -- 处理Rank4A
    COALESCE(
        CASE WHEN category = 'A' 
             THEN CAST(DENSE_RANK() OVER (ORDER BY CASE WHEN category='A' THEN RowNum END) AS VARCHAR(10))
             ELSE LAST_VALUE(
                 CASE WHEN category='A' THEN DENSE_RANK() OVER (ORDER BY CASE WHEN category='A' THEN RowNum END) END
             ) OVER (ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) IGNORE NULLS
        END,
        'W'
    ) AS Rank4A,
    -- 处理Rank4B
    COALESCE(
        CASE WHEN category = 'B' 
             THEN CAST(DENSE_RANK() OVER (ORDER BY RowNum DESC) AS VARCHAR(10))
             ELSE FIRST_VALUE(
                 CASE WHEN category='B' THEN DENSE_RANK() OVER (ORDER BY RowNum DESC) END
             ) OVER (ORDER BY RowNum ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) IGNORE NULLS
        END,
        'W'
    ) AS Rank4B
FROM YourTable
ORDER BY RowNum;

逻辑拆解 #

1. Rank4A核心逻辑：

   • 先给所有A类行计算DENSE_RANK（B类行此处会得到NULL）。
   • 对B类行，用LAST_VALUE(...) IGNORE NULLS按RowNum升序遍历，取到当前行之前最后一个非NULL的A类排名——完美匹配“最近出现的A类排名”的要求。
   • 用COALESCE把极端情况（比如首行是B）的NULL转成W。

2. Rank4B核心逻辑：

   • 先给所有B类行按RowNum降序计算DENSE_RANK（A类行此处会得到NULL）。
   • 对A类行，用FIRST_VALUE(...) IGNORE NULLS按RowNum升序遍历，取到当前行之后第一个非NULL的B类排名——对应“降序视角下最近的B类排名”的要求。
   • 同样用COALESCE把没有后续B类行的A类行结果转成W。

这个方案完全符合你的限制，没有用到任何禁用语法，而且窗口函数是数据库原生优化的，大数据量下的性能远优于关联或循环写法。

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