按照顺序严格地将两个表连接在一起

Question

如果我有两个表(t1、t2)，每个表都只有一个列。

t1      
letters  
a         
b         
c         

t2
nums
1
2
3

是否有可能以某种方式将它们“合并”，从而产生一个两列的结果集，看起来像这样：

letters nums
a       1
b       2
c       3

解决方案的要求：

• 必须按指定顺序组合每个表的数据，因此能够在连接之前对每个表的数据进行排序。
• 不使用任何函数，如row_number，添加额外的列进行连接。

加分项： - 如果两个表的行数不同，则最终结果集是两个表中行数较大的计数，并且“缺失”的数据为空值。

只想知道在这些限制条件下是否可能实现。

- Josh Diehl

---

创建表t3，选择t1和t2的所有内容。 - Omid CompSCI

选择 * 从 t1, t2 按 desc 排序; - Omid CompSCI

如果使用 row_number()，那会很容易，但是没有这个函数的话，我暂时想不到其它的方法。这些表需要有一个共同点才能进行连接。也许可以通过存储过程来实现，而不是直接使用 SQL。 - Shawn

@OmidCompSCI 你试过那些了吗？ - Shawn

@TimBiegeleisen，我保持版本不可知，因为我很好奇是否有一种纯标准的SQL方法来实现这个。如果有人发布了一个只适用于一个版本的答案，他们可以说明一下。 - Josh Diehl

Answer 1

你想使用 row_number()。但是，SQL 表格表示无序集合，因此你需要一个指定排序的列。

思路如下：

select l.letter, n.number
from (select l.*, row_number() over (order by ?) as seqnum
      from letters l
     ) l join
     (select n.*, row_number() over (order by ?) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

?代表指定排序的列。

如果您想获取两个表中的所有行，请使用full join而不是内部连接。

更新：

row_number()是明显的解决方案，但是假设值是唯一的，则可以使用相关子查询来完成此操作：

select l.letter, n.number
from (select l.*, 
             (select count(*) from letters l2 where l2.letter <= l.letter) as seqnum
      from letters l
     ) l join
     (select n.*, 
             (select count(*) from numbers n2 where n2.num <= n.num) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

我认为不允许使用row_number()这个函数是非常荒谬的，因为它是几乎所有数据库都支持的ISO/ANSI标准函数。

- Gordon Linoff

---

正确，我的问题禁止使用函数。 - Josh Diehl

我正在点赞这个回答，因为它是唯一一个假设两列没有任何共同之处（除了彼此之间的总体顺序）的通用答案。 - Tim Biegeleisen

并使用FULL OUTER JOIN来收集奖金。顺便说一句，这种操作有时被称为Zip，类比于拉链。 - David Browne - Microsoft

它被downvote了，因为问题明确禁止使用row_number函数，而你原始的答案使用了row_number。我提出这个问题是为了探讨特定的情况。你的编辑确实达到了所需的结果，所以我现在很乐意将downvote转换为upvote。 :) - Josh Diehl

@DavidBrowne-Microsoft 我差点把这个标题定为“压缩”，但我觉得这会暗示我想要将每个表的行交错到结果集的一列中。 - Josh Diehl

Answer 2

如果你的SQL版本支持ASCII函数（可以为每个小写字母生成一个ASCII码），那么你可以通过将ASCII码向下移动96并进行连接：

最初的回答：

SELECT
    t1.letters,
    t2.nums
FROM table1 t1
INNER JOIN table2 t2
    ON t2.nums = ASCII(t1.letters) - 96;

最初的回答：

演示

- Tim Biegeleisen

---

聪明，但字母/数字数据仅用于说明。真实数据更加复杂。 - Josh Diehl

@JoshDiehl 如果没有ASCII函数将字母转换为数字，或者没有ROW_NUMBER，你无法真正回答你的问题。 - Tim Biegeleisen