sql - 对交叉表查询 sql 中的聚合字段进行排序

Question

我有一个交叉表查询，我需要按降序对聚合字段进行排序。有没有办法做到这一点？价格是我需要排序的字段。

TRANSFORM Sum([dbo_RX Reporting].Price) AS SumOfPrice
SELECT [dbo_RX Reporting].[Master Drug Name], Sum([dbo_RX Reporting].Price) AS Total
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analg*") AND (([dbo_RX Reporting].Period) Like "2018*") AND (([dbo_RX Reporting].[Plan Name])="Alabama"))
GROUP BY  [dbo_RX Reporting].[Master Drug Name]
PIVOT [dbo_RX Reporting].Period;


Master Drug Name           Total    201804       201805       201806    201807
ACETAMINOPHEN           $1,311.72   $335.27     $330.92     $286.47     $359.06 
ACETAMINOPHEN/CODEINE   $2,399.87   $891.89     $543.60     $658.08     $306.30 
ALLOPURINOL               $976.27   $215.98     $234.16     $263.96     $262.17 
Amrix                   $1,065.14             $1,065.14         
APAP                    $1,526.08   $415.34     $524.56     $297.04     $289.14 

标签： sqlms-access

Answer 1

因为交叉表查询是特殊类型的 MS Access 查询，它不会运行通常的ORDER BY子句。在聚合查询中，您可以引用计算的聚合列ORDER BY：

SELECT [dbo_RX Reporting].[Master Drug Name], Sum([dbo_RX Reporting].Price) AS [Total]
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like 'Analg*') 
   AND (([dbo_RX Reporting].Period) Like '2018*') 
   AND (([dbo_RX Reporting].[Plan Name])='Alabama'))
GROUP BY  [dbo_RX Reporting].[Master Drug Name]
ORDER BY Sum([dbo_RX Reporting].Price) DESC

对于交叉表查询，您需要保存原始查询并运行第二个查询来调用ORDER BY.

SELECT * FROM myCrosstabQ q ORDER BY q.[Total] DESC

---

不，您不能TRANSFORM在子查询中运行 a 作为派生表来仅处理一个查询。

SELECT * FROM (TRANSFORM ...) q ORDER BY q.[Total] DESC

TRANSFORM但是，与基于子查询派生表的相反在 MS Access SQL 中是有效的：

TRANSFORM Sum(dt.Price) AS SumOfPrice
SELECT dt.[Master Drug Name], Sum(dt.Price) AS [Total]
FROM (SELECT TOP 5 * FROM [dbo_RX Reporting]) dt
WHERE (((dt.[Therapeutic Class]) Like '*Analg*') 
   AND ((dt.Period) Like '2018*') AND ((dt.[Plan Name])='Alabama'))
GROUP BY dt.[Master Drug Name]
PIVOT dt.Period;