mysql随机取出每个分组前2条记录

mysql随机取出每个分组前2条记录

SELECT 
    a.*
FROM
    t_notary_business AS a,
    (SELECT 
        GROUP_CONCAT(id order by rand()) AS ids,item_name
    FROM
        t_notary_business
				WHERE  item_name in ('0116','0101','0102','00')
    GROUP BY item_name) AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 2

性能优化

SELECT 
    a.*
FROM
    t_notary_business AS a,
    (SELECT 
        substring_index(GROUP_CONCAT(d.id order by rand()),',',n) AS ids,item_name
    FROM
        t_notary_business
				WHERE  item_name in ('0116','0101','0102','00')
    GROUP BY item_name) AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND n