MySQL 分组后取前几条

• 利用GROUP_CONCAT和SUBSTRING_INDEX实现，能很好的利用索引，适合大数据。

SELECT 
    *
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ',',
                        1) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC;

• 如果取多条，SUBSTRING_INDEX(x,x,8)，通过3第三个参数来限制取会分组后的条数。外层在通过GROUP_CONCAT来返回所有主键。

由于在子查询中，IN (GROUP_CONCAT())会放弃索引使用，如下：

SELECT * from yourtable where id in(
SELECT 
    GROUP_CONCAT(ID)
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ',',
                        8) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC
) 

所以请根据EXPLAIN计划，来选择是否用2条语句来查询。

如果还是要一条语句，好吧。

SELECT * from yourtable where FIND_IN_SET(id,(
SELECT 
    GROUP_CONCAT(ID) id
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ',',
                        8) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC
))