如何从CROSS JOIN生成的5列表中筛选无序唯一记录

解决方案 #

你要的是5个不重复数值的组合（无序，同组数值仅保留一条），而非排列（有序，同值不同序算多条），直接修改WHERE条件即可实现，是性能最优的方案：

SELECT 
  t.col1 AS col1,
  t1.col1 AS col2,
  t2.col1 AS col3,
  t3.col1 AS col4,
  t4.col1 AS col5
FROM table1 t 
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
  t.col1 < t1.col1
  AND t1.col1 < t2.col1
  AND t2.col1 < t3.col1
  AND t3.col1 < t4.col1

原理说明 #

该条件强制后一列的数值严格大于前一列，天然保证了5个值互不重复，且每个数值组合只会按从小到大的顺序出现一次，正好匹配你要保留1 2 3 4 5、过滤所有其他排列的需求。
相比你原来写的全不等于判断，这个条件的计算量小很多，原表有数百行数据的场景下，查询速度提升非常明显。

备选方案（仅适用于已生成结果集需要去重的场景） #

如果你已经生成了全量排列的结果集需要清洗，可以用排序去重的方式实现（性能远低于上述方案，仅做参考），以MySQL 8.0+为例：

WITH all_permutations AS (
  -- 此处替换为你原来的查询语句
  Select t.col1 c1, t1.col1 c2, t2.col1 c3, t3.col1 c4, t4.col1 c5
  from table1 t 
  CROSS JOIN table1 t1
  CROSS JOIN table1 t2
  CROSS JOIN table1 t3
  CROSS JOIN table1 t4
  WHERE
      t.col1 != t1.col1 and t.col1 != t2.col1 and t.col1 != t3.col1 and t.col1 != t4.col1 
      and t1.col1 != t2.col1 and t1.col1 != t3.col1 and t1.col1 != t4.col1 
      and t2.col1 != t3.col1 and t2.col1 != t4.col1
      and t3.col1 != t4.col1
)
SELECT DISTINCT
  LEAST(c1,c2,c3,c4,c5) AS col1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT c ORDER BY c SEPARATOR ','), ',', 2), ',', -1) AS col2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT c ORDER BY c SEPARATOR ','), ',', 3), ',', -1) AS col3,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT c ORDER BY c SEPARATOR ','), ',', 4), ',', -1) AS col4,
  GREATEST(c1,c2,c3,c4,c5) AS col5
FROM all_permutations,
JSON_TABLE(
  JSON_ARRAY(c1,c2,c3,c4,c5),
  '$[*]' COLUMNS(c INT PATH '$')
) AS vals
GROUP BY c1,c2,c3,c4,c5

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