php - mysql查询的问题，求帮助

Question

为了方便阐述问题 我简化的弄一张多对多的关系表 一共3个字段
id wid uid
现在希望能查出 在一组wid中 每个（不重复的）wid 下对应的 前5个uid(order by id limit 5)
即最后得出结果为
wid1 = [uid, uid, uid, uid ...]
wid2 = [uid, uid, uid, uid ...]
能不能直接用一条sql取出这样的结果呢？

Answer 1

假定这张表名字暂定为 demo_table
可用下面的语句实现你的需求

SELECT
    wid,
    SUBSTRING_INDEX(GROUP_CONCAT(uid ORDER BY id),',',5) AS 'uids'
FROM demo_table
GROUP BY wid

Answer 2

@deepgoing 的思路没问题，为了解决uid重复和排序问题，可以先做一次distcint的查询，如：

SELECT
    wid,
    SUBSTRING_INDEX(GROUP_CONCAT(uid ORDER BY id),',',5) AS 'uids'
FROM (
  SELECT DISTINCT wid, uid
  FROM demo_table
  ORDER BY wid, uid
)
GROUP BY wid