将2个复杂查询组合为1

Question

我正在尝试找出是否有一种将这2个查询组合到一个查询中的方法。我已经遇到了我所知道的，无法弄清楚这是否可能。

这是每个位置每天销售(持续一个月(的第一个查询：

if object_id('tempdb..#LY_Data') is not null drop table #LY_Data
select  
    [LocationId]            = ri.LocationId,
    [LY_Date]               = convert(date, ri.ReceiptDate),
    [LY_Trans]              = count(distinct ri.SalesReceiptId),
    [LY_SoldQty]            = convert(money, sum(ri.Qty)),
    [LY_RetailAmount]       = convert(money, sum(ri.ExtendedPrice)),
    [LY_NetSalesAmount]     = convert(money, sum(ri.ExtendedAmount))
into #LY_Data
from rpt.SalesReceiptItem ri
join #Location l
    on ri.LocationId = l.Id
where ri.Ignored = 0
    and ri.LineType = 1 /*Item*/
    and ri.ReceiptDate between @_LYDateFrom and @_LYDateTo
group by 
    ri.LocationId, 
    ri.ReceiptDate

然后，第二查询根据每天该月的总销售量计算一个比率(以后使用(：

if object_id('tempdb..#LY_Data2') is not null drop table #LY_Data2
select 
    [LocationId]            = ly.LocationId,
    [LY_Date]               = ly.LY_Date,
    [LY_Trans]              = ly.LY_Trans,
    [LY_RetailAmount]       = ly.LY_RetailAmount,
    [LY_NetSalesAmount]     = ly.LY_NetSalesAmount,
    [Ratio]                 = ly.LY_NetSalesAmount / t.MonthlySales
into #LY_Data2
from ( 
        select
            [LocationId]        = ly.LocationId,
            [MonthlySales]      = sum(ly.LY_NetSalesAmount)
        from #LY_Data ly
        group by
            ly.LocationId
) t
join #LY_Data ly
    on t.LocationId = ly.LocationId

我已经尝试使用第一个查询作为从子句中的第二查询组中的子查询，但这不能让我在最多选择的语句中选择这些列(多零件标识符无法绑定(。

以及在第二次查询结束时将第一个查询放入加入子句中。

可能有一些我缺少的东西，但是我仍然是SQL的新手，因此，任何帮助或仅在正确方向上的指针将不胜感激！：(

Answer 1

您可以尝试使用公共表表达式(CTE(和窗口函数：

if object_id('tempdb..#LY_Data') is not null drop table #LY_Data
;with
    cte AS
    (
        select  
            [LocationId]            = ri.LocationId,
            [LY_Date]               = convert(date, ri.ReceiptDate),
            [LY_Trans]              = count(distinct ri.SalesReceiptId),
            [LY_SoldQty]            = convert(money, sum(ri.Qty)),
            [LY_RetailAmount]       = convert(money, sum(ri.ExtendedPrice)),
            [LY_NetSalesAmount]     = convert(money, sum(ri.ExtendedAmount))
        from rpt.SalesReceiptItem ri
        join #Location l
            on ri.LocationId = l.Id
        where ri.Ignored = 0
            and ri.LineType = 1 /*Item*/
            and ri.ReceiptDate between @_LYDateFrom and @_LYDateTo
        group by 
            ri.LocationId, 
            ri.ReceiptDate
    )
select
    [LocationId]        = cte.LocationId,
    [LY_Date]           = cte.LY_Date,
    ...
    [Ratio]             = cte.LY_NetSalesAmount / sum(cte.LY_NetSalesAmount) over (partition by cte.LocationId)
into #LY_Data
from cte

sum(cte.LY_NetSalesAmount) over (partition by cte.LocationId)为您提供每个locationId的总和。代码假设此总和始终是非零的。否则，将发生划分为0的错误。

Answer 2

似乎您需要做的就是在第一个查询中计算ratio。

您可以使用相关的子查询来完成此操作。

SELECT
...
convert(money, sum(ri.ExtendedAmount)/(SELECT sum(ri2.ExtendedAmount)
                                       FROM rpt.SalesReceiptItem ri2
                                       WHERE ri2.LocationId=ri.LocationId
                                      )
) AS ratio  --extended amount/total extended amount for this location