sql - 如何将使用 top 1 子句的查询连接到使用聚合函数的查询？

Question

我有一个使用 TOP 1 子句 (query2) 的查询，我想将它连接到另一个使用聚合函数的查询，但我不知道该怎么做。

我想要一个看起来像这样的结果查询:

"select Count(*), Avg(AmtSpent), TOP 1 (Outlet_Code)...."

请指教。谢谢。

查询 1:

select Count(*), Avg(AmtSpent) from Transact Where CardNo In
(Select CardNo from Card where MemberID = 'Mem003')

查询 2:

select TOP 1 (Outlet_Code) from Transact where CardNo In
(Select CardNo from Card where MemberID = 'Mem003')
group by Outlet_Code
Order by count(Outlet_Code) desc

Answer 1

像这样，只要每个子查询只返回一行，看起来就是这种情况，因为您的一个查询选择了前 1 个，而另一个没有 group by 子句。这适用于 SQL Server 2008。

select TransactCount, TransactAvg, OutletCode
from
(

    select TOP 1 (Outlet_Code) as OutletCode 
    from Transact where CardNo In
    (Select CardNo from Card where MemberID = 'Mem003')
    group by Outlet_Code
    Order by count(Outlet_Code) desc


) rsOutletCode
cross join
(

    select Count(*) as TransactCount, Avg(AmtSpent) as TransactAvg
    from Transact 
    Where CardNo In
    (Select CardNo from Card where MemberID = 'Mem003')

) rsTransact