MySQL如何拼接两个无共同列的表且不产生交叉连接、不丢失任意行

实现逻辑 #

• 两张表无直接关联字段的前提下，先分别为两张表按原始顺序生成连续的行序号
• 基于生成的行序号做全外连接，即可实现按行并排拼接，既不会生成交叉连接的冗余数据，也不会丢失任意一张表的行数据

支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/Oracle等）实现SQL #

SELECT 
  t1.i, t1.foo,
  d.User, d.start_date, d.end_date
FROM (
  -- 为t1按i排序生成行号，和原有顺序一致
  SELECT *, ROW_NUMBER() OVER (ORDER BY i) AS rn FROM t1
) t1
FULL OUTER JOIN (
  -- 为dates按User排序生成行号，和原有顺序一致
  SELECT *, ROW_NUMBER() OVER (ORDER BY User) AS rn FROM dates
) d ON t1.rn = d.rn
ORDER BY COALESCE(t1.rn, d.rn);

MySQL 5.x 低版本无窗口函数实现SQL #

SELECT 
  t1.i, t1.foo,
  d.User, d.start_date, d.end_date
FROM (
  SELECT *, @rn1 := @rn1 + 1 AS rn 
  FROM t1, (SELECT @rn1 := 0) init
  ORDER BY i
) t1
LEFT JOIN (
  SELECT *, @rn2 := @rn2 + 1 AS rn 
  FROM dates, (SELECT @rn2 := 0) init
  ORDER BY User
) d ON t1.rn = d.rn
UNION ALL
SELECT 
  t1.i, t1.foo,
  d.User, d.start_date, d.end_date
FROM (
  SELECT *, @rn3 := @rn3 + 1 AS rn 
  FROM t1, (SELECT @rn3 := 0) init
  ORDER BY i
) t1
RIGHT JOIN (
  SELECT *, @rn4 := @rn4 + 1 AS rn 
  FROM dates, (SELECT @rn4 := 0) init
  ORDER BY User
) d ON t1.rn = d.rn
WHERE t1.rn IS NULL
ORDER BY COALESCE(t1.rn, d.rn);

执行以上SQL即可得到你预期的拼接结果，行顺序完全对应两张表的原始排列顺序。

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