主页 / user-7709086

kagmole's questions

Martin Hope
kagmole
Asked: 2025-04-08 17:16:39 +0800 CST
  • 6

我有以下数据：

用户身份 子编号 教育 塞姆奥德
1 701 500 10
1 702 255 11
1 703 500 12
1 704 500 14
1 705 500 15
1 706 500 16
1 707 500 17
2 ... ... ..

给定 aUserId和SubId，我想SemOrd在同一个EduId链中找到最小的那个。
例如：给定UserId = 1和SubId = 706，结果应该是SemOrd = 12。
由于它意味着一个链，所以我选择了递归 CTE 解决方案。

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
), Chain AS (
    SELECT
        SemOrd AS SemOrd_First,
        UserId,
        SubId,
        EduId,
        SemOrd,
        Ordinal,
        OrdinalParted
    FROM Base
    UNION ALL
    SELECT
        Chain.SemOrd_First,
        Base.UserId,
        Base.SubId,
        Base.EduId,
        Base.SemOrd,
        Base.Ordinal,
        Base.OrdinalParted
    FROM
        Base
        INNER JOIN Chain
            ON Base.UserId = Chain.UserId
            AND Base.EduId = Chain.EduId
            AND Base.Ordinal = Chain.Ordinal + 1
            AND Base.OrdinalParted = Chain.OrdinalParted + 1
), Minimal AS (
    SELECT
        UserId,
        SubId,
        MIN(SemOrd_First) AS SemOrd_First
    FROM Chain
    GROUP BY
        UserId,
        SubId
)
SELECT SemOrd_First FROM Minimal WHERE UserId = 1 AND SubId = 706

在纸面上和小型表格上，它有效。
但在生产表上，它基本上无法使用，因为它需要先计算所有 CTE 条目，然后再进行WHERE过滤。查询速度太慢了。

当然，一种解决方案是将该WHERE子句移到第一个 CTE 内：

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
    WHERE UserId = 1 AND SubId = 706
), Chain AS (
/* ... */

这样就很快了。但我想用这个查询创建一个视图。
因此，该WHERE子句不能放在第一个 CTE 中，否则我们在调用视图时无法选择UserIdand 。SubId

问题在于 SQL Server 无法将WHERE第一个 CTE 内的子句冒泡并减少执行计划中的工作量。

所以问题是：

  • 有没有办法让 SQL Server 将该WHERE子句冒泡？
  • 有没有 CTE 递归解决方案的替代方法？

如果以上方法都不起作用，我想最好的选择就是使用存储过程。

sql