Mysql联表查询按照某个字段分组并取每组的前8条数据

SELECT
    t1.*
FROM
    (
        SELECT
            sqrt(
                (10 - ec.l) * (10 - ec.l) + (10 - ec.a) * (10 - ec.a) + (10 - ec.b) * (10 - ec.b)
            ) AS distValue,
            em. NAME material_name,
            ec.*
        FROM
            ea_color AS ec
        JOIN ea_material AS em ON em.id = ec.material_id
        WHERE
            ec.delete_time IS NULL
        AND ec.material_id IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 8)
    ) AS t1
WHERE
    (
        SELECT
            count(*) + 1
        FROM
            (
                SELECT
                    sqrt(
                        (10 - ec.l) * (10 - ec.l) + (10 - ec.a) * (10 - ec.a) + (10 - ec.b) * (10 - ec.b)
                    ) AS distValue,
                    em. NAME material_name,
                    ec.*
                FROM
                    ea_color AS ec
                JOIN ea_material AS em ON em.id = ec.material_id
                WHERE
                    ec.delete_time IS NULL
                AND ec.material_id IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 8)
            ) AS t2
        WHERE
            t1.material_id = t2.material_id
        AND t2.distValue < t1.distValue
    ) <= 8
AND t1.delete_time IS NULL
ORDER BY
    t1.material_id,
    t1.distValue