如何在UNIONS之间的SQL中放置ORDER BY子句

| 我想实现将返回排序列表的简单SQL查询。问题是我将
ORDER BY
子句放在任何地方时都出现语法错误。 
SELECT 
    fr.FunctionRoleID, fr.FunctionRoleInternalName
FROM 
    users u 
JOIN 
    UserRoles ur ON ur.UserID = u.UserID
JOIN 
    Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID
JOIN 
    FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
WHERE 
    u.UserName = @UserName
    AND u.Active = 1

UNION 

SELECT 
    fr.FunctionRoleID, fr.FunctionRoleInternalName
FROM 
    Roles r
JOIN 
    Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID
JOIN 
    FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID
WHERE 
    r.RoleName = \'Authenticated Users\'
    AND @UserName IS NOT NULL
    AND LEN(@UserName) > 0
我要插入的内容： 
ORDER BY fr.DisplayName ASC
编辑 如果我使用创建子查询 
SELECT * 
FROM 
     (
     [my initial query]
     )
ORDER BY 
    [COLUMN NAME] ASC
我收到以下错误消息：   \'ORDER \'附近的语法不正确。预期的\'AS \'，\'ID \'或\'QUOTED_id \'     
2020-02-24 7 条评论
分享

没有找到相关结果

    已邀请:

    犯痪桂涛杭

            在大多数数据库中，您只能在联合的末尾放置一个“ 4”。 由于联合会抽象出各个表别名，因此您只需列出列名。所以省略the5ѭ： 
    ORDER BY DisplayName
        
    2020-02-24 0 0
    分享

    惭法搽

            ORDER BY子句需要放在Union的最后一个SELECT语句之后。     
    2020-02-24 0 0
    分享

    骂狮淋唐便

            
    select * from(
*what you have there*
) as foo
order by DisplayName ASC
    我不在IDE的前面，因此语法可能有点过时，但这就是这个主意。 e：是的，想通通了语法...加了别名:)     
    2020-02-24 0 0
    分享

    缮记箔

            您未选择
    DisplayName
    ，因此无法将其用于
    ORDER BY
    a10ѭ派生的集合。如果要订购它，并从结果中忽略它； 
    ;WITH T (FunctionRoleID, FunctionRoleInternalName, DisplayName) AS (
    SELECT 
        fr.FunctionRoleID, fr.FunctionRoleInternalName, fr.DisplayName
    FROM users u
        JOIN UserRoles ur ON ur.UserID = u.UserID
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
    WHERE 
        u.UserName = @UserName
    AND 
        u.Active = 1

    UNION

    SELECT 
        fr.FunctionRoleID, fr.FunctionRoleInternalName, fr.DisplayName
    FROM 
        Roles r
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID
    WHERE 
        r.RoleName = \'Authenticated Users\'
        and @UserName is not null and LEN(@UserName) > 0
)
SELECT 
    FunctionRoleID, FunctionRoleInternalName
FROM T
    ORDER BY DisplayName
        
    2020-02-24 0 0
    分享

    净爽

            尝试 
    SELECT * FROM (

SELECT  
    fr.FunctionRoleID, fr.FunctionRoleInternalName 



FROM  
    users u  
    JOIN UserRoles ur ON ur.UserID = u.UserID 
    JOIN Roles_FunctionRoles rfr ON rfr.RoleID = ur.RoleID 
    JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID  


WHERE  
    u.UserName = @UserName 
AND  
    u.Active = 1 


UNION  

    SELECT  
        fr.FunctionRoleID, fr.FunctionRoleInternalName 
    FROM  
        Roles r 
        JOIN Roles_FunctionRoles rfr ON rfr.RoleID = r.RoleID 
        JOIN FunctionRoles fr ON fr.FunctionRoleID = rfr.FunctionRoleID 
    WHERE  
        r.RoleName = \'Authenticated Users\' 
        and @UserName is not null and LEN(@UserName) > 0 ) a

ORDER BY a.FunctionRoleInternalName ASC
    基本上，您是根据UNION的结果进行选择，然后执行ORDER BY ...请注意，我使用\“ FunctionRoleInternalName \” ...仅当将其用作列时，才可以将其更改为\“ DiaplayName \” UNION查询中的ALIAS ...例如\“ FunctionRoleInternalName AS DisplayName \”     
    2020-02-24 0 0
    分享

    刷遍派戳

            对于“ 10”，“ 0”在末尾，并应用于两个查询的组合结果；您无法按联合中两个查询都未选择的列进行排序。 您需要做的是在两个查询中都选择fr.DisplayName。那么您可以通过它订购。 如果您不希望显示名称成为输出列之一，则将整个内容嵌套在仅检索所需列的外部查询中。     
    2020-02-24 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.037784099578857