sql - SQL 查询最近 7 天日期前 3 名的售罄产品

Question

我想要上周售罄的前 3 件产品。

这是我的 sql 查询。

select   ProductId,sum(Quantity) as quantity,createdOn from

(SELECT  inv.Id,invd.ProductId, invd.Quantity  ,cast (inv.CreatedOn as date) as createdOn FROM Invoice as inv 
INNER JOIN 
InvoiceDetail invd
ON
invd.InvoiceId = inv.id
WHERE inv.CreatedOn  >= DATEADD(day,-11117, GETDATE()) ) as tbl 
group by createdOn , ProductId 
ORDER BY createdOn DESC 

但我没有得到每个日期前 3 名的产品。如果我使用前 3 名，那么它只会提供前 3 名产品，而我想要上周每天前 3 名的产品。

这是我的输出。但我每天只想要 3 条记录。

预期输出：

标签： sqlsql-server

Answer 1

如果我理解正确，您可以使用Row_numberwindows功能来获得quantity每天前 3 名。

createdon每天制作行号，根据列quantity从高到低的顺序。

   ;WITH CTE AS(
    SELECT productid,quantity,createdon,Row_number() over(partition by createdon ORDER BY quantity DESC,productid DESC) as RN
    FROM 
    ( 
        SELECT    invd.productid, 
                  sum(invd.quantity) as quantity, 
                  cast(inv.createdon AS date) AS createdon
        FROM      invoice  AS inv INNER JOIN invoicedetail invd
        ON         invd.invoiceid = inv.id 
        WHERE      inv.createdon >= dateadd(day,-11117, getdate()) 
        GROUP BY cast(inv.createdon AS date), invd.productid
    ) AS tbl 
)
SELECT * 
FROM CTE
WHERE RN <= 3

sqlfiddle

[结果]：

| productID | quantity |  createdon | rn |
|-----------|----------|------------|----|
|        94 |        7 | 2018-07-25 |  1 |
|      1119 |        2 | 2018-07-25 |  2 |
|      1115 |        2 | 2018-07-25 |  3 |
|        94 |        4 | 2018-07-26 |  1 |
|      1117 |        2 | 2018-07-26 |  2 |
|      1114 |        2 | 2018-07-26 |  3 |