SQL Server CTE子查询ORDER BY报Msg1033错误的解决方法咨询

我来帮你搞定这个排序报错的问题！你碰到的Msg 1033错误，核心原因是SQL Server的语法规则限制：在视图、内联函数、派生表、子查询和CTE这类对象里，单独使用ORDER BY是不允许的，除非同时搭配TOP、OFFSET或者FOR XML子句。这是因为这些对象本质属于无序的集合，内部排序不会对最终结果产生实际作用，反而会触发语法校验错误。

修改后的正确代码 #

;WITH myTree AS ( 
    SELECT 
        y.User_id, 
        y.user_usercode, 
        y.user_username, 
        y.user_uplineID, 
        trans_WinLose, 
        y.User_ID AS sourceID, 
        trans_id, 
        trans_Rolling, 
        y.User_Level, 
        lvl1.User_Level AS Level_lvl1, 
        lvl2.User_Level AS Level_lvl2, 
        y.User_GivenPT, 
        lvl1.User_GivenPT AS GivenPT_lvl1, 
        lvl2.User_GivenPT AS GivenPT_lvl2, 
        y.User_GivenComm, 
        lvl1.User_GivenComm AS downline_Comm 
    FROM tbl_user y 
    INNER JOIN tbl_trans x ON x.trans_Robot_ID = y.User_RobotID 
    INNER JOIN tbl_user lvl1 ON y.user_uplineID = lvl1.User_ID 
    INNER JOIN tbl_user lvl2 ON lvl1.user_uplineID = lvl2.User_ID 
    UNION ALL 
    SELECT 
        u.User_ID, 
        u.user_usercode, 
        u.user_username, 
        u.user_uplineID, 
        t.trans_WinLose, 
        t.sourceID AS sourceID, 
        t.trans_Id, 
        t.trans_Rolling, 
        u.User_Level, 
        t.User_Level, 
        t.Level_lvl1, 
        u.User_GivenPT, 
        t.User_GivenPT, 
        t.GivenPT_lvl1, 
        u.User_GivenComm, 
        t.User_GivenComm 
    FROM myTree t 
    INNER JOIN tbl_user u ON t.user_uplineID = u.User_ID 
) 
SELECT * 
FROM (
    SELECT 
        mytree.*, 
        (
            SELECT CASE 
                WHEN Level_lvl1 = 7 THEN GivenPT_lvl1 
                WHEN level_lvl2 = 7 THEN User_GivenPT-GivenPT_Lvl2 
                ELSE (CASE WHEN (User_GivenPT-GivenPT_lvl1) > 0 THEN User_GivenPT - GivenPT_lvl1 ELSE 0 END) 
            END
        ) AS Net_PT 
    FROM Mytree
) AS c
ORDER BY c.trans_ID; -- 将排序移到最外层

关键修改点 #

1. 移除了内部派生表中的ORDER BY mytree.trans_ID，因为它属于无效的语法使用场景
2. 在最外层的SELECT语句末尾添加ORDER BY c.trans_ID，这样就能对最终返回的结果集进行排序，完全符合SQL Server的语法要求

这样修改后，既实现了你按trans_ID排序的需求，又不会触发语法错误啦！

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