合并两个列，忽略重复的多行-MySQL [英] Merge two colums, multiple rows ignoring duplicates - MySQL

问题描述

这也许是一个愚蠢的问题.不适合我:说实话，我对MySql查询的了解不是那么熟练，所以我正在寻找一些帮助.

This is, maybe, a stupid question. Not for me: honestly, I'm not so skilled with MySql queries so I'm looking for a little help.

我有一张桌子:

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组件构建)

I would like to move "type_b" column content in "type_a" column ignoring duplicate fields (delimited by * and *, ex: color_1. This kind of storage is builded by a Joomla component).

我想得到这个最终结果:

I would like to have this final result:

    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*        |         |
    _________________________________________

完成类似任务的最佳方法是什么?

What's the best way to accomplish something similar?

谢谢大家！

推荐答案

您可以使用此语句来完成此操作(不，看起来不太好)，假设表的名称为example:

You could do it with this statement (no, it's not looking nice), assuming the name of your table is 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 = ''
;

演示

Demo of the SELECT statement

说明

基本上，我采用了方法 https://stackoverflow.com/users/1920232/peterm">peterm 完成拆分.我必须先通过TRIM删除外部的*.

Basically I adapted the method of peterm to get the split done. I had to remove the outer * first by TRIM.

为了允许空字符串作为列值，我添加了CASE构造，以消除此类值.如果您的列中包含NULL值，则可以用以下方式替换CASE:

To allow the empty string as column value, I've added the CASE construct, to eliminate such values. If your column has NULL values instead, you could substitute the CASE by

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关键字)将为我们提供不同颜色值的列表，并使用GROUP BY id和GROUP_CONCAT获得*分隔值列表.最后，我们添加一个开头和结尾的*来满足您的要求.

The UNION (without the ALL keyword) of this construct will give us the list of distinct color values and with GROUP BY id and GROUP_CONCAT we'll get the * separated value list. Last we add a leading and a trailing * to match your requirements.

对于更新，您必须修改选择，以使其仅返回一列和一行(带有where子句).

For the update you've got to modificate the select, so that it returns just one column with one row (with the where clause).

注意

如peterm所述，这将允许您的值列表中最多包含100个值.我不认为您会需要更多，但是如果您愿意，那么您就必须根据自己的需要调整数字的生成.

As stated by peterm this will allow up to 100 values in your value list. I don't believe you will need more, but if you will, then you've got to adapt the generating of the numbers up to your needs.

这篇关于合并两个列，忽略重复的多行-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！