mysql - rand () - 获取每个 id 的 10 个 rand 值的结果

Question

我有如下查询:

select s.name, m.c_id, m.r_stat, m.l_upd, m.desc, c.email 
from manual m 
join selling s 
join contact c 
on m.id=s.id 
and m.id=c.id 
where c.id in (21,22) order by rand() limit 10;

我希望每个 id 21 和 22 都有 10 条记录。

Answer 1

使用变量:

SELECT *
FROM (
    select s.name, m.c_id, m.r_stat, m.l_upd, m.desc, c.email,
           @rn := IF(@id = c.id,
                     @rn + 1,
                     IF(@id := c.id, 1, 1)
                    ) as rn
    from manual m
    join selling s
      on m.id=s.id 
    join contact c
      on m.id=c.id
    cross join ( SELECT @rn := 0, @id := 0) as vars
    where c.id in (21,22) 
    order by c.id, rand()
   ) as t
where rn <= 10

;