如何在多个UNION查询上创建动态订单?

Question

我有一次大学练习，我必须演示如何方便地使用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

它返回:

tbody> <<tr>客户UISOJ客户GCJSG客户FVXPQ客户EYHKM客户VMLOG客户UISOJ客户IAIJK客户GCJSG客户MDLWA客户CYZTN客户FRXZL客户THHDP客户LCOUJ客户IRRVL客户NYUHS客户FRXZL客户THHDP客户LCOUJ客户IRRVL

客户	总消费额	总订单	描述
客户VMLOG	100.8000	1	最小订单
	357.0000	2	最小订单
	649.0000	3	最小订单
	1488.7000	2	最小订单
	1571.2000	3	最小订单
	100.8000	1	最低支出
	357.0000	2	最低支出
	522.5000	3	最低支出
	649.0000	3	最低支出
	836.7000	5	最低支出
	32555.5500	19	大多数订单
	57317.3900	19	大多数订单
	113236.6800	30	大多数订单
	115673.3900	31日	大多数订单
	117483.3900	28	大多数订单
	52245.9000	18	开支大户
	57317.3900	19	开支大户
	113236.6800	30	开支大户
	115673.3900	31日	开支大户
	117483.3900	28	开支大户

将您的所有查询放在cte中并选择cto中的字段，然后通过

编写您的订单例句:

WITH q
AS
(
SELECT p.ProductID,p.Name,p.ModifiedDate
FROM Production.Product p
UNION 
SELECT ProductCategoryID,Name,ModifiedDate
FROM Production.ProductCategory
)
SELECT *
FROM q
ORDER BY q.Name,q.ModifiedDate

您可以在每个子查询中添加ROW_NUMBER，并使用它来排序最终结果:

。

SELECT s.Client , s.InvoiceAmount,s.Description,s.RN
FROM    (
SELECT TOP 5 f.Client , f.InvoiceAmount,'Small clients' as Description,ROW_NUMBER() OVER (ORDER BY f.InvoiceAmount) RN
FROM dbo.FactSales f
ORDER BY f.InvoiceAmount 
) s
UNION ALL
SELECT l.Client , l.InvoiceAmount,Description,l.RN
FROM    (
SELECT TOP 5 f.Client , f.InvoiceAmount,'Large clients' as Description,ROW_NUMBER() OVER (ORDER BY f.InvoiceAmount DESC) RN
FROM dbo.FactSales f
ORDER BY f.InvoiceAmount DESC
) l
ORDER BY Description,RN;