SQL：消除 GROUP_CONCAT 中的重复链

发布于 2024-12-07 11:42:32 字数 1032 浏览 0 评论 0原文

我正在运行查询来分析某些用户的 Group_member_ID 的变化。我想确定的是 group_member_ids 中的关键变化（例如 2011 年 1 月 8 日的 Group1，2011 年 5 月 8 日的 Group5）。

我想出了这个命令：

select id, 
CAST(group_concat(concat('[',group_member_id,'-',from_unixtime(obs_time),']') order by obs_time) as CHAR(10000) CHARACTER SET utf8) 
from Table1 
where id=1

结果：

imei 组/日期

1[178-2011-06-13 18:58:31],[0-2011-06-13 19:20:56],[0-2011-06-17 17:21:57],[0-2011-06-19 16:53:29],[0-2011-06-22 16:41:11],[178-2011-09-30 16:43:11],[179-2011-10-01 18:43:11]

如何消除组/日期 [0-2011-06-17 17:21:57],[0-2011-06-19 16:53:29],[0-2011- 06-22 16:41:11] 来自此查询，因为我已经确定了 group_member_id=0 的第一条记录，其他记录对我来说并不重要...... 换句话说，我希望我的最终结果如下：

imei 组/日期

1[178-2011-06-13 18:58:31],[0-2011-06-13 19:20:56],[178-2011-09-30 16:43:11],[179-2011-10-01 18:43:11]

我被卡住了。我正在考虑在我的 group_concat 中使用 LIMIT 但显然这是不可能的。或者是吗？

感谢您的回答。

原文

I'm running a query to analyse variations in Group_member_ID for some users.
What I would like to identify is the key variations in group_member_ids (eg Group1 on 01/08/2011, Group5 on 05/08/2011).

I came up with this command:

select id, 
CAST(group_concat(concat('[',group_member_id,'-',from_unixtime(obs_time),']') order by obs_time) as CHAR(10000) CHARACTER SET utf8) 
from Table1 
where id=1

RESULT:

imei group/date

1[178-2011-06-13 18:58:31],[0-2011-06-13 19:20:56],[0-2011-06-17 17:21:57],[0-2011-06-19 16:53:29],[0-2011-06-22 16:41:11],[178-2011-09-30 16:43:11],[179-2011-10-01 18:43:11]

How can I eliminate the Group/date [0-2011-06-17 17:21:57],[0-2011-06-19 16:53:29],[0-2011-06-22 16:41:11] from this query as I already identified the first record for group_member_id=0 and the others do not matter for me...
In other words, I would like my final result to look like:

imei group/date

1[178-2011-06-13 18:58:31],[0-2011-06-13 19:20:56],[178-2011-09-30 16:43:11],[179-2011-10-01 18:43:11]

I'm stuck. I was thinking of using LIMIT in my group_concat but apparently it's not possible. Or is it?

Thanks for your answers.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星軌x 2024-12-14 11:42:32

尝试使用 GROUP BY

  SELECT id, CAST( GROUP_CONCAT ( 
                                 CONCAT('[',group_member_id,'-',from_unixtime(obs_time),']') 
                                 ORDER BY obs_time ) as CHAR(10000) CHARACTER SET utf8)
                           AS group_set  
  FROM table1 
  WHERE id=1
  GROUP BY group_member_id

TRY with GROUP BY

  SELECT id, CAST( GROUP_CONCAT ( 
                                 CONCAT('[',group_member_id,'-',from_unixtime(obs_time),']') 
                                 ORDER BY obs_time ) as CHAR(10000) CHARACTER SET utf8)
                           AS group_set  
  FROM table1 
  WHERE id=1
  GROUP BY group_member_id

回复收藏 0 原文

~没有更多了~