現在有一個對戰資訊表, 表里存了玩家ID,勝負情況, 現在需要統計每一個玩家的連勝或者連敗情況,有沒有什么思路.
比方說如我的截圖里玩家1兩連勝,玩家2三連敗,玩家3 三連勝,那么結果就回傳下表這樣的結果
uj5u.com熱心網友回復：
表中至少需要一個能用于排序的欄位。uj5u.com熱心網友回復：
嗯是的，已經按時間排好序了
uj5u.com熱心網友回復：
用SQL-DML腳本貌似不易處理，可以考慮存盤程序處理 或者 讀取到程式中進行處理。uj5u.com熱心網友回復：
排序欄位是什么？建議完整貼出例子。uj5u.com熱心網友回復：
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 |
uj5u.com熱心網友回復：
不好意思前面沒看完的你的題目
這邊重新寫一個
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 |
滿好玩的問題
你先看一下，有不懂的地方再問我
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/88021.html
標籤：MySQL