MySQL:组值

Question

假设我有一个表Connections，格式为[logID, user, time]

一个例子是:

| logID |  user |  time 
|-----------------------
|  91   | terry | 12:55:00  <--- Last by user
|  90   | terry | 12:54:26
|  89   | nami  | 12:52:12  <--- Last by user
|  88   | terry | 12:50:50  <--- Last by user
|  87   | terry | 12:49:21
|  86   | terry | 12:48:16
|  85   | terry | 12:46:07
|  84   | nami  | 12:31:22  <--- Last by user
|  83   | nami  | 12:30:30
|  82   | nami  | 12:29:26 
|  81   | terry | 12:27:12  <--- Last by user

所需的查询应该对用户列进行分组每当它发生变化并选择该用户的最后一个时间戳:

| logID |  user |  time 
|-----------------------
|  91   | terry | 12:55:00  <--- Last by user
|  89   | nami  | 12:52:12  <--- Last by user
|  88   | terry | 12:50:50  <--- Last by user
|  84   | nami  | 12:31:22  <--- Last by user
|  81   | terry | 12:27:12  <--- Last by user

我一直在研究 GROUP BY，但没有任何进展......

Answer 1

一种方法是使用用户变量:

SELECT   logID, @lastuser:=user AS user, time
FROM     mytable, (SELECT @lastuser:=NULL) init
HAVING   NOT @lastuser<=>user
ORDER BY time DESC

查看 sqlfiddle .