如何从服务和销售表中返回前30名客户收入组合

Question

我可以使用此查询提取各个行，但希望将这两个表组合起来，并按客户编号显示按销售人员分列的前30位客户。

select top 30   sil.[Sell-to Customer No_]as 'Customer No.',
                cus.[Name], 
                sil.[Responsibility Center], 
                sil.[Amount] as 'Total',
                'SALES'
FROM            [Sales Invoice Line] sil left outer join [Customer]cus
on              sil.[Sell-to Customer No_] = cus.[No_]  
where           sil.[Amount] > 0
and             sil.[Responsibility Center] != 'cis'
and             sil.[Posting Date] between '10-01-13' and (current_timestamp)

group by        sil.[Amount], sil.[Sell-to Customer No_], sil.[Responsibility Center], cus.[Name]


union all


select top 30   sil.[Customer No_],
                cus.[Name], 
                sil.[Responsibility Center], 
                sil.[Amount] as 'Total', 
                'SERVICE'
FROM            [Service Invoice Line] sil left outer join [Customer]cus
on              sil.[Customer No_] = cus.[No_]
where           sil.[Amount] > 0
and             sil.[Responsibility Center] != 'cis'
and             sil.[Posting Date] between '10-01-13' and (current_timestamp)

group by        sil.[Amount], sil.[Customer No_], sil.[Responsibility Center], cus.[Name]

Answer 1

用一个查询包装返回60行的整个查询，以获得前30行。

select top 30 x, y, z 
from (
    select top 30 x, y, z from a where blah
    union all
    select top 30 x, y, z from b where blah
) x --here's the alias for the subquery
order by topcriteria

order很重要，所以您可以得到适当的查询“顶部”。

编辑:下面是您的求和示例：

select top 30   sil.[Sell-to Customer No_]as 'Customer No.',
                cus.[Name], 
                sil.[Responsibility Center], 
                sum(sil.[Amount]) as 'Total',
                'SALES'
FROM            [Sales Invoice Line] sil left outer join [Customer]cus
on              sil.[Sell-to Customer No_] = cus.[No_]  
where           sil.[Amount] > 0
and             sil.[Responsibility Center] != 'cis'
and             sil.[Posting Date] between '10-01-13' and (current_timestamp)
group by        sil.[Sell-to Customer No_], sil.[Responsibility Center], cus.[Name]