mysql - 使用 mysql 查询生成针对特定条件重置的序列号

Question

我在mysql数据库中有一个名为browsekot的表。该表包含从不同商店(餐厅)订购的菜单项以及堂食期间的数量和价格。我需要生成一份报告。

使用以下查询:

rec.Open "select Outlet,ItemName,id,sum(Quantity) as Quantity, sum(Value) as Value,@i:= @i + 1 as result from (SELECT  @i := 0) h , browsekot group by ItemName,Outlet order by @i ", adoconn

给我这个输出:

Sr.No.....Outlet.....Name
1.............Taj...........x
2.............Taj...........y
3.............Mez..........t
4.............Mez..........z

但我想重置每个 socket 的计数 @i 并希望我的输出为:

Sr.No.....Outlet.....Name
1.............Taj...........x
2.............Taj...........y
1.............Mez..........t
2.............Mez..........z

我想重置上述查询本身中的计数，因为稍后我将使用此查询与 SHAPE 命令来在数据报告中显示。如何更改上述查询以重置每个导出的计数？

Answer 1

使用以下 SQL:

select Outlet,ItemName,id, Quantity, `Value`,
       @i:= IF(Outlet = @last_outlet, @i + 1, 1) as result,
       @last_outlet := Outlet
from (SELECT  @i := 0, @last_outlet := NULL) h
JOIN (SELECT Outlet, ItemName, id, SUM(Quantity) AS Quantity, SUM(`Value`) as `Value`
      FROM browsekot
      GROUP BY ItemName,Outlet
      ORDER BY Outlet) i

FIDDLE