mysql - 合并到一个具有相同 id 或属性 SQL Group_concat 的组没有帮助:(
问题描述
如果它们具有相同的 id 或相同的属性,我需要将它们组合在一行中。所以我想我需要使用INNER JOIN
and GROUP_CONCAT
,但我不知道如何。问题是,如果两个用户没有共同的属性,但与相同的第三个用户属于同一个组,那么这三个用户必须合并为一个组。我也没有表格中的group_id
列。
group_id, user_id, group_attributes
1, 1, "red, green, yellow, grey, purple, coffeemaker"
1, 2, "red, green, yellow, grey, purple, coffeemaker"
1, 3, "red, green, yellow, grey, purple, coffeemaker"
1, 4, "red, green, yellow, grey, purple, coffeemaker"
1, 5, "red, green, yellow, grey, purple, coffeemaker"
2, 6, "coffee, milk, croissant"
2, 7, "coffee, milk, croissant"
2, 8, "coffee, milk, croissant"
原始数据可减少您的回答时间。
CREATE TABLE task (
user_id INT(10) NOT NULL,
attribute VARCHAR(50) NULL DEFAULT NULL);
INSERT INTO task (user_id, attribute)
VALUES
(1, 'red'),
(1, 'green'),
(2, 'green'),
(2, 'yellow'),
(3, 'grey'),
(3, 'coffeemaker'),
(4, 'grey'),
(4, 'purple'),
(5, 'purple'),
(5, 'red'),
(6, 'black'),
(7, 'black'),
(7, 'milk'),
(8, 'milk'),
(8, 'croissant');
解决方案
这是一个图行走问题,所以简单JOIN
是不够的。一种方法是获取与给定属性关联的所有属性。以下递归 CTE 执行此操作:
with recursive aa as (
select distinct t1.attribute as at1, t2.attribute as at2
from task t1 join
task t2
on t1.user_id = t2.user_id
),
cte as (
select at1, at2, at1 as found, 1 as lev
from aa
union all
select cte.at1, aa.at2, concat_ws(',', found, aa.at2), lev + 1
from cte join
aa
on cte.at2 = aa.at1
where find_in_set(aa.at2, found) = 0
)
select distinct at1, at2
from cte;
然后,您可以使用相同的递归 CTE 将值组合成一个字符串:
with recursive aa as (
select distinct t1.attribute as at1, t2.attribute as at2
from task t1 join
task t2
on t1.user_id = t2.user_id
),
cte as (
select at1, at2, at1 as found, 1 as lev
from aa
union all
select cte.at1, aa.at2, concat_ws(',', found, aa.at2), lev + 1
from cte join
aa
on cte.at2 = aa.at1
where find_in_set(aa.at2, found) = 0
)
select dense_rank() over (order by pairs.all_attributes) as group_id, t.user_id, pairs.all_attributes
from (select at1, group_concat(at2) as all_attributes
from cte
group by at1
) pairs join
(select user_id, min(attribute) as min_attribute
from task
group by user_id
) t
on t.min_attribute = pairs.at1;
我看不出这段代码有什么问题。但是 db<>fiddle 坚持要为pairs
. 但是,我认为这将适用于您的数据库。这是小提琴。
推荐阅读
- javascript - 在 QuillJS 中将自定义类添加到块引用
- c++ - Esp-32 从 'const char*' 到 'int' 的无效转换 [-fpermissive]
- r - 使用 Shiny 更新数据帧值并在 Shiny 会话结束后在本地环境中访问它
- c - C中gcvt()和_gcvt()的区别
- react-native - 有人可以帮助我,以便当我单击按钮时,计时器会重置吗?谢谢
- javascript - Angular 9:如何防止iOS键盘在将焦点从输入更改为表单内的另一个时消失
- c++ - 如果包含,有条件地将功能从一个标头集成到另一个标头中(这甚至有必要吗?)
- discord.py - 我如何检查某人是否具有特定角色 discord py
- javascript - 如何找到数组中连续零序列的所有第一个索引?
- splunk - splunk 报告中的空白 CSV