把下面#a,#b替换成你自己的表就行了 --查询#b表中所有字段与#a表对应字段不匹配的数据 select * from #b where checksum(*) not in(select checksum(*) from #a)--查询#b表中所有字段与#a表对应字段匹配的数据 select * from #b where checksum(*) in(select checksum(*) from #a)
DECLARE @test1 TABLE(t11 INT,t12 VARCHAR(30)) DECLARE @test2 TABLE(t21 INT,t22 VARCHAR(30)) DECLARE @test3 TABLE(t31 INT,t32 VARCHAR(30))INSERT INTO @test1(t11,t12) SELECT 1,'abc' UNION ALL SELECT 1,'bcd' UNION ALL SELECT 2,'abc'INSERT INTO @test2(t21,t22) SELECT 1,'bcd' UNION ALL SELECT 2,'ccc' UNION ALL SELECT 1,'bcd' INSERT INTO @test3(t31,t32) SELECT 1,'test' UNION ALL SELECT 2,'abc' UNION ALL SELECT 3,'ddd'DECLARE @tmp TABLE(tmp1 INT,tmp2 VARCHAR(30))INSERT INTO @tmp(tmp1,tmp2) SELECT * FROM @test1 UNION ALL SELECT * FROM @test2 UNION ALL SELECT * FROM @test3-- 所有数据 SELECT * FROM @tmp-- 不相同的数据 SELECT DISTINCT * FROM @tmp-- 相同的数据 SELECT * FROM @tmp AS t GROUP BY t.tmp1,t.tmp2 HAVING COUNT(*) > 1
3个表的结构都一样! 給一个就可以了,你改一下名字就行了 数据的话我先给个例子吧 比如表一 1879 2010-08-17 18:39:05.703 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 70g 深圳市广利来贸易有限公司 1 15 2 NULL 1880 2010-08-17 09:25:51.160 有友泡凤爪 You You Chicken Feet 100g 广州市硕园贸易有限公司 1 15 0 NULL 1881 2010-08-17 09:25:51.177 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 100g 深圳市广利来贸易有限公司 1 15 0 NULL表二 2879 2010-08-17 18:39:05.703 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 70g 深圳市广利来贸易有限公司 1 15 2 NULL 2880 2010-08-17 09:25:51.160 有友泡凤爪 You You Chicken Feet 100g 广州市硕园贸易有限公司 1 15 0 NULL 2883 2010-08-17 09:25:51.190 有友山椒凤爪 You You Spicy Chicken Feet 180g 广州市硕园贸易有限公司 1 15 0 NULL 表三 3801 2010-08-20 11:00:42.407 完达山幼儿配方奶粉 Wondersun Baby Milk Powder 400g 黑龙江省完达山乳业股份有限公司 78 3 15 0 NULL 3883 2010-08-17 09:25:51.190 有友山椒凤爪 You You Spicy Chicken Feet 180g 广州市硕园贸易有限公司 1 15 0 NULL 统计的结果要吧相同的合并成一条,并且把价格放到后面(priceA,priceB,priceC,如果只有一个priceB,priceC就为""),然后不相同的也要列出来,然后把价格放到后面(priceA,priceB,priceC,如果只有一个priceB,priceC就为"")
select distinct * from ta union select distinct * from tb union select distinct * from tc
checksum(*)可以 select * from a where checksum(*) not in (select checksum(*) from b) and checksum(*) not in (select checksum(*) from c) union all select * from b where checksum(*) not in (select checksum(*) from a) and checksum(*) not in (select checksum(*) from c) union all select * from c where checksum(*) not in (select checksum(*) from a) and checksum(*) not in (select checksum(*) from b)
--查询#b表中所有字段与#a表对应字段不匹配的数据
select * from #b
where checksum(*) not in(select checksum(*) from #a)--查询#b表中所有字段与#a表对应字段匹配的数据
select * from #b
where checksum(*) in(select checksum(*) from #a)
DECLARE @test2 TABLE(t21 INT,t22 VARCHAR(30))
DECLARE @test3 TABLE(t31 INT,t32 VARCHAR(30))INSERT INTO @test1(t11,t12)
SELECT 1,'abc' UNION ALL
SELECT 1,'bcd' UNION ALL
SELECT 2,'abc'INSERT INTO @test2(t21,t22)
SELECT 1,'bcd' UNION ALL
SELECT 2,'ccc' UNION ALL
SELECT 1,'bcd' INSERT INTO @test3(t31,t32)
SELECT 1,'test' UNION ALL
SELECT 2,'abc' UNION ALL
SELECT 3,'ddd'DECLARE @tmp TABLE(tmp1 INT,tmp2 VARCHAR(30))INSERT INTO @tmp(tmp1,tmp2)
SELECT * FROM @test1 UNION ALL
SELECT * FROM @test2 UNION ALL
SELECT * FROM @test3-- 所有数据
SELECT * FROM @tmp-- 不相同的数据
SELECT DISTINCT * FROM @tmp-- 相同的数据
SELECT * FROM @tmp AS t GROUP BY t.tmp1,t.tmp2 HAVING COUNT(*) > 1
数据的话我先给个例子吧
比如表一
1879 2010-08-17 18:39:05.703 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 70g 深圳市广利来贸易有限公司 1 15 2 NULL
1880 2010-08-17 09:25:51.160 有友泡凤爪 You You Chicken Feet 100g 广州市硕园贸易有限公司 1 15 0 NULL
1881 2010-08-17 09:25:51.177 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 100g 深圳市广利来贸易有限公司 1 15 0 NULL表二
2879 2010-08-17 18:39:05.703 辣媳妇山椒凤爪 Spice Xi Fu Chicken Feet 70g 深圳市广利来贸易有限公司 1 15 2 NULL
2880 2010-08-17 09:25:51.160 有友泡凤爪 You You Chicken Feet 100g 广州市硕园贸易有限公司 1 15 0 NULL
2883 2010-08-17 09:25:51.190 有友山椒凤爪 You You Spicy Chicken Feet 180g 广州市硕园贸易有限公司 1 15 0 NULL
表三
3801 2010-08-20 11:00:42.407 完达山幼儿配方奶粉 Wondersun Baby Milk Powder 400g 黑龙江省完达山乳业股份有限公司 78 3 15 0 NULL
3883 2010-08-17 09:25:51.190 有友山椒凤爪 You You Spicy Chicken Feet 180g 广州市硕园贸易有限公司 1 15 0 NULL
统计的结果要吧相同的合并成一条,并且把价格放到后面(priceA,priceB,priceC,如果只有一个priceB,priceC就为""),然后不相同的也要列出来,然后把价格放到后面(priceA,priceB,priceC,如果只有一个priceB,priceC就为"")
union select distinct * from tb
union select distinct * from tc
你是不是要找出A,B,C三个表中每一个字段都相同的记录?
你试过3楼的方法?按那个方法举一反三,找出两个表相同的,不同的记录,要把需要的记录用union all合并一下就行了啊
select * from a where checksum(*) not in (select checksum(*) from b) and checksum(*) not in (select checksum(*) from c)
union all
select * from b where checksum(*) not in (select checksum(*) from a) and checksum(*) not in (select checksum(*) from c)
union all
select * from c where checksum(*) not in (select checksum(*) from a) and checksum(*) not in (select checksum(*) from b)