如何在逗号分隔字符串字段上汇总两个表？

发布于 2025-02-07 20:50:12 字数 1181 浏览 3 评论 0原文

在一个表中，我有一个由逗号分隔的多个值的列。在第二个中，具有ID和名称的列。我需要得到用斜线隔开的名字。

表1：

ID	值	1	value2
1	ABCD	1,2,3	EFGH
2	IJKL	1,4,6	MNOP

Table2：

ID	名称
1	1AB
2	2CD
2 CD 2 CD 3 3 3	3 3
3	3 3
3 3	5ij
6	6kl

SELECT
    a.*,  
    GROUP_CONCAT(b.name,'/') groupedName
FROM
    table1 a
LEFT JOIN
    table2 b ON b.id IN (a.ids)
WHERE
    a.id = 1

结果：

ID	value1	IDS	IDS值2	value2 groupedname
1 ABCD 1 ABCD 1	ABCD	1 ABCD 1,2 ，3	EFGH	1AB/2CD/3EF

原文

In one table I have a column with multiple values separated by a comma. In the second, columns with id and name. I need to get names separated by a slash.

Table1:

id	value1	ids	value2
1	abcd	1,2,3	efgh
2	ijkl	1,4,6	mnop

Table2:

id	name
1	1AB
2	2CD
3	3EF
4	4GH
5	5IJ
6	6KL

SELECT
    a.*,  
    GROUP_CONCAT(b.name,'/') groupedName
FROM
    table1 a
LEFT JOIN
    table2 b ON b.id IN (a.ids)
WHERE
    a.id = 1

Result:

id	value1	ids	value2	groupedName
1	abcd	1,2,3	efgh	1AB/2CD/3EF

分享到QQ

分享到微博

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

发布评论

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

橘香 2025-02-14 20:50:13

假设ID是table1的主要键，请在 on 中使用operator one 等条款：

SELECT t1.*,  
       GROUP_CONCAT(t2.name, '/') groupedName
FROM table1 t1 LEFT JOIN table2 t2 
ON ',' || t1.ids || ',' LIKE '%,' || t2.id || ',%'
GROUP BY t1.id;

请参阅

Assuming that id is the primary key of table1, use the operator LIKE in the ON clause like this:

SELECT t1.*,  
       GROUP_CONCAT(t2.name, '/') groupedName
FROM table1 t1 LEFT JOIN table2 t2 
ON ',' || t1.ids || ',' LIKE '%,' || t2.id || ',%'
GROUP BY t1.id;

See the demo.

回复收藏 0 原文

小苏打饼 2025-02-14 20:50:13

鉴于您的“ table1.ids ”具有varchar（n）类型，您可以使用喜欢功能，以适应JOIN 在两个表之间，然后应用group_concat聚合函数：

SELECT t1.id,
       t1.value1,
       t1.ids,
       t1.value2,
       GROUP_CONCAT(t2.name, '/') AS groupedName 
FROM       table1 t1
INNER JOIN table2 t2
        ON t1.ids LIKE t2.id || ',%'
        OR t1.ids LIKE  '%,' || t2.id || ',%'
        OR t1.ids LIKE           '%,' || t2.id
        OR t1.ids = t2.ids
GROUP BY t1.id,
         t1.value1,
         t1.ids,
         t1.value2

检查demo 此处。

Given that your "Table1.ids" has VARCHAR(n) type, you can use the LIKE function for the matching condition on the JOIN between the two tables, only then apply the GROUP_CONCAT aggregation function:

SELECT t1.id,
       t1.value1,
       t1.ids,
       t1.value2,
       GROUP_CONCAT(t2.name, '/') AS groupedName 
FROM       table1 t1
INNER JOIN table2 t2
        ON t1.ids LIKE t2.id || ',%'
        OR t1.ids LIKE  '%,' || t2.id || ',%'
        OR t1.ids LIKE           '%,' || t2.id
        OR t1.ids = t2.ids
GROUP BY t1.id,
         t1.value1,
         t1.ids,
         t1.value2

Check the demo here.

回复收藏 0 原文

~没有更多了~