SQL - 多个条目在 c1 中显示一个实例及其在 c2 中的对应映射

Question

从三个表中，使用内连接，我的结果如下所示:

╔═══════╦═══════╦═══════╗
║ t1.c1 ║ t2.c1 ║ t3.c1 ║
╠═══════╬═══════╬═══════╣
║  1a   ║  2a   ║  3a   ║
║  1a   ║  2b   ║  3b   ║
║  1a   ║  2c   ║  3c   ║
║  1b   ║  2a   ║  3a   ║
║  1b   ║  2b   ║  3b   ║
║  1b   ║  2c   ║  3c   ║
╚═══════╩═══════╩═══════╝

有可能变成下面这样吗？

╔═══════╦═══════╦═══════╗
║ t1.c1 ║ t2.c1 ║ t3.c1 ║
╠═══════╬═══════╬═══════╣
║  1a   ║  2a   ║  3a   ║
║       ║  2b   ║  3b   ║
║       ║  2c   ║  3c   ║
║  1b   ║  2a   ║  3a   ║
║       ║  2b   ║  3b   ║
║       ║  2c   ║  3c   ║
╚═══════╩═══════╩═══════╝

这是我正在使用的代码:

SELECT t1.c1, t2.c1, t3.c1
FROM t1
  INNER JOIN t2
    ON t1.c1=t3.c1
  INNER JOIN t3
    ON t3.c1=t2.c1
WHERE t1.c2 = 'n'
ORDER BY t1.c1 ASC

Answer 1

我想你可以用这个:

SELECT     CASE WHEN ROW_NUMBER() OVER (PARTITION BY t1.c1 ORDER BY t2.c1,t3.c1) = 1 THEN t1.c1 END, 
           t2.c1, t3.c1
FROM       t1
CROSS JOIN t2
INNER JOIN t3
ON         t3.c1 = t2.c1
ORDER BY   t1.c1