我有一个列在mysql表,从一个游戏中列出赢家。我试图找到一个查询,将返回每个球员的最长的胜利运行。或者一种用php解释查询的方法。
在本例中是凯特4,埃德•1迈克2,哈利1,
**Winner**
Kate
Kate
Ed
Harry
Ed
Harry
Mike
Mike
Ed
Harry
Kate
Kate
Kate
Kate
Ed
提前道歉,因为我对这一切都很陌生。
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中确实更容易完成。
使用变量会使查询更简单,但我想只用SQL来解决这个问题。这是我的查询:
SELECT winner, COALESCE(MAX(id2-min_id1), 0)+1
FROM (
SELECT MIN(w1.id) min_id1, w1.winner, w2.id id2
FROM
winners w1 LEFT JOIN winners w2
ON w1.id < w2.id
AND w1.winner = w2.winner
WHERE
NOT EXISTS (SELECT NULL FROM winners w3
WHERE w3.winner != w1.winner
AND w3.id > w1.id AND w3.id < w2.id)
GROUP BY
w2.id, w1.winner
) s
GROUP BY
winner
请参阅此处。我之所以使用ID,是因为没有ID或时间戳,就无法确定SQL查询返回的行顺序。这里假设ID没有空格。
好了,轮到我了…这里假设id是auto_increment,没有间隔…
SELECT winner
, MAX(streak) longest_streak
FROM
( SELECT a.*
, MIN( c.id ) - a.id + 1 streak
FROM results a
LEFT
JOIN results b
ON a.id = b.id + 1
AND b.winner = a.winner
LEFT
JOIN results c
ON a.id <= c.id
AND c.winner = a.winner
LEFT
JOIN results d
ON c.id = d.id - 1
AND d.winner = a.winner
WHERE b.id IS NULL
AND c.id IS NOT NULL
AND d.id IS NULL
GROUP
BY a.id
) x
GROUP
BY winner
ORDER
BY longest_streak DESC;