怎么统计连续数据的个数

[我这个帅逼]

现在有一个对战信息表, 表里存了玩家ID,胜负情况, 现在需要统计每一个玩家的连胜或者连败情况,有没有什么思路.

比方说如我的截图里玩家1两连胜,玩家2三连败,玩家3 三连胜,那么结果就返回下表这样的结果

[我这个帅逼]

引用 1 楼 ACMAIN_CHM 的回复:

表中至少需要一个能用于排序的字段。

嗯是的，已经按时间排好序了

[ACMAIN_CHM]

表中至少需要一个能用于排序的字段。

[kampoo]

用SQL-DML脚本貌似不易处理，可以考虑存储过程处理 或者 读取到程序中进行处理。

[ACMAIN_CHM]

排序字段是什么？建议完整贴出例子。

[IT-WeiHan]

CREATE TABLE TestTable
    (`ID` int, `胜负` varchar(4))
;
    
INSERT INTO TestTable
    (`ID`, `胜负`)
VALUES
    (1, 'win'),
    (1, 'win'),
    (1, 'loss'),
    (1, 'win'),
    (2, 'win'),
    (2, 'win'),
    (2, 'win'),
    (2, 'loss'),
    (2, 'loss'),
    (2, 'loss'),
    (2, 'loss'),
    (2, 'loss'),
    (2, 'loss'),
    (3, 'win'),
    (3, 'win'),
    (3, 'win')
;

select 
ID,sum(
  case when `胜负` = 'win' then 1
  when `胜负` = 'loss' then -1
  else 0
  end
) recode
from TestTable
group by ID;

| ID | recode |
|----|--------|
|  1 |      2 |
|  2 |     -3 |
|  3 |      3 |
        

[IT-WeiHan]

不好意思 前面没看完的你的题目 这边重新写一个

CREATE TABLE TestTable
    (`ID` int, `胜负` varchar(4))
;
    
INSERT INTO TestTable
    (`ID`, `胜负`)
VALUES
    (1, 'win'),
    (1, 'win'),
    (1, 'loss'),
    (1, 'loss'),
    (1, 'win'),
    (2, 'win'),
    (2, 'win'),
    (2, 'win'),
    (2, 'loss'),
    (2, 'loss'),
    (2, 'loss'),
    (3, 'win'),
    (3, 'win'),
    (3, 'win')
;

CREATE TABLE IF NOT EXISTS temp_table AS (
  select @rownum:=@rownum + 1 as sid
  ,t.* 
  from TestTable t
  ,(SELECT @rownum := 0) r
);

CREATE TABLE IF NOT EXISTS WinningStreak AS (
  select T1.* from temp_table T1
  left join temp_table T2 on T1.id = T2.id
    and T1.sid = T2.sid + 1 and T1.`胜负` = T2.`胜负`
  left join temp_table T3 on T1.id = T3.id
    and T1.sid = T3.sid - 1 and T1.`胜负` = T3.`胜负`
  where T2.sid is not null or T3.sid is not null
  order by T1.sid
);

CREATE TABLE IF NOT EXISTS SerailTable AS (
  select T1.*,T2.sid T2flag, T3.sid T3flag from WinningStreak T1
  left join WinningStreak T2
    on T1.sid = T2.sid + 1 and T1.`胜负` = T2.`胜负`
  left join WinningStreak T3
    on T1.sid = T3.sid - 1 and T1.`胜负` = T3.`胜负`
  order by T1.sid
);

CREATE TABLE IF NOT EXISTS RankTable AS (
  select @rownum:=@rownum + 1 as rank,T.* from (
    select * from  SerailTable
    where T2flag is not null and T3flag is null
    union all
    select * from  SerailTable
    where T2flag is null and T3flag is not null
  ) T,(SELECT @rownum := 0) r
  order by sid
);

select 
T1.id,T1.`胜负`,(T2.sid-T1.sid + 1) recode
from 
(select * from  RankTable where rank %  2 = 1) T1
left join (select * from  RankTable where rank % 2 = 0) T2
on T1.rank + 1 = T2.rank


## 得出id 1 先连赢两场接着连输两场
## ID 2 连赢三场接着连输三场
| id |   胜负 | recode |
|----|------|--------|
|  1 |  win |      2 |
|  1 | loss |      2 |
|  2 |  win |      3 |
|  2 | loss |      3 |
|  3 |  win |      3 |

满好玩的问题 你先看一下，有不懂的地方再问我