如何用SQL生成无重复的星期组合？解决重复结果问题

现有一张存储星期几的表，当其中一天或多天被标记为'X'时，需要生成这些天的所有不重复组合表。我尝试了两段SQL语句，但均出现重复组合，请问可行的算法是什么？

尝试代码1：

INSERT INTO combinaciones (dia1, dia2, dia3)
SELECT t1.day AS day1, t2.day AS day2, t3.day AS day3
FROM (
SELECT 'Lunes' AS day
UNION SELECT 'Miércoles'
UNION SELECT 'Viernes'
) AS t1
CROSS JOIN (
SELECT '' AS day
UNION SELECT 'Miércoles' AS day
UNION SELECT 'Viernes' AS day
) AS t2
CROSS JOIN (
SELECT '' AS day
UNION SELECT '' AS day
UNION SELECT 'Viernes' AS day
) AS t3
WHERE t1.day <> t2.day AND t1.day <> t3.day AND t2.day <> t3.day
and t1.day < t2.day AND t2.day < t3.day;;

尝试代码2：

INSERT INTO combinaciones (dia1, dia2, dia3)
 -- 单日组合
 SELECT day AS dia1, '' AS dia2, '' AS dia3
 FROM (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d1
 UNION
 -- 两日组合
 SELECT d1.day AS day1, d2.day AS day2, '' AS day3
 FROM (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d1,
 (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d2
 WHERE d1.day <> d2.day
 UNION
 -- 三日组合
 SELECT d1.day AS day1, d2.day AS day2, d3.day AS day3
 FROM (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d1,
 (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d2,
 (
 SELECT 'Lunes' AS day
 UNION SELECT 'Miércoles'
 UNION SELECT 'Viernes'
 ) AS d3
 WHERE d1.day <> d2.day AND d1.day <> d3.day AND d2.day <> d3.day and 
 d1.day < d2.day AND d2.day < d3.day;

问题根源 #

1. 代码1的交叉连接逻辑混乱：手动构造的子查询包含重复空值、固定枚举天数，无法灵活适配标记的日期，且空值与实际日期的比较逻辑错误，导致组合遗漏或重复。
2. 代码2的两日组合未加排序约束：比如d1.day='Lunes' AND d2.day='Miércoles'和d1.day='Miércoles' AND d2.day='Lunes'会被视为两条不同记录，但实际是同一个组合，这就是重复的核心来源。

可行算法与优化SQL #

核心思路：先提取所有标记为'X'的日期，分别生成1天、2天、...、N天的组合，每个组合内通过日期排序约束（如d1.dia < d2.dia < d3.dia）从根源避免重复，最后用UNION合并所有结果。

假设原存储标记日期的表名为dias_marcados，字段dia存储星期几（如'Lunes'、'Miércoles'等），优化后的SQL如下：

INSERT INTO combinaciones (dia1, dia2, dia3)
-- 1. 单日组合
SELECT dia AS dia1, '' AS dia2, '' AS dia3
FROM dias_marcados
UNION
-- 2. 两日组合
SELECT d1.dia AS dia1, d2.dia AS dia2, '' AS dia3
FROM dias_marcados d1
JOIN dias_marcados d2 ON d1.dia < d2.dia
UNION
-- 3. 三日组合（仅当标记天数≥3时生效）
SELECT d1.dia AS dia1, d2.dia AS dia2, d3.dia AS dia3
FROM dias_marcados d1
JOIN dias_marcados d2 ON d1.dia < d2.dia
JOIN dias_marcados d3 ON d2.dia < d3.dia;

关键说明 #

• 无需手动枚举日期，直接从原表提取标记数据，适配性更强。
• 排序约束确保每个组合内的日期按固定顺序排列，彻底避免反向重复（如(Lunes, Miércoles)和(Miércoles, Lunes)不会同时出现）。
• 若标记天数超过3天，可按相同逻辑扩展多日组合（如四日组合新增一层JOIN并添加d3.dia < d4.dia约束）。

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