根据 SQL 中的表顺序选择数据

Question

我的数据库(sql server(中有3个表。第一个表是我的主表，我正试图从剩下的两个表中更新/插入新的记录

基于3个标准。

1. 我想维护tbl1中的所有记录，tbl1是我的主表。

2. 如果我们在两个以上的表(即tbl2_2020和tbl3_2019(中遇到相同id1、id2、id3、id4的重复记录，那么我想从最近的一个表中提取该记录(最近的记录是根据tbl名称中的年份选择的(&最后一张表中不应该有重复的记录。

3. tbl1中不存在但tbl2或tbl3中存在的唯一记录应选择

4. 在最后的tbl中有一个单独的列，描述记录的来源

---my attempt so far (didnt work though :-) )
select COALESCE(t1.id1, t2.id1, t3.id1) as id1,
COALESCE(t1.id2, t2.id2, t3.id2) as id2,
.....so on for all the fields
from tbl1_2021 t1 full join tbl2_2020 t2 ON
t1.id1= t2.id and t1.id2=t2.id2 and t1.id3=t2.id3 and t1.id4=t2.id4
full join
tbl3_2019 t3 ON
t2.id1= t3.id1 and t2.id2=t3.id2 and t2.id3=t3.id3 and t2.id4=t3.id4

tb1_2021：(主要tbl(

| id1 | id2 | id3 | id4 | name | age | website | nation | email | address |
| 1   | 11  | 111 | 1111| raj  | 20  | .com    | india  |       | india addr |
| 2   | 22  | 222 | 2222| roger| 21  | .usa    | usa    |[e-mail]| usa |

tbl2_2020：

| id1 | id2 | id3 | id4 | website | name | age | nation | email | zipcode | parentname | device |
| 1   | 11  | 111 | 1111| .com    | raj  | 20 | india   |       |         |            |        |
| 3   | 33  | 333 | 3333| .ca     | amy  | 24 | uk      | amy   |  zip333 |  sage      | ipad   |

2019年3月3日：

| id1 | id2 | id3 | id4 | name | age | website | nation | email | address |
| 3   | 33  | 333 | 3333|      |     |         |        |       |         |
| 2   | 22  | 222 | 2222| roger| 21  | .usa    | usa    |[e-mail]|        |
| 4   | 44  | 444 | 4444| nick| 28   |  .irl   | uk     |[e-mail]| nickadr|

最终表格(输出(

| id1 | id2 | id3 | id4 | name | age | website | nation | email | address | source
| 1   | 11  | 111 | 1111| raj  | 20  | .com    | india  |       |india addr| tbl_2021
| 2   | 22  | 222 | 2222| roger| 21  | .usa    | usa    |[e-mail]| usa      | tbl_2021
| 3   | 33  | 333 | 3333| amy  | 24  | .ca     | uk     |       |          | tbl_2020
| 4   | 44  | 444 | 4444| nick | 28  | .irl    | uk     |[e-mail]| nickaddr | tbl_2019

Answer 1

您可以尝试以下查询

SELECT id1
,id2
,id3
,id4
,name
,age
,website
,nation
,email
,address
,source
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id1, id2, id3, id4 ORDER BY a.year DESC) [rowId]
FROM (
SELECT id1, id2, id3, id4, name, age, website, nation, email, address, 'tbl_2021' source, 2021 [year]
FROM tbl1_2021

UNION ALL

SELECT id1, id2, id3, id4, name, age, website, nation, email, address, 'tbl_2020' source, 2020 [year]
FROM tbl2_2020

UNION ALL

SELECT id1, id2, id3, id4, name, age, website, nation, email, address, 'tbl_2019' source, 2019 [year]
FROM tbl3_2019
) a
) b
WHERE b.rowId = 1
ORDER BY b.id1