T-SQL - 使用相关子查询的聚合来聚合所有行

Question

store   item   datekey   onhand   salesunits
--------------------------------------------
001     A      50        65       2
001     A      51        8        4
001     A      52        0        8
--------------------------------------------

我需要完成的任务是：按商店和商品获取大于零减去已售总单位数的最新库存。所以在上面的例子中它将是 8-14=-6。

我使用相关子查询来确定最新的日期键，然后连接回主查询。但显然这样做我会丢失与销售单位求和所需的其他行相关的数据：

这就是我所拥有的，这是错误的：

select s1.Store, s1.Item, s1.OnHand, sum(salesunit)
from sales s1
  join (select top 1 store,item, max(DateKey) as datekey
        from sales
        where isnull(onhand,0) > 0
          and DateKey in (50,51,52)
        group by store, item) s2  on s2.store=s1.store and s2.item=s1.item and s2.datekey=s1.datekey
group by s1.Store, s1.Item, s1.OnHand

感谢您的帮助！

Answer 1

;
WITH totals AS (
  SELECT
    *,
    totalsalesunits = SUM(salesunits) OVER (PARTITION BY store, item),
    rnk = ROW_NUMBER() OVER (PARTITION BY store, item
                                 ORDER BY SIGN(onhand) DESC, datekey DESC)
  FROM sales
)
SELECT
  store,
  item,
  onhand,
  totalsalesunits
FROM totals
WHERE rnk = 1

Answer 2

我会这样做：

首先一些测试数据：

DECLARE @tbl TABLE
        (
            store VARCHAR(4),
            item VARCHAR(2),
            datekey INT,
            onhand INT,
            salesUnits INT
        )

INSERT INTO @tbl
VALUES
    ('001','A',50,65,2),
    ('001','A',51,8,4),
    ('001','A',52,0,8)

查询如下：

;WITH cteTotalSales AS
(
    SELECT
        SUM(tbl.salesUnits) OVER(PARTITION BY 1) AS TotalSalesUnit,
        tbl.store,
        tbl.item,
        ISNULL(tbl.onhand,0) AS onhand,
        tbl.salesUnits,
        tbl.datekey
    FROM
        @tbl AS tbl
), cteLatest AS
(
    SELECT
        RANK() OVER
            (
                PARTITION BY cteTotalSales.store,cteTotalSales.item 
                ORDER BY cteTotalSales.datekey DESC
            ) AS iRank,
        cteTotalSales.store,
        cteTotalSales.item,
        cteTotalSales.onhand,
        cteTotalSales.salesUnits,
        cteTotalSales.datekey
    FROM
        cteTotalSales
    WHERE
        (cteTotalSales.onhand-cteTotalSales.TotalSalesUnit)>0
)
SELECT
    *
FROM
    cteLatest
WHERE
    iRank=1

Answer 3

;with a as
(
select rn = row_number() over (partition by store, item order by case when onhand = 0 then -1 else datekey end desc), 
Store, Item, OnHand, salesunit
from sales
)
select store, item, sum(case when rn = 1 then onhand end)-sum(salesunit) OnHand, sum(salesunit) sumsalesunit from a
group by store, item