SQL 动态 ORDER BY 使用别名 [英] SQL dynamic ORDER BY using alias

问题描述

使用 SQL Server，我可以使用别名对普通的 SELECT 查询进行排序:

Using SQL Server, I can order a normal SELECT query using an alias:

SELECT   
   u.FirstName + ' ' + u.LastName as PhysicianName, COUNT(r.Id) as ReferralsCount
FROM     
   Referrals r 
INNER JOIN
   Users u ON r.PhysicianId = u.Id
GROUP BY 
   r.PhysicianId, u.FirstName, u.LastName
ORDER BY 
   PhysicianName

然而，尝试使用动态 ORDER BY 做同样的事情:

However, attempting to do the same thing with a dynamic ORDER BY:

SELECT   
   u.FirstName + ' ' + u.LastName as PhysicianName, COUNT(r.Id) as ReferralsCount
FROM     
   Referrals r 
INNER JOIN
   Users u ON r.PhysicianId = u.Id
GROUP 
   BY r.PhysicianId, u.FirstName, u.LastName
ORDER BY 
    CASE WHEN @orderby = 'PhysicianName' THEN PhysicianName END,
    CASE WHEN @orderby = 'ReferralsCount' THEN ReferralsCount END

产生以下错误:

消息 207，级别 16，状态 1，第 10 行
列名PhysicianName"无效.
Msg 207, Level 16, State 1, Line 11无效的列名ReferralsCount".

Msg 207, Level 16, State 1, Line 10
Invalid column name 'PhysicianName'.
Msg 207, Level 16, State 1, Line 11 Invalid column name 'ReferralsCount'.

推荐答案

SELECT 中定义的列别名只能在 ORDER BY 中单独使用.不是在表达.

Column aliases defined in the SELECT can only be used in the ORDER BY on their own. Not in an expression.

您可以按如下方式调整您的原始尝试.

You can adjust your original attempt as follows.

;WITH T
     AS (SELECT u.FirstName + ' ' + u.LastName AS PhysicianName,
                COUNT(r.Id)                    AS ReferralsCount
         FROM   Referrals r
                INNER JOIN Users u
                  ON r.PhysicianId = u.Id
         GROUP  BY r.PhysicianId,
                   u.FirstName,
                   u.LastName)
SELECT *
FROM   T
ORDER  BY CASE
            WHEN @orderby = 'PhysicianName' THEN PhysicianName
          END,
          CASE
            WHEN @orderby = 'ReferralsCount' THEN ReferralsCount
          END 

这篇关于SQL 动态 ORDER BY 使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！