by取第一条 - mysql group by最新




選擇每個用戶最近日期的行 (6)

我有一張表(“lms_attendance”)的用戶登記入住時間和外出時間如下所示:

id  user    time    io (enum)
1   9   1370931202  out
2   9   1370931664  out
3   6   1370932128  out
4   12  1370932128  out
5   12  1370933037  in

我試圖創建一個這張表的視圖,它只輸出每個用戶id的最近記錄,同時給我“in”或“out”值,所以如下所示:

id  user    time    io
2   9   1370931664  out
3   6   1370932128  out
5   12  1370933037  in

我目前非常接近,但我意識到意見不會接受subquerys,這使得它更難。 我得到的最接近的查詢是:

select 
    `lms_attendance`.`id` AS `id`,
    `lms_attendance`.`user` AS `user`,
    max(`lms_attendance`.`time`) AS `time`,
    `lms_attendance`.`io` AS `io` 
from `lms_attendance` 
group by 
    `lms_attendance`.`user`, 
    `lms_attendance`.`io`

但我得到的是:

id  user    time    io
3   6   1370932128  out
1   9   1370931664  out
5   12  1370933037  in
4   12  1370932128  out

哪個接近,但不完美。 我知道最後一組不應該在那裡,但沒有它,它會返回最近的時間,但不會與它的相對IO值相關。

有任何想法嗎? 謝謝!


可能你可以按用戶分組,然後按時間順序排序。 像下面這樣

  SELECT * FROM lms_attendance group by user order by time desc;

基於@TMS的答案,我喜歡它,因為不需要子查詢,但我認為省略'OR'部分就足夠了,並且更易於理解和閱讀。

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL

如果您對空行次數不感興趣,則可以在WHERE子句中對它們進行過濾:

SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL

查詢:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

結果:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

每次都能工作的解決方案:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

沒有必要嘗試重新發明輪子,因為這是greatest-n-per-group常見greatest-n-per-group 。 介紹了非常好的解決方案 。

我更喜歡最簡單的解決方案( 請參閱SQLFiddle,更新Justin's ),而不使用子查詢(因此易於在視圖中使用):

SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
  ON t1.user = t2.user 
        AND (t1.time < t2.time 
         OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL

這也適用於在同一組中有兩個不同記錄具有相同最大值的情況 - 感謝(t1.time = t2.time AND t1.Id < t2.Id)的技巧。 我在這裡所做的只是確保在同一用戶的兩個記錄具有相同時間的情況下,只有一個被選擇。 實際上,如果條件是否是Id或其他內容,那麼實際上並不重要 - 基本上任何保證是唯一的標準都會使這項工作成為現實。


這對我有效:

SELECT user, time FROM 
(
    SELECT user, time FROM lms_attendance --where clause
) AS T 
WHERE (SELECT COUNT(0) FROM table WHERE user = T.user AND time > T.time) = 0
ORDER BY user ASC, time DESC

select b.* from 

    (select 
        `lms_attendance`.`user` AS `user`,
        max(`lms_attendance`.`time`) AS `time`
    from `lms_attendance` 
    group by 
        `lms_attendance`.`user`) a

join

    (select * 
    from `lms_attendance` ) b

on a.user = b.user
and a.time = b.time




greatest-n-per-group