=================================版权声明=================================
版权声明:原创文章 谢绝转载
请通过右侧公告中的“联系邮箱(wlsandwho@foxmail.com)”联系我
勿用于学术性引用。
勿用于商业出版、商业印刷、商业引用以及其他商业用途。
本文不定期修正完善。
本文链接:http://www.cnblogs.com/wlsandwho/p/5071967.html
耻辱墙:http://www.cnblogs.com/wlsandwho/p/4206472.html
=======================================================================
没啥说的,鄙视那些无视版权随意抓取博文的爬虫小网站站长,圣诞了,祝你们见到上帝。
=======================================================================
“为什么会写这么一些无聊又浅显的小博客呢?”
“因为这些都是我在企鹅群里回答的问题呀。把有代表性的写出来、记录一下,以后再遇到这些问题就不用回答了。多省事呀。”
=======================================================================
上问题。
看起来是年底了,要把不同的表里的数据进行汇总。可以猜测下是出库和入库的两个表,或者其他类似双生的东西。
既然事主敢打包票说不重复而且一一对应,那就按最简单的方法来吧。
事主没有提供主键,下文代码中的主键是我自己加的。
=======================================================================
1 --by WLS 2 --20151224 3 --网络代码有风险复制粘贴须谨慎 4 USE tempdb 5 GO 6 7 IF OBJECT_ID (N't_AbyWLS', N'U') IS NOT NULL 8 DROP TABLE t_AbyWLS; 9 CREATE TABLE t_AbyWLS(id INTEGER PRIMARY KEY,MC NVARCHAR(5),S1 INTEGER) 10 GO 11 12 IF OBJECT_ID (N't_BbyWLS', N'U') IS NOT NULL 13 DROP TABLE t_BbyWLS; 14 CREATE TABLE t_BbyWLS(id INTEGER PRIMARY KEY,MC NVARCHAR(5),S2 INTEGER) 15 GO 16 17 GO 18 19 INSERT INTO t_AbyWLS 20 SELECT 1,'A',23 21 UNION 22 SELECT 2,'B',34 23 UNION 24 SELECT 3,'C',56 25 GO 26 27 INSERT INTO t_BbyWLS 28 SELECT 1,'A',11 29 UNION 30 SELECT 2,'B',12 31 UNION 32 SELECT 3,'C',13 33 UNION 34 SELECT 4,'D',NULL 35 UNION 36 SELECT 5,'E',14 37 GO 38 39 SELECT * FROM t_AbyWLS 40 GO 41 42 SELECT * FROM t_BbyWLS 43 GO 44 45 SELECT a.id AS f1,a.mc AS f2,a.s1 AS f3,b.id AS f4,b.mc AS f5,b.s2 AS f6 FROM t_AbyWLS a FULL JOIN t_BbyWLS b ON a.id=b.id 46 GO 47 48 WITH TempFull 49 AS 50 ( 51 SELECT a.id AS f1,a.mc AS f2,b.id AS f4,b.mc AS f5,a.s1 AS f3,b.s2 AS f6 FROM t_AbyWLS a FULL JOIN t_BbyWLS b ON a.id=b.id 52 ) 53 SELECT CASE WHEN f1 IS NULL THEN f4 ELSE f1 END AS A, 54 CASE WHEN f2 IS NULL THEN f5 ELSE f2 END AS MC, 55 f3 AS S1, 56 f6 AS S2 57 FROM TempFull 58 GO
没啥说的,基本上就是一个Full Outer Join。
=======================================================================
执行效果
附上执行计划
=======================================================================