提取 GROUP BY 的结果

发布于 2024-11-06 03:13:26 字数 694 浏览 2 评论 0原文

假设我有一个像这样的表：

name        | address
------------+----------------
JOHN SMITH  | 123 FAKE ST
JANE SMITH  | 123 FAKE ST
DAN JOHNSON | 456 WHATEVER RD

现在假设我创建一个视图，在其中进行 GROUP BY 地址，结果如下：

name                   | address         | group_id
-----------------------+-----------------+---------
JOHN SMITH, JANE SMITH | 123 FAKE ST     | 1
DAN JOHNSON            | 456 WHATEVER RD | 2

有没有一种方法，仅使用 SQL 来“扩展”分组的结果是这样的吗？

name        | address         | group_id
------------+-----------------+---------
JOHN SMITH  | 123 FAKE ST     | 1
JANE SMITH  | 123 FAKE ST     | 1
DAN JOHNSON | 456 WHATEVER RD | 2

原文

Let's say I have a table like this:

name        | address
------------+----------------
JOHN SMITH  | 123 FAKE ST
JANE SMITH  | 123 FAKE ST
DAN JOHNSON | 456 WHATEVER RD

Now let's say I create a view where I do GROUP BY address, resulting in something like this:

name                   | address         | group_id
-----------------------+-----------------+---------
JOHN SMITH, JANE SMITH | 123 FAKE ST     | 1
DAN JOHNSON            | 456 WHATEVER RD | 2

Is there a way, using just SQL, to "expand" the results of that grouping, like this?

name        | address         | group_id
------------+-----------------+---------
JOHN SMITH  | 123 FAKE ST     | 1
JANE SMITH  | 123 FAKE ST     | 1
DAN JOHNSON | 456 WHATEVER RD | 2

分享到QQ

分享到微博

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

发布评论

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

本王不退位尔等都是臣 2024-11-13 03:13:26

一种选择是使用 SUBSTRING_INDEX() 函数。

检查此博客文章，其中定义了 SPLIT_STRING() 函数：mysql-split-string-function

回复收藏 0 原文

月隐月明月朦胧 2024-11-13 03:13:26

是的，正如 ypercube 指出的那样，这是可能的，您将需要 SUBSTRING_INDEX 函数。您还需要重新生成行，这很棘手，因为 mysql 不支持递归查询。

您可以采取解决方法，这里有一个假设最多 3 条记录的解决方案只是为了说明：

SELECT SUBSTRING_INDEX(name, ',', 1), address, group_id
FROM aggregated a1
WHERE
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 2), ',', -1), address, group_id
FROM aggregated a2
WHERE name LIKE '%,%'
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 3), ',', -1), address, group_id
FROM aggregated a3
WHERE name LIKE '%,%,%'

上述方法的概念丑陋和缺陷是建议您永远不要将其作为系统中正常设计的过程执行此操作的原因之一（性能也永远不会好）：聚合记录被认为是表示层的一部分，不用于将任何内容写回到数据库中，并且始终能够从未聚合的源中检索记录。

当您偏离上述规则时，您将不得不采取与上述类似的方法（其他支持递归查询的数据库可能会做得更好，但是对于查询将无法使用索引的事实来说，没有灵丹妙药。高效的列名称）。

编辑：
我对group_concat（和类似的）使用了术语“聚合”，但它不够具体。更好的说法是 - 在单个字段中存储多个值（在单个列中重复组）。

Yes, as ypercube noted it is possible and you will need a SUBSTRING_INDEX function. You will also need to regenerate rows which is tricky since mysql does not support recursive queries.

You can do a workaround, here's a solution assuming max 3 records just to illustrate:

SELECT SUBSTRING_INDEX(name, ',', 1), address, group_id
FROM aggregated a1
WHERE
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 2), ',', -1), address, group_id
FROM aggregated a2
WHERE name LIKE '%,%'
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 3), ',', -1), address, group_id
FROM aggregated a3
WHERE name LIKE '%,%,%'

The conceptual ugliness and deficiencies of the above approach is one of the reasons why you are advised never to do this as a normal, designed procedure in the system (performance will also never be good): aggregating records is considered a part of presentation layer and not to be used to write anything back into the database and always be able to retrieve the records from the unaggregated source.

When you deviate from the above rule you will have to take approach similar to the above (other database which support recursive queries might do it nicer, but there is no magic silver bullet for the fact that the query will not be able to use indexes on column name efficiently).

EDIT:
I used the term aggregating for the group_concat (and similar), which is not specific enough. It would be better to say - storing multiple values in single field (repeating group in a single column).

回复收藏 0 原文