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

Question

我想实现将返回排序列表的简单 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

我收到以下错误消息:

Incorrect syntax near 'ORDER'. Expected 'AS', 'ID' or 'QUOTED_id'

Answer 1

在大多数数据库中，您只能放置一个 order by在工会结束时。

因为联合抽象了单个表别名，所以您只需列出列名。所以省略 fr. :

ORDER BY DisplayName