如何清除Last of Record Nulls

Question

在加入这些表时，我有三个表

declare @tbl_1 table (pid int, loc varchar(100), avaId int,xpId int,qf varchar(100));
declare @tbl_2 table (soid int,pid int,sid int,name2 varchar(100), nrt2 int);
declare @tbl_3 table (woid int,pid int,wid int,name3 varchar(100), nrt3 int);
insert into @tbl_1 values (1000,'Bangalore',30,9,'ABC');
insert into @tbl_2 values(0,1000,1,'name1',8);
insert into @tbl_2 values(1,1000,8,'name2',5);
insert into @tbl_2 values(2,1000,7,'name3',6);
insert into @tbl_3 values(0,1000,2,'D1',9);
insert into @tbl_3 values(1,1000,1,'D2',2);
insert into @tbl_3 values(2,1000,3,'D3',0);
insert into @tbl_3 values(3,1000,4,'D4',5);

加入查询：

select 
    t.pid, t.loc, t.avaId, t.xpId, t.qf,
    tt.sid, tt.name2, 
    ttt.wid, ttt.name3
from 
    @tbl_1 t
left join 
    @tbl_2 tt on t.pid = tt.pid
right outer join
    @tbl_3 ttt on t.pid = ttt.pid 
               and tt.soid = ttt.woid 

当我使用这个加入时，我得到的结果低于

pid    loc    avaId    xpId    qf    sid    name2    wid    name3
1000    Bangalore    30    9    ABC    1    name1    2    D1
1000    Bangalore    30    9    ABC    8    name2    1    D2
1000    Bangalore    30    9    ABC    7    name3    3    D3
NULL    NULL    NULL    NULL    NULL    NULL    NULL    4    D4

我如何才能得到这个结果：

pid    loc    avaId    xpId    qf    sid    name2    wid    name3
1000    Bangalore    30    9    ABC    1    name1    2    D1
1000    Bangalore    30    9    ABC    8    name2    1    D2
1000    Bangalore    30    9    ABC    7    name3    3    D3
1000    Bangalore    30    9    NULL    NULL    4    D4

感谢

Answer 1

如果我正确理解你的意图，你可以先加入@tbl_2和@tbl_3，然后加入@tbl_1。

WITH cte AS
(
   SELECT COALESCE(tt.pid, ttt.pid) AS pid, tt.sid, tt.name2, 
    ttt.wid, ttt.name3
  FROM @tbl_2 tt
  FULL OUTER JOIN @tbl_3 ttt
    ON tt.pid = ttt.pid
   AND tt.soid = ttt.woid 
)
select t.pid, t.loc, t.avaId, t.xpId, t.qf, c.sid, c.name2, c.wid, c.name3
from @tbl_1 t
JOIN cte c
  ON t.pid = c.pid;

LiveDemo

输出：

╔══════╦═══════════╦════════╦══════╦═════╦══════╦═══════╦═════╦═══════╗
║ pid  ║    loc    ║  avaId ║ xpId ║ qf  ║ sid  ║ name2 ║ wid ║ name3 ║
╠══════╬═══════════╬════════╬══════╬═════╬══════╬═══════╬═════╬═══════╣
║ 1000 ║ Bangalore ║     30 ║    9 ║ ABC ║ 1    ║ name1 ║   2 ║ D1    ║
║ 1000 ║ Bangalore ║     30 ║    9 ║ ABC ║ 8    ║ name2 ║   1 ║ D2    ║
║ 1000 ║ Bangalore ║     30 ║    9 ║ ABC ║ 7    ║ name3 ║   3 ║ D3    ║
║ 1000 ║ Bangalore ║     30 ║    9 ║ ABC ║ NULL ║ NULL  ║   4 ║ D4    ║
╚══════╩═══════════╩════════╩══════╩═════╩══════╩═══════╩═════╩═══════╝