表 a
字段1 字段2 字段3
城市1 产品1 1000
城市2 产品2 2000
城市3 产品3 100表b
字段1 字段2 字段3
城市1 产品1 1000
城市2 产品2 2000
比较上述两张表,找出这两个表中差异的记录,该如何写?谢谢大家帮忙.
字段1 字段2 字段3
城市1 产品1 1000
城市2 产品2 2000
城市3 产品3 100表b
字段1 字段2 字段3
城市1 产品1 1000
城市2 产品2 2000
比较上述两张表,找出这两个表中差异的记录,该如何写?谢谢大家帮忙.
union all
select * from tb where checksum(*) not in(select checksum(*) from ta)
select a.* from a where exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select a.* from a , b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3--不同
select a.* from a where not exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select b.* from b where not exists(select 1 from a where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3)
select a.* from a where exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select a.* from a , b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3--不同
select a.* from a where not exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select b.* from b where not exists(select 1 from a where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3)
select a.*,'A不在B中的数据' content from a where not exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
union all
select b.*,'B不在A中的数据' content from b where not exists(select 1 from a where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3)
declare @a table (字段1 nvarchar(10),字段2 nvarchar(10),字段3 int)
insert into @a select '城市1','产品1',1000
union all select '城市2','产品2',2000
union all select '城市3','产品2',2000
declare @b table (字段1 nvarchar(10),字段2 nvarchar(10),字段3 int)
insert into @b select '城市1','产品1',1000
union all select '城市2','产品2',1000
select * from @a
union
select * from @b
字段1 字段2 字段3
---------- ---------- -----------
城市1 产品1 1000
城市2 产品2 1000
城市2 产品2 2000
城市3 产品2 2000(4 行受影响)
DECLARE @ta TABLE
(
col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT
)
DECLARE @tb TABLE
(
col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT
)
INSERT @ta SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000
UNION ALL SELECT '城市3', '产品3', 100
INSERT @tb SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000--两个表中差异的记录
(SELECT * FROM @ta
EXCEPT
SELECT * FROM @tb)
UNION
(SELECT * FROM @tb
EXCEPT
SELECT * FROM @ta)
col1 col2 col3
------------ ---------- -----------
城市3 产品3 100(1 row(s) affected)
我就想得到col1 col2 col3
------------ ---------- -----------
城市3 产品3 100
但是用你写的语句我德不到这个结果,
--try
select * from a
except
select * from b
create table A (city varchar(10),product varchar(10),num int)
create table B (city varchar(10),product varchar(10),num int)
insert A select 'a','hair',1000 union all
select 'b','loneo',2000 union all
select 'c','note',300
insert B select 'a','hair',1000 union all
select 'b','loneo',2000 union all
select 'd','mmm',400select * from A t where not exists (select * from B where t.city = city and product = t.product and num = t.num )
union
select * from B t where not exists (select * from A where t.city = city and product = t.product and num = t.num )drop table A
drop table Bcity product num
c note 300
d mmm 400
--相同
select a.* from a where exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select a.* from a , b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3--不同
select a.* from a where not exists(select 1 from b where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3)
select b.* from b where not exists(select 1 from a where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3)
select * from (select * from a except select * from b ) a
union all select * from (select * from b except select * from a) b
DECLARE @ta TABLE(col1 VARCHAR(100),col2 VARCHAR(100),col3 INT)
DECLARE @tb TABLE(col1 VARCHAR(100),col2 VARCHAR(100),col3 INT)
INSERT @ta SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000
UNION ALL SELECT '城市3', '产品3', 100
INSERT @tb SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000SELECT * FROM @ta EXCEPT SELECT * FROM @tbcol1 col2 col3
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
城市3 产品3 100(1 行受影响)
DECLARE @ta TABLE(col1 VARCHAR(100),col2 VARCHAR(100),col3 INT)
DECLARE @tb TABLE(col1 VARCHAR(100),col2 VARCHAR(100),col3 INT)
INSERT @ta SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000
UNION ALL SELECT '城市3', '产品3', 100
INSERT @tb SELECT '城市1', '产品1', 1000
UNION ALL SELECT '城市2', '产品2', 2000SELECT * FROM @tb INTERSECT SELECT * FROM @ta --INTERSECT将返回两个表中都存在的行col1 col2 col3
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
城市1 产品1 1000
城市2 产品2 2000(2 行受影响)虽然偏离话题,但值得学习