如何在MySQL中生成无重复及逆序重复的组合查询结果

我有一个MySQL查询，能从5列返回所有可能的组合，但需要去除两类重复项：

• 同值重复：比如Car|Car这类元素相同的组合
• 逆序重复：如果已经存在Car|Truck，就移除Truck|Car这类顺序相反的重复组合

试过用DISTINCT但没有效果。

示例数据 #

原查询语句 #

SELECT
    item1, item2, item3, item4, item5
    ,concat_ws('<br>',NULLIF(item1,''),NULLIF(item2,''),NULLIF(item3,''),NULLIF(item4,''),NULLIF(item5,'')) as combination
FROM (
    SELECT column1 AS item1
    FROM sample_data
) AS t1
CROSS JOIN (
    SELECT column2 AS item2
    FROM sample_data
) AS t2
CROSS JOIN (
    SELECT column3 AS item3
    FROM sample_data
) AS t3
CROSS JOIN (
    SELECT column4 AS item4
    FROM sample_data
) AS t4
CROSS JOIN (
    SELECT column5 AS item5
    FROM sample_data
) AS t5
GROUP BY
    item1, item2, item3, item4, item5
    ,concat_ws('<br>',IFNULL(item1,''),IFNULL(item2,''),IFNULL(item3,''),IFNULL(item4,''),IFNULL(item5,''))

补充说明（两列简化示例） #

输入数据：

column1|column2
Car|Car
Truck|Truck
Plane|Plane

期望输出：

Car|Truck
Car|Plane
Truck|Plane

第一步：提取所有非空唯一值 #

先从5列中提取所有非空的唯一值，避免后续处理重复的原始值：

CREATE TEMPORARY TABLE unique_values AS
SELECT DISTINCT value
FROM (
    SELECT column1 AS value FROM sample_data WHERE column1 IS NOT NULL AND column1 != ''
    UNION
    SELECT column2 AS value FROM sample_data WHERE column2 IS NOT NULL AND column2 != ''
    UNION
    SELECT column3 AS value FROM sample_data WHERE column3 IS NOT NULL AND column3 != ''
    UNION
    SELECT column4 AS value FROM sample_data WHERE column4 IS NOT NULL AND column4 != ''
    UNION
    SELECT column5 AS value FROM sample_data WHERE column5 IS NOT NULL AND column5 != ''
) AS all_values;

第二步：生成无重复的两两组合 #

针对你补充示例中的两两组合需求，通过自连接并使用a.value < b.value的条件，同时排除同值和逆序重复：

SELECT
    CONCAT(a.value, '|', b.value) AS combination
FROM unique_values a
JOIN unique_values b ON a.value < b.value
ORDER BY a.value, b.value;

这个查询会直接输出你期望的两两组合结果，既没有同值重复，也不会出现逆序的重复项。

第三步：扩展到多元组合（以3元为例） #

如果需要生成3元、4元甚至5元的无重复无序组合，可以通过多次连接并确保每个后续值都大于前一个值，这样就能避免逆序和元素重复：

-- 3元组合示例
SELECT
    CONCAT(a.value, '|', b.value, '|', c.value) AS combination
FROM unique_values a
JOIN unique_values b ON a.value < b.value
JOIN unique_values c ON b.value < c.value
ORDER BY a.value, b.value, c.value;

同理，4元组合只需再连接一次unique_values d，并添加条件c.value < d.value，以此类推。

替代方案（无需临时表） #

如果不想创建临时表，可以直接将唯一值查询嵌入到连接中：

SELECT
    CONCAT(a.value, '|', b.value) AS combination
FROM (
    SELECT DISTINCT value
    FROM (
        SELECT column1 AS value FROM sample_data WHERE column1 IS NOT NULL AND column1 != ''
        UNION
        SELECT column2 AS value FROM sample_data WHERE column2 IS NOT NULL AND column2 != ''
        UNION
        SELECT column3 AS value FROM sample_data WHERE column3 IS NOT NULL AND column3 != ''
        UNION
        SELECT column4 AS value FROM sample_data WHERE column4 IS NOT NULL AND column4 != ''
        UNION
        SELECT column5 AS value FROM sample_data WHERE column5 IS NOT NULL AND column5 != ''
    ) AS all_values
) a
JOIN (
    SELECT DISTINCT value
    FROM (
        SELECT column1 AS value FROM sample_data WHERE column1 IS NOT NULL AND column1 != ''
        UNION
        SELECT column2 AS value FROM sample_data WHERE column2 IS NOT NULL AND column2 != ''
        UNION
        SELECT column3 AS value FROM sample_data WHERE column3 IS NOT NULL AND column3 != ''
        UNION
        SELECT column4 AS value FROM sample_data WHERE column4 IS NOT NULL AND column4 != ''
        UNION
        SELECT column5 AS value FROM sample_data WHERE column5 IS NOT NULL AND column5 != ''
    ) AS all_values
) b ON a.value < b.value
ORDER BY a.value, b.value;

内容的提问来源于stack exchange，提问作者AmberRossy