例：
表一
PN. SN. XN. SJ.
1P1 S1 A1 08.5.3
1P1 S2 A2 08.5.3
1P2 S3 A3 08.5.4
1P4 S4 A4 08.5.5
表二
PN. SN. XN. SJ.
1P1 S1 A1 08.5.3
1P2 S4 A4 08.5.3
1P2 S3 A3 08.5.4
1P5 S5 A5 08.5.5
以PN和SN为关键词做出对比,输出至表三 显示重复和未重复的.
最好给个解释,谢谢
敢不敢发个让我明白的，或者直接复制就能运行的。
SQL内两个表之间的数据对比？
答案:3 悬赏:60
解决时间 2021-03-01 14:35
- 提问者网友：朱砂泪
- 2021-02-28 14:26
最佳答案
- 二级知识专家网友：星星坠落
- 2021-02-28 15:54
重复的：select * from 表一 where PN+SN in (select PN+SN from 表二)
未重复的：select * from 表一 where PN+SN not in (select PN+SN from 表二)
未重复的：select * from 表一 where PN+SN not in (select PN+SN from 表二)
全部回答
- 1楼网友：气场征服一切
- 2021-02-28 17:38
select a.*,'相同数据' as note from 表a a left join 表b b
on a.name=b.name
where a.age=b.age
union all
select *,'不相同数据' as note from 表a c where c.name not in
(
select a.name from 表a a left join 表b b
on a.name=b.name
where a.age=b.age
) or c.age not in
(
select a.age from 表a a left join 表b b
on a.name=b.name
where a.age=b.age
)
union all
select *,'不相同数据' as note from 表b c where c.name not in
(
select a.name from 表a a left join 表b b
on a.name=b.name
where a.age=b.age
) or c.age not in
(
select a.age from 表a a left join 表b b
on a.name=b.name
where a.age=b.age
)
- 2楼网友：如果这是命
- 2021-02-28 16:17
理解为关键字段是否重复的记录分类：
select table1.*,重复 as 标记
INTO TABLE 3 --加上这个，只要表结构没问题，就可以直接在查询分析器执行生成TABLE3
from table1,table2 where table1.pn=table2.pn and table1.sn=table2.sn
union
select isnull(table1.pn,table2.pn) as pn,isnull(table1.sn,table2.sn) as sn,isnull(table1.xn,table2.xn) as xn,*,isnull(table1.sj,table2.sj) as sj,不重复 as 标记 from table1 full join table2 on table1.pn=table2.pn and table1.sn=table2.pn where table1.pn is null or table2.pn is null
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
大家都在看
推荐资讯