sql-server - 我的问题还有其他解决方案吗？

Question

对于给定的输入，我需要获得所需的输出

输入

table1                                   
+----------------------+
id      |    coursename   
+-----------------------+      
1            SQL
2            Artificial Intelligence
3            Machine Learning

table2

+---------------------------+
id       |    teachername
+----------------------------+
1             shreya
1             ramya
2             darshan
2             ravi

table3

+----------------------------+
id        |   studentname
+-----------------------------+
1               raj
1              mani
1              chandru
2              prem

输出

+------------------------------------------------------------------------+
id        | coursename            |    teachername  | studentname
+-------------------------------------------------------------------------+
1            SQL                        shreya         raj
1            SQL                        ramya          mani
1            SQL                        null           chandru
2          artificial intelligence       darshan       prem
2          artificial intelligence        ravi          null
3          machine learning               null         null

我以一种方式得到了解决方案

SELECT c.id,
       c.coursename,
       t.teachername,
       s.studentname
FROM  (SELECT id,
              teachername,
              row_number()
                OVER(
                  partition BY id
                  ORDER BY id) AS teach
       FROM   table2)AS t
      FULL JOIN(SELECT id,
                       studentname,
                       row_number()
                         OVER(
                           partition BY id
                           ORDER BY id) AS stud
                FROM   table3) AS s
             ON( t.teach = s.stud
                 AND t.id = s.id )
      RIGHT JOIN (SELECT id,
                         coursename
                  FROM   table1) AS c
              ON( c.id = s.id
                   OR c.id = t.id ) 

标签： sql-server

Answer 1

你想要LEFT JOIN：FULL OUTER JOIN

WITH cte AS (
     SELECT t1.id, t1.coursename, t2.teachername,
            ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY(SELECT 1)) AS RN
     FROM table1 t1 LEFT JOIN
          table2 t2
          ON t2.id = t1.id

), cte1 AS (
     SELECT t1.id, t1.coursename, t3.studentname,
            ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY(SELECT 1)) AS RN
     FROM table1 t1 LEFT JOIN
          table3 t3
          ON t3.id = t1.id
)

SELECT ISNULL(c.id, c1.id) AS id, ISNULL(c.coursename, c1.coursename) AS coursename, c.teachername, c1.studentname
FROM cte c full outer join
     cte1 c1 
     ON c1.id = c.id and c1.RN = c.RN;