mysql语句分组，同时分组记录为某字段最大值

GroupByAndMax.sql

--按照user_id分组,分组获得的记录为round_num最大的记录：
-- 方法一：
SELECT
  a.activity_id,
  a.activity_round_id,
  a.round_num,
  a.user_id
FROM
    jmf_activity_order AS a
WHERE
    a.round_num = (
        SELECT
            max(b.round_num)
        FROM
            jmf_activity_order AS b
        WHERE
      a.user_id = b.user_id
    )
GROUP BY a.user_id
-- 方法二：
-- 不同的mysql版本老一点的可以不用limit，新一点的可以用limit不然不会排序，有缺陷
SELECT
  a.activity_id,
  a.activity_round_id,
  a.round_num,
  a.user_id
FROM
    (
        SELECT
             *
        FROM
            jmf_activity_order AS b
        ORDER BY
            b.round_num DESC
    LIMIT 100000
    ) AS a
GROUP BY a.user_id
-- 方法三：
SELECT
  a.activity_id,
  a.activity_round_id,
  a.round_num,
  a.user_id
FROM
    jmf_activity_order AS a
WHERE
    NOT EXISTS (
        SELECT
            b.*
        FROM
            jmf_activity_order AS b
        WHERE
            a.user_id = b.user_id
        AND b.round_num > a.round_num
    )
GROUP BY a.user_id
-- 方法四：
SELECT
  a.activity_id,
  a.activity_round_id,
  a.round_num,
  a.user_id
FROM
    jmf_activity_order AS a
WHERE
    EXISTS (
        SELECT
            b.*
        FROM
            jmf_activity_order AS b
        WHERE
            a.user_id = b.user_id
        AND b.round_num > a.round_num
        HAVING
            count(*) = 0
    )
GROUP BY a.user_id