mysql，使用group_concat

发布于 2024-11-07 02:34:12 字数 218 浏览 0 评论 0原文

谁能告诉我如何限制 MySQL 中每个组的 GROUP_CONCAT 中的值数量？我正在使用下面的查询，它还会为每个组生成 2 个以上的串联值

  SELECT GROUP_CONCAT(remaining) 
    FROM `busroute` 
GROUP BY bus

有人可以让我知道如何针对我的问题修改上述查询吗？

原文

Could anyone let me know how to limit the number of values in GROUP_CONCAT for each group in MySQL? I am using the below query which also produces more than 2 concatenated values for each group

  SELECT GROUP_CONCAT(remaining) 
    FROM `busroute` 
GROUP BY bus

Could anyone let me know how to modify the above query for my problem?

分享到QQ

分享到微博

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

发布评论

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

夜空下最亮的亮点 2024-11-14 02:34:12

我不知道有什么方法可以限制分组的行数，并且我认为您无法做到这一点。

但是，如果您只想对两行进行分组，则可以手动进行分组和 group_concat，并且一次只对两行进行分组：

SELECT t1.bus, concat(t1.remaining, ',', t2.remaining) 
FROM busroute as t1 
    JOIN busroute as t2 on t1.bus = t2.bus 
WHERE t1.id < t2.id

这里我们刚刚获得了总线路由表的两个副本，然后加入将它们放在总线编号上，然后我们在行中获取一些唯一的列值（可以是任何列，只要它是作为其上设置的唯一属性的列）并消除行与其自身的匹配。我用了“<”而不是“<>”因为我只想匹配同一对非唯一行一次。

I don't know of a way to limit the number of rows that are grouped, and I don't think that you can do it.

But if you are only going to want to have two rows that you want to group, you can do the grouping and group_concat manually and only group two rows at a time:

SELECT t1.bus, concat(t1.remaining, ',', t2.remaining) 
FROM busroute as t1 
    JOIN busroute as t2 on t1.bus = t2.bus 
WHERE t1.id < t2.id

Here we've just gotten two copies of the busroute table and then joined them together on the bus number, then we take some unique column value in the row (which could be any column as long as it's column as the unique attribute set on it) and eliminate matches of a row against its self. I used '<' rather than '<>' since I only want to match the same pair of non-unique rows once.

回复收藏 0 原文

~没有更多了~