sql - 在 CTE/集合运算符中排序

Question

因此，我的业务需求是每天获得前 30 位客户（有关系），每个客户每周只能选择一次。所以我做了三个查询，每个都一样（很好），但我对理论基础有疑问。

1）第一种方法，当我有两个 CTE 并在作为集合操作（​​除外）一部分的表达式期间从这两组中选择 TOP 30 时，但是 - 据我所知 - 我们不应该在集合操作期间使用 order by ， 正确的？顺便说一句，它工作得很好

WITH A  
AS (select dbo.customerentities.customer_id, first_name, last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions 
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 2) and convert(date, 
getdate() - 1) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null), 

B  
AS (select dbo.customerentities.customer_id, first_name, last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 1) and convert(date, 
getdate()) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null)

select *from B where customer_id in
(select top 30 with ties customer_id from B order by sale desc
except
select top 30 with ties customer_id from A order by sale desc)
order by sale desc

2）第二个几乎相同，但我决定选择 CTE 级别的前 30 名，但再一次 - CTE 是关系，对吗？所以我们不应该使用 order by 关系吗？否则，它会起作用。

WITH A  
AS (select top 30 with ties dbo.customerentities.customer_id, first_name, 
last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions 
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 2) and convert(date, 
getdate() - 1) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null
order by sale desc), 

B  
AS (select top 30 with ties dbo.customerentities.customer_id, first_name, 
last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 1) and convert(date, 
getdate()) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null
order by sale desc)

select *from B where customer_id in
(select customer_id from B 
except
select customer_id from A )
order by sale desc

3）第三种方法是最糟糕的，但我对此毫无疑问。

select A.customer_id, A.first_name, A.last_name, A.tel, A.sum_sale
from (select top 30 with ties dbo.customerentities.customer_id, first_name, 
last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 1) and convert(date, 
getdate()) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null
order by sale desc) as A
left join (select top 30 with ties dbo.customerentities.customer_id, 
first_name, 
last_name, tel, 
sale.sum_sale 
from dbo.CustomerSubscriptions ---check for client's subscriptions 
left join dbo.CustomerEntities on dbo.CustomerSubscriptions.customer_id = 
dbo.CustomerEntities.customer_id 
left join (select customer_id, sum (sale) as sum_sale, from dbo.OrderTable 
where sale_date between CONVERT(DATE, getdate() - 2) and convert(date, 
getdate() - 1) group by customer_id) as sale on CustomerEntities.customer_id 
= sale.customer_id
where not sale.sum_sale is null
order by sale desc) as B on A.customer_id = B.customer_id
where B.customer_id is null --- to exclude customers which already received 
--- bonus day earlier
order by sale desc

所以我的问题是 - 我应该使用第三种方法还是可以轻松地使用 CTE/set 操作的最高阶？

标签： sqlsql-server