如何对多UNION合并的TSQL查询实现按分类动态排序？

问题描述 #

我有一个大学作业，要求演示Union的便捷用法，我使用了TSQLV4示例数据库进行实现。

我编写的代码如下：

FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Top Spender' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 2 desc) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Lowest Spender' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 2 ASC) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Most Orders' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 3 desc) base

UNION

SELECT [Customer],[Total Spent],[Total Orders],[Description]
FROM

(SELECT TOP 5 
cust.companyname as [Customer], 
SUM(ord.total) AS [Total Spent],
COUNT(ord.orderid) AS [Total Orders],
'Least Orders' AS [Description]

FROM sales.Orders ord 
JOIN sales.customers cust ON cust.custid = ord.custid
GROUP BY [companyname]
ORDER BY 3 asc) base
order by 4,2

查询返回结果如下：

该代码满足作业要求，我也拿到了A+，但我发现存在排序问题，一直想解决。
我将UNION合并后的整体查询按第4列排序，让同分类的行聚合在一起，但二级排序只能选第2列（Total Spent）或第3列（Total Orders），这就导致一半分类的二级排序逻辑不合理：比如最少/最多订单分类按消费总额排序，或最低/最高消费分类按订单数排序，都不符合需求。
我已经创建了一个表值函数，接收varchar类型参数（计划传入Description列的值），通过IF语句读取@description参数值，返回自定义排序后的UNION查询结果。
但我编写的最后一行排序代码无法实现预期效果：

[...]
ORDER BY 3 asc) base
ORDER BY dbo.FtOrdenamiento(base.[Description])

错误提示：Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FtOrdenamiento", or the name is ambiguous.

请问有什么方法可以实现我需要的排序效果？

解决方案 #

错误原因 #

你创建的是表值函数，这类函数返回的是表结构，只能在FROM子句中调用，不能直接放在ORDER BY子句里使用。如果要在排序逻辑中调用函数，需要改为创建标量值函数，同时要确保函数属于dbo架构、当前数据库账号有该函数的执行权限。但实际上你不需要额外开发函数，用SQL原生的CASE表达式就能实现自定义排序，性能和可维护性更好。

具体实现 #

把你原有代码最后的order by 4,2替换为如下排序逻辑即可：

ORDER BY
  -- 一级排序：自定义分类的展示顺序，可按需调整顺序编号
  CASE [Description]
    WHEN 'Top Spender' THEN 1
    WHEN 'Lowest Spender' THEN 2
    WHEN 'Most Orders' THEN 3
    WHEN 'Least Orders' THEN 4
  END,
  -- 二级排序：匹配对应分类的排序字段
  CASE
    WHEN [Description] IN ('Top Spender', 'Lowest Spender') THEN [Total Spent]
    WHEN [Description] IN ('Most Orders', 'Least Orders') THEN [Total Orders]
  END
  -- 控制排序方向：高消费、多订单按降序，低消费、少订单按升序
  * CASE
    WHEN [Description] IN ('Top Spender', 'Most Orders') THEN -1
    ELSE 1
  END ASC

上述逻辑利用了「数值乘以-1后升序排列等价于原数值降序排列」的特性，只用一段逻辑就覆盖了所有分类的排序需求，不需要额外的函数支持。

内容的提问来源于stack exchange，提问作者Andy