mysql - SQL-按特定字段分组并连接另一个字段

Question

我有一个返回下表的查询:

   P_id   S_id   Time
1  "20"     A    15 
2  "30"     B    50
3  "50"     A    99 
4  "70"     A    60

我想根据“Sid”列对表进行分组，并按“Time”列排序，因此它看起来像这样:

        P_id       S_id   
1  "20","70","50"    A     
2       "30"         B    

通过更改 SQL 查询来实现此目的的最佳方法是什么？

当尝试添加“GROUP BY S_id”时，出现错误:

SELECT list expression references column query which is neither grouped nor aggregated at [2:16]

(意味着它不知道如何对 P_id 的值(所有字符串)进行分组

Answer 1

我想你想要:

select s_id, group_concat(p_id order by time) as p_ids
from t
group by s_id;

如果您想要第一列包含数字，您可以将其添加到:

select (@rn := @rn + 1) as seqnum, s_id, group_concat(p_id order by time) as p_ids
from t cross join
     (select @rn := 0) params
group by s_id;