SQL Server 在同一查询中多次选择多个计数日期

Question

我正在尝试找出一种方法来从Orders表中提取每个customer id的订单计数以及日期范围内第一个和最后一个订单date，其中每个订单都有buyer_id和seller_id。 订单表包含OrderNumber、Buyer_ID、Seller_ID、OpenDate、ClosedDate 。 我可以单独运行以下查询来实现我的目标，但如果可能的话，我希望将所有内容都放在同一个查询中。

Order_Table：

OrderNumber, Buyer_ID, Seller_ID, OpenDate, ClosedDate

Buyer_ID订单：

select Buyer_ID, COUNT(*)as BuyerOrders
from
(
    select Buyer_ID
    from Orders
    where OpenDate between @StartDate and @EndDate
)
a
group by Buyer_ID

Seller_ID订单：

select Seller_ID, COUNT(*)as SellerOrders
from
(
    select Seller_ID
    from Orders
    where OpenDate between @StartDate and @EndDate
)
a
group by Seller_ID

该范围内的第一个和最后一个订单的日期：？？

任何意见都非常感谢！

由于结果是一个联合，并且同一customer_id可能同时具有买方和卖方的条目，因此如何将信息放在同一行中？我的第一次尝试是根据联合的结果创建一个临时表，但是对于如何在同一行上为结果表中的每个Customer_ID显示买方订单计数，卖方订单计数等，我画了一个空白。

Answer 1

select 'Buyer' as Type, 
    Buyer_ID ID, 
    Count(*) OrderCount, 
    Min(OpenDate) FirstOrder, 
    Max(OpenDate) LastOrder
from Orders
where OpenDate between @StartDate and @EndDate
group by Buyer_ID
union
select 'Seller', 
    Seller_ID, 
    Count(*), 
    Min(OpenDate) FirstOrder, 
    Max(OpenDate) LastOrder
from Orders
where OpenDate between @StartDate and @EndDate
group by Seller_ID

[编辑] 是的，有点厚脸皮接受我的回答，然后不接受它并改变问题！无论如何，请尝试以下操作：

;with BuyerFirst (Buyer_ID, RowNum, BuyerCount, OrderID, OpenDate)
As
(select Buyer_ID, 
    ROW_NUMBER() over (partition by Buyer_ID order by OpenDate, OrderID) as RowNum,
    count(*) over (partition by Buyer_ID) As BuyerCount, 
    OrderID, 
    OpenDate
from Orders
where OpenDate between @StartDate and @EndDate),
BuyerLast (Buyer_ID, RowNum, OrderID, OpenDate)
As
(select Buyer_ID, 
    ROW_NUMBER() over (partition by Buyer_ID order by OpenDate Desc, OrderID Desc) as RowNum,
    OrderID, 
    OpenDate
from Orders
where OpenDate between @StartDate and @EndDate),
SellerFirst (Seller_ID, RowNum, SellerCount, OrderID, OpenDate)
As
(select Seller_ID, 
    ROW_NUMBER() over (partition by Seller_ID order by OpenDate, OrderID) as RowNum,
    count(*) over (partition by Buyer_ID) As SellerCount, 
    OrderID, 
    OpenDate
from Orders
where OpenDate between @StartDate and @EndDate),
SellerLast (Seller_ID, RowNum, OrderID, OpenDate)
As
(select Seller_ID, 
    ROW_NUMBER() over (partition by Seller_ID order by OpenDate Desc, OrderID Desc) as RowNum,
    OrderID, 
    OpenDate
from Orders
where OpenDate between @StartDate and @EndDate)

select c.*, 
    bf.BuyerCount, 
    bf.OpenDate As BuyerFirstOrderDate,
    bf.OrderID As BuyerFirstOrderID, 
    bl.OpenDate As BuyerLastOrderDate, 
    bl.OrderID As BuyerLastOrderID,
    sf.SellerCount, 
    sf.OpenDate As SellerFirstOrderDate,
    sf.OrderID As SellerFirstOrderID, 
    sl.OpenDate As SellerLastOrderDate, 
    sl.OrderID As SellerLastOrderID
from Customers c
    left join BuyerFirst bf on c.CustomerID = bf.Buyer_ID and bf.RowNum = 1
    left join SellerFirst sf on c.CustomerID = sf.Seller_ID and sf.RowNum = 1
    left join BuyerLast bl on c.CustomerID = bl.Buyer_ID and bl.RowNum = 1
    left join SellerLast sl on c.CustomerID = sl.Seller_ID and sl.RowNum = 1