PHP/MySQL-获得连胜

Question

我试图展示用户的连胜，但我很难理解我将如何写它，甚至是概念。

以下是一个示例(从最新到最旧(db:

id | win_id| loss_id| player id
1  |  123  | 999    | 123
2  |  123  | 999    | 123
3  |  999  | 123    | 123
4  |  123  | 999    | 123

因此，我想显示的是玩家的连胜er_id:123-因此，它应该为连胜回声"2"，因为用户在id 3 上丢失时出现了中断

Answer 1

当前连胜的可能解决方案：找到玩家获胜的所有比赛，但前提是以后没有失败：

SELECT COUNT(t.id)
FROM table t
WHERE t.player_id = 123
AND t.win_id = 123 /* player 123 won */
AND NOT EXISTS(SELECT * 
FROM table t2
WHERE t2.player_id = 123
AND t2.win_id <> 123 /* player 123 lost */
AND t2.id > t.id /* lost occur after the win we look at */
)   

Answer 2

我认为您的问题是一个缺口和孤岛问题，您希望将"相邻"行分组在一起。

这里有一种方法，使用行号之间的差异来定义组(这需要MySQL 8.0(；然后，我们可以通过连胜进行聚合，通过递减连胜长度进行排序，并只保持最高连胜：

select win_id, count(*) no_wins, min(id) start_id, max(id) end_id
from (
select 
t.*,
row_number() over(partition by player_id order by id) rn1,
row_number() over(partition by player_id, win_id order by id) rn2
from mytable t
where player_id = 123
) t
where win_id = 123
group by win_id, rn1 - rn2
order by no_wins desc
limit 1

DB Fiddle上的演示：

| win_id | no_wins | start_id | end_id |
| ------ | ------- | -------- | ------ |
| 123    | 2       | 1        | 2      |

Answer 3

这将显示用户123的最高winstreak它与mysql5.x 配合使用

我添加了一些数据来显示的作用

CREATE TABLE wintable (
`id` INTEGER,
`win_id` INTEGER,
`loss_id` INTEGER,
`player id` INTEGER
);
INSERT INTO wintable
(`id`, `win_id`, `loss_id`, `player id`)
VALUES
('1', '123', '999', '123'),
('2', '123', '999', '123'),
('3', '123', '777', '123'),
('4', '123', '777', '123'),
('5', '999', '123', '123'),
('6', '123', '999', '123'),
('7', '123', '999', '123'),
('8', '123', '999', '123'),
('9', '123', '777', '123'),
('10', '123', '777', '123'),
('11', '123', '999', '123'),
('12', '123', '999', '123'),
('13', '123', '777', '123'),
('14', '123', '777', '123'),
('15', '999', '123', '123');

✓✓

SELECT 
id,
IF(`loss_id` <> 123,
@num:=@num + 1,
@num:=0) winstreek
FROM
(SELECT 
*
FROM
wintable
WHERE
`player id` = 123
ORDER BY `id`) t1,
(SELECT @num:=0) a

id|winstreek-：|--------：1|12|23|34|45|06|17|28|39 | 410 | 511 | 612 | 713 | 814 | 915|0

<blockquote\

SELECT MAX(winstreek) winstreek
FROM 
(SELECT 
id,
IF(`loss_id` <> 123,
@num:=@num + 1,
@num:=0) winstreek
FROM
(SELECT 
*
FROM
wintable
WHERE
`player id` = 123
ORDER BY `id`) t1,
(SELECT @num:=0) a) b;

>

|winstreek||--------||9 |

db<>小提琴这里