mysql - 选择总和，直到找到特定行

Question

CREATE TABLE Coins (Badge VARCHAr(10), Cointype INT,  Number INT);

INSERT INTO Coins (Badge,Cointype, Number) VALUES
('' , 1, 1),
('' , 5, 2),
('' , 20, 3),
('' , 1, 4),
('' , 5, 5),
('' , 5, 6),
('' , 5, 7),
('RESET', 0 , 0),
('' , 1, 8),
('' , 10, 9),
('RESET', 0 , 0),
('' , 5, 10),
('' , 20, 11);

嗨，对于这个表，我希望在第一行上方有以下总和 ('RESET',0,0)

select 
SUM(case when Cointype=1 Then Number else 0 END) as SUM1,
SUM(case when Cointype=5 Then Number else 0 END) as SUM5,
SUM(case when Cointype=20 Then Number else 0 END) as SUM20
from Coins 

结果必须是这样的:

SUM1    SUM5    SUM20
5       20      3 

这在 MySql 中可能吗？

实际情况中，还有一个ID和时间戳字段。顶部记录是具有最高 ID 和时间戳的最新记录。因此必须汇总最近的记录，直到 RESET 行。 (如果我们需要排序，可以使用 ID 或时间戳字段)

那么表格是这样的:

CREATE TABLE Coins (ID INT, Badge VARCHAr(10), Cointype INT,  Number INT);

INSERT INTO Coins (ID, Badge,Cointype, Number) VALUES
(13,'' , 1, 1),
(12,'' , 5, 2),
(11,'' , 20, 3),
(10,'' , 1, 4),
(9,'' , 5, 5),
(8,'' , 5, 6),
(7,'' , 5, 7),
(6,'RESET', 0 , 0),
(5,'' , 1, 8),
(4,'' , 10, 9),
(3,'RESET', 0 , 0),
(2,'' , 5, 10),
(1,'' , 20, 11);

Answer 1

如果您有自动数字列:

<强> SQL DEMO

select 
    SUM(case when Cointype=1 Then Number else 0 END) as SUM1,
    SUM(case when Cointype=5 Then Number else 0 END) as SUM5,
    SUM(case when Cointype=20 Then Number else 0 END) as SUM20
from Coins 
WHERE ID < ( SELECT MIN(ID)
             FROM Coins
             WHERE Badge = 'RESET' )

输出

| SUM1 | SUM5 | SUM20 |
|------|------|-------|
|    5 |   20 |     3 |

编辑:

更改示例后，行的顺序看起来像是相反的。那么你需要 MAX() 而不是 MIN()

select 
    SUM(case when Cointype=1 Then Number else 0 END) as SUM1,
    SUM(case when Cointype=5 Then Number else 0 END) as SUM5,
    SUM(case when Cointype=20 Then Number else 0 END) as SUM20
from Coins 
WHERE ID > ( SELECT MAX(ID)
             FROM Coins
             WHERE Badge = 'RESET' )