sql-按某个字段分组并连接另一个字段

nr7wwzry 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(483)

我有一个查询返回下表：

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查询的最佳方法是什么？
当尝试添加“groupbys\u id”时，出现以下错误：

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

（这意味着它不知道如何对p\u id（所有字符串）的值进行分组）

2条答案

我想你想要：

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;

select group_concat(P_id,',') from tablename group by S_id ;