首頁>Program>source

是否有更有效的方法来进行以下操作?

select * 
    from foo as a
    where a.id = (select max(id) from foo where uid = a.uid group by uid)
    group by uid;
)

這个答案看起来很相似,但這是這樣做的最好方法-http:// www. barricane.com/2012/02/08/mysql-select-last-matching-row.html了解更多详细資訊。

CREATE TABLE foo ( id INT(10) NOT NULL AUTO_INCREMENT, uid INT(10) NOT NULL, value VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), INDEX `uid` (`uid`) )
最新回復
  • 5月前
    1 #

    尝試查詢-

    SELECT t1.* FROM foo t1
      JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
        ON t1.id = t2.id AND t1.uid = t2.uid;
    

    然後使用EXPLAIN分析查詢。


    SELECT t1.* FROM foo t1
      LEFT JOIN foo t2
        ON t1.id < t2.id AND t1.uid = t2.uid
    WHERE t2.id is NULL;
    

  • 5月前
    2 #

    返迴每个 GROUP BY的最後一行 在MySQL中使用 WHERE 條款:

    SELECT *
    FROM foo
    WHERE id IN (
      SELECT Max(id)
      FROM foo
      WHERE value='XYZ'
      GROUP BY u_id
    )
    LIMIT 0,30
    

  • 5月前
    3 #

    如果表格很大.製作 view 包含所有最後一行ID

    create view lastrecords as (select max(id) from foo where uid = a.uid group by uid)
    

    現在使用此 view加入您的主要查詢 .会更快。

     SELECT t1.* FROM tablename as t1
        JOIN lastrecords as  t2
        ON t1.id = t2.id AND t1.uid = t2.uid;
    

    或者您也可以直接在查詢中与最後一个記錄聯接:

    SELECT t1.* FROM tablename as t1
    JOIN (SELECT uid, MAX(id) id FROM tablename GROUP BY id) as  t2
    ON t1.id = t2.id AND t1.uid = t2.uid;
    

  • 5月前
    4 #

    最簡單的方法:您可以从 selected list that is sorted中選擇

    SELECT * FROM (SELECT * FROM foo order by id DESC) AS footbl
    group by uid
    order by id DESC
    

  • 5月前
    5 #

    這對我有用,谢谢!

    SELECT t1.* FROM foo t1
      JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
        ON t1.id = t2.id AND t1.uid = t2.uid;
    

    我的版本:

    SELECT * FROM messages t1 JOIN (SELECT MAX(id) id FROM messages  where uid = 8279 and actv=1 GROUP BY uid ) t2 ON t1.id = t2.id ORDER BY datex desc LIMIT 0,10;
    

    下面的這段代碼不会返迴我想要的所有行,因為如果max id列為非活動列,那麼即使该組具有活動行,它也会跳過该組。

    select * from messages where uid = 8279 and actv=1 and id In (Select max(id) From messages Group By uid) order by datex desc;
    

相似問題

  • java:如何在另一種方法中使用一種方法的變數?
  • python:如何在windows控製台中顯示UTF-8