有几个不同点:
1 :A 表有的数据,B 表没有;
2 :A 表有的数据,B 表也有,而且所有字段信息的值都相同;
3 :A 表有的数据,B 表也有,只是有个别字段信息的值不相同;
4 :B 表有的数据,A 表没有;
现在要求是除了2之外,其他情况的数据要拿到。如何写SQL语句的速度最快?
NULL会不会造成影响?写出一个也行呀,分不够我可以在加。谢谢哦!~ ^_^
11 个解决方案
#1
1 , 4 可能用到主键了。
主键是 id.
主键是 id.
#2
(select * from a minus select * from b)
union all
(select * from b minus select * from a)
union all
(select * from b minus select * from a)
#3
1.select * from a
minus
select * from b
minus
select * from b
#4
1.select * from a
minus
select * from b
3.select * from a where not exists(select * from b where a.col1=b.col1 and a.col2=b.col2...)
4.select * from b
minus
select * from a
minus
select * from b
3.select * from a where not exists(select * from b where a.col1=b.col1 and a.col2=b.col2...)
4.select * from b
minus
select * from a
#5
(select * from a minus select * from b)
union
(select * from b minus select * from a)
union
(select * from b minus select * from a)
#6
谢谢大哥们,我现在有个问题。就是3的情况中,如果出现NULL的话怎么办呢?
sasacat(傻傻猫) 给的方案3中,会不会出问题呢 ?
sasacat(傻傻猫) 给的方案3中,会不会出问题呢 ?
#7
select * from(
(
(select * from a minus select * from b)
union
(select * from b minus select * from a)
)
minus
(
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
));
(
(select * from a minus select * from b)
union
(select * from b minus select * from a)
)
minus
(
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
));
#8
谢谢 bzszp(SongZip)大哥,我明白点了。
#9
我糊涂了... @_@
#10
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
不是空吗?
intersect
(select * from b minus select * from a)
不是空吗?
#11
楼上的sql,没意义吧
union部分:(A-B)|(B-A)=(A|B)-(A&B)
而intersect部分:(A-B)&(B-A)=空集
减去空集有什么用啊?!
union部分:(A-B)|(B-A)=(A|B)-(A&B)
而intersect部分:(A-B)&(B-A)=空集
减去空集有什么用啊?!
#1
1 , 4 可能用到主键了。
主键是 id.
主键是 id.
#2
(select * from a minus select * from b)
union all
(select * from b minus select * from a)
union all
(select * from b minus select * from a)
#3
1.select * from a
minus
select * from b
minus
select * from b
#4
1.select * from a
minus
select * from b
3.select * from a where not exists(select * from b where a.col1=b.col1 and a.col2=b.col2...)
4.select * from b
minus
select * from a
minus
select * from b
3.select * from a where not exists(select * from b where a.col1=b.col1 and a.col2=b.col2...)
4.select * from b
minus
select * from a
#5
(select * from a minus select * from b)
union
(select * from b minus select * from a)
union
(select * from b minus select * from a)
#6
谢谢大哥们,我现在有个问题。就是3的情况中,如果出现NULL的话怎么办呢?
sasacat(傻傻猫) 给的方案3中,会不会出问题呢 ?
sasacat(傻傻猫) 给的方案3中,会不会出问题呢 ?
#7
select * from(
(
(select * from a minus select * from b)
union
(select * from b minus select * from a)
)
minus
(
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
));
(
(select * from a minus select * from b)
union
(select * from b minus select * from a)
)
minus
(
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
));
#8
谢谢 bzszp(SongZip)大哥,我明白点了。
#9
我糊涂了... @_@
#10
(select * from a minus select * from b)
intersect
(select * from b minus select * from a)
不是空吗?
intersect
(select * from b minus select * from a)
不是空吗?
#11
楼上的sql,没意义吧
union部分:(A-B)|(B-A)=(A|B)-(A&B)
而intersect部分:(A-B)&(B-A)=空集
减去空集有什么用啊?!
union部分:(A-B)|(B-A)=(A|B)-(A&B)
而intersect部分:(A-B)&(B-A)=空集
减去空集有什么用啊?!