sql - 如何在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 by放在联合的末尾。

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

ORDER BY DisplayName