56,878
社区成员




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 |
满好玩的问题
你先看一下,有不懂的地方再问我
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 |