sql-server - 不同的sql查询

Question

我的数据库记录如下:-

cartid  orderid   foodid   qty
==============================
92      107       5        1
93      107       5        1
94      107       5        1
95      107       11       1
96      107       5        1
97      108       5        1

可以排成这种形式吗？
假设最大sum(qty)=3

foodid   sum(qty)
=================
5        3
11       1
5        2

Answer 1

您看起来像是在尝试对 cartid 订购的相同 foodid 的连续范围(岛)求和？

;with cart as
(
SELECT 92 AS cartid,107 AS orderid,5 AS foodid, 1 AS qty UNION ALL
SELECT 93,107,5, 1 UNION ALL
SELECT 94,107,5, 1 UNION ALL
SELECT 95,107,11,1 UNION ALL
SELECT 96,107,5, 1 UNION ALL
SELECT 97,108,5, 1 

),
NumberedCart As
(
SELECT cartid,foodid,qty,
ROW_NUMBER() OVER (ORDER BY cartid)- 
       ROW_NUMBER() OVER (PARTITION BY foodid ORDER BY cartid) AS G
 FROM cart
 )

 SELECT foodid, SUM(qty) AS [sum(qty)]
 FROM NumberedCart
 GROUP BY foodid,G
 ORDER BY MIN(cartid)

返回

foodid      sum(qty)
----------- -----------
5           3
11          1
5           2