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