包括计算列在内的动态排序依据子句不起作用

Question

我有一个这样的语句：

DECLARE @OrderBy nvarchar(50)
SELECT
    t1.Forename,
    t1.Surname,
/** Calculcated Column Start **/
    (
    SELECT
      (
      SELECT Count(FulfilledDelivery) FROM dbo.Deliveries d1 WHERE d1.UserID = t1.UserID
      )
      -
      (
      SELECT Count(CancelledDelivery) FROM dbo.Deliveries d1 WHERE d1.UserID = t1.UserID
      )
    ) AS 'Deliveries'
/** Calculcated Column End **/
FROM
    TableName t1
WHERE
ORDER BY 
    CASE WHEN @OrderBy = 'Deliveries' THEN 'Deliveries' END DESC,
    CASE WHEN @OrderBy = 'Forename' THEN t1.Forename END DESC,
    CASE WHEN @OrderBy = 'Surname' THEN t1.Surname END DESC
    END;

基本上，除了计算列"Deliveries"在OrderBy子句中不起作用之外，这一切都很好。我没有从SQL代码中得到任何错误，但当我将@OrderBy参数设置为"Deliveries"时，它根本没有按计算列"Deliveries"排序。

如果我删除了动态order by子句，那么它可以像这样工作：

.... 
ORDER BY 
   'Deliveries' DESC;

如何使其在动态版本中工作？

Answer 1

yes别名不能通过在顺序中访问，您可以使用CTE并获得计算列的名称，或者您需要通过按顺序重复整个计算

;WITH CTE T
as
(
SELECT
    t1.Forename,
    t1.Surname,
/** Calculcated Column Start **/
    (
    SELECT
      (
      SELECT Count(FulfilledDelivery) FROM dbo.Deliveries d1 WHERE d1.UserID = t1.UserID
      )
      -
      (
      SELECT Count(CancelledDelivery) FROM dbo.Deliveries d1 WHERE d1.UserID = t1.UserID
      )
    ) AS 'Deliveries'
/** Calculcated Column End **/
FROM
    TableName t1
)
SELECT * FROM T
ORDER BY 
    CASE WHEN @OrderBy = 'Deliveries' THEN T.Deliveries END DESC,
    CASE WHEN @OrderBy = 'Forename' THEN T.Forename END DESC,
    CASE WHEN @OrderBy = 'Surname' THEN T.Surname END DESC
    END;