合并两列，忽略重复的多行 - MySQL

Question

合并两列，忽略重复的多行 - MySQL

3

也许这是一个愚蠢的问题，但对我来说并不是。老实说，我对MySql查询并不是很熟练，所以我需要一些帮助。

我有一张表：

id |      type_a              |    type_b        |
__________________________________________________
 1 | *color_1*color_2*color_3*| *color_1*        |
 2 | *color_3*                | *color_3*color_2*|
 3 | *color_2*color_3*        | *color_4*        |
 4 | *color_1*color_3*color_4*|                  |
 5 | *color_4*                | *color_5*        |
__________________________________________________

我希望将“type_a”列中的“type_b”列内容移动，忽略重复字段（由*和*分隔，例如：color_1。这种存储方式是通过Joomla组件构建的）。我想要最终结果如下：

    id |      type_a              |  type_b |  
    _________________________________________
     1 | *color_1*color_2*color_3*|         |
     2 | *color_3*color_2*        |         |
     3 | *color_2*color_3*color_4*|         |
     4 | *color_1*color_3*color_4*|         |
     5 | *color_4*color_5*        |         |
    _________________________________________

什么是类似实现的最佳方法？

感谢大家！

- DavidF

3

在MySQL中实现这个功能真的很难，因为它没有任何用于按分隔符拆分字符串的函数。你应该规范化你的架构设计。 - Barmar

2

你可以编写一个存储过程来完成这个任务，但最好是转移到更好的Joomla组件。在列中存储以字符分隔的列表大多数时候会引发问题。 - VMai

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- VMai · Accepted Answer

您可以使用以下语句（不太美观），假设您的表名为example：

UPDATE
    example e1
SET
    e1.type_a = (
        SELECT
            CONCAT('*', GROUP_CONCAT(DISTINCT n1.value ORDER BY n1.value SEPARATOR '*'), '*') as type_a
        FROM ( 
            SELECT
                id, 
            CASE 
                WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1) = '' THEN NULL
                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)
            END value
            FROM example e CROSS JOIN (
                SELECT 
                    a.N + b.N * 10 + 1 AS n
                FROM
                    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                ORDER BY n
            ) n
            WHERE 
                n.n <= 1 + (LENGTH(e.type_a) - LENGTH(REPLACE(e.type_a, '*', '')))
            UNION
            SELECT
                id, 
            CASE 
                WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1) = '' THEN NULL
                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1)
            END value
            FROM example e CROSS JOIN (
                SELECT 
                    a.N + b.N * 10 + 1 AS n
                FROM
                    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                ORDER BY n
            ) n
            WHERE 
                n.n <= 1 + (LENGTH(e.type_b) - LENGTH(REPLACE(e.type_b, '*', '')))
        ) n1
        WHERE 
            n1.id = e1.id
        GROUP BY 
            id
    ),
    e1.type_b = ''
;

SELECT语句的演示

说明

基本上，我改编了方法，使用peterm的方法来完成拆分。我必须先通过TRIM删除外部*。

为了允许空字符串作为列值，我添加了CASE结构来消除这些值。如果你的列有NULL值，你可以用CASE替换它们。

SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)

并且

SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)

这个结构的UNION（不包括ALL关键字）将给我们一个独特颜色值列表，用id分组和GROUP_CONCAT可以得到以*分隔的值列表。最后，我们添加一个前导和尾随*以满足您的要求。

对于更新，您需要修改选择语句，以便它只返回一个带有where子句的列中的一行。

请注意：如peterm所述，这将允许在值列表中最多有100个值。我不认为您会需要更多，但如果需要，那么您必须根据自己的需求调整数字生成。