现在有一个对战信息表, 表里存了玩家ID,胜负情况, 现在需要统计每一个玩家的连胜或者连败情况,有没有什么思路.比方说如我的截图里玩家1两连胜,玩家2三连败,玩家3 三连胜,那么结果就返回下表这样的结果
解决方案 »
- mysql字符集
- CMD每隔5分钟判断mysql是否正常,不正常则net stop mysql,再net start mysql,怎么写,谢谢!
- Mysql存储过程语法问题...
- 如何使用MySQL C API获取UTF-8编码方式的中文字段
- data too long for column的问题
- Mysql安装出现1714!
- 关于两列数据合并
- 何处可下载mysql 4.1 for win 32?
- 各位先生、女士,请看:
- 关于mysql储存过程中句柄问题
- mysql 存储过程的问题
- MySQL 始终连不上 本地也一样 Navicat 连接完全没问题,但是一旦AS JAVA 代码连接就出现如下错误 麻烦大神解救啊!!!!!!!!!!!!!!!
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 |
前面没看完的你的题目
这边重新写一个
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 |
满好玩的问题
你先看一下,有不懂的地方再问我