mysql查询以查找在列中运行时间最长的 [英] mysql query to find the longest run in a column

问题描述

我在mysql表中有一列，列出了游戏的获胜者. 我正在尝试找到一个查询，该查询将为每个玩家返回最长的获胜记录. 或用php解释查询的方法.

I have a column in a mysql table that lists winners from a game. I'm trying to find a query that will return the longest run of wins for each player. Or a way to interpret a query with php.

在这种情况下 凯特4 第1版 迈克2 哈里1，

In this case it would be Kate 4, Ed 1, Mike 2, Harry 1,

**Winner**
Kate
Kate
Ed
Harry
Ed
Harry
Mike
Mike
Ed
Harry
Kate    
Kate  
Kate
Kate
Ed

提前道歉，因为我对所有这一切都很陌生.

Apologies in advance as I am very new to all of this.

推荐答案

SELECT winner, MAX(winningStreak) FROM (
SELECT
winner,
IF(winner=@prev, @rownum:=@rownum+1, @rownum:=1) AS winningStreak,
@prev:=winner
FROM
yourTable
, (SELECT @prev:=NULL, @rownum:=1) vars
/*ORDER BY whateverDeterminesTheOrderOfTheWinners*/
)sq
GROUP BY winner
ORDER BY winningStreak DESC

您需要另一列来确定获奖者的顺序(就像您列出了获奖者一样)并调整查询中被注释掉的部分.除此之外，该查询还可以，但是用PHP确实会更容易.

You need another column which determines the order of the winners like you have listed them and adjust the outcommented part of the query. Apart from that this query works, but it would really be easier done in PHP.

在此处实时查看.

这篇关于mysql查询以查找在列中运行时间最长的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！