php - mysql query problem, please help

Question

In order to facilitate the explanation of the problem, I simplified a many-to-many relationship table with a total of 3 fields
id wid uid
Now I hope to find out each (non-duplicate) wid in a group of wid The corresponding first 5 uids (order by id limit 5)
That is, the final result is
wid1 = [uid, uid, uid, uid ...]
wid2 = [uid, uid, uid, uid ...]
Can I directly use a sql to get such a result?

黄舟 · Answer

Assume that the name of this table is tentatively demo_table
You can use the following statement to achieve your needs

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

ringa_lee · Answer

@deepgoing’s idea is okay. In order to solve the problem of uid duplication and sorting, you can first do a distcint query, such as:

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