首页新闻找找看学习计划

以编号合并2表,冲突时以A表信息为准,有没有SQL参考一下

0
悬赏园豆:10 [待解决问题]

就是做类似A UNION B的两表UNION操作,当A B两表的编号相同时,取A表信息,求个参考SQL

---江北的主页 ---江北 | 菜鸟二级 | 园豆:208
提问于:2018-11-06 15:33
< >
分享
所有回答(2)
0

select * from A
union all
select * from B where B.ID not in (select ID from A)

fangq | 园豆:417 (菜鸟二级) | 2018-11-07 10:57
0

--已解决
WITH S1 AS(
SELECT , 'A' AS Flag FROM Supply
), S2 AS(
SELECT
, 'B' AS Flag FROM supplyCopy
), S3 AS(
SELECT * FROM S1 UNION ALL
SELECT * FROM S2
)

SELECT p.
FROM (
SELECT

, ROW_NUMBER() OVER (PARTITION BY S3.id ORDER BY S3.Flag ASC) AS rn
FROM S3
) p
WHERE p.rn = 1
-------------------------
WITH S1 AS(
SELECT , 'A' AS Flag FROM Supply
), S2 AS(
SELECT
, 'B' AS Flag FROM supplyCopy
), S3 AS(
SELECT * FROM S1 UNION ALL
SELECT * FROM S2
), S4 AS(
SELECT SUPPLYID, MIN(FLAG) AS minFlag FROM S3 GROUP BY supplyId
)

SELECT
S3.* FROM S3 JOIN S4
ON
S3.supplyId = S4.supplyId AND S3.Flag = S4.minFlag

---江北 | 园豆:208 (菜鸟二级) | 2018-11-07 14:17
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册