一个多表查询引出的问题(1)

        最近一段时间做公司内部的一个业务员业绩管理站点，其中在做报表查询的时候遇到了一个有意思的问题，着实为难了我一下。因为一些不方便的原因，同时也为了便于描述，下面我把这个报表查询简化一下，但是本质上是一样的。         首先是说说表结构吧，一共两张表，暂命名为表T1和T2吧，T1有A，B，C，D四个字段，T2有A，B，C，E四个字段。T2表的A，B两列的联合的值是T1表A，B两列联合的值的子集
         我想要的结果是返回一个查询暂命名为T吧，T的结构是 A       B          C           D         E，但是T中要包括T1和T2两个表中的所有C的值。看到这里有些博友可能马上就想到了这很简单嘛，一个表连接或右连接就搞定了（的确我一开始也是这样认为的），但是请注意，我要的结果是要所有C都出现在查询结果中。
         为了便于说明问题我们先对两个表添加一些测试数据：
         T1:   A         B           C          D
                a1        b1          c1          d1
                a2        b2          c2          d2 
                a3        b3         c3           d3
                a4        b4         c4           d4

         T2:   A         B           C          E
                  a1       b1        c1         e1
                  a2       b2        c2         e2
                  a3       b3        c5         e3 
                 a4       b4        c6         e4
针对于上面两个表的实际数据，也就是结果要像下面这样，也就是要A，B，C三列是联合唯一的
        T:       A         B           C          D         E
                  a1       b1        c1         d1       e1
                  a2       b2        c2         d2       e2
                  a3       b3        c3         d3     null
                  a4       b4        c4         d4     null 
                  a3       b3        c5         null     e3
                  a4       b4        c6         null     e4
       咋看之下，两个表的数据合并到一个结果中来，好像还是典型的表连接查询嘛，可是针对我们想要的这个结果来说该用什么想的连接方式呢，inner join？条件呢 T1.A = T2.A and T1.B = T2.B这两个我想应该没有问题的，那C呢，T1.C = T2.C ？好像不对，T1.C is null or T2.C is null ? 更不对了，这样结果行数只会是小于等于T1和T2中的C的不唯一的个数。再来看看left join吧。我们知道，一般情况下，left join的结果的行数只会是等于参加连接的左边表的行数，现在结果的行数是6行，明显不等于T1表的4行。看来左连接的方式也是不行的。那行右连接呢？右连接从本质上来说和左连接的方式是一样，都是其对应的左连接对称的方式，就像a+b=b+a一样，个人感觉主要是为了某些时候写SQL的方便，比如说连续的做连接查询。一般来说左连接能做到的事情，右连接都可以做到，左连接做不到的事情，右连接也同样做不到（扯远了，呵呵）。还有一种方式cross join，也叫笛卡尔积，交叉连接，不带任何条件的连接，结果行数永远等于T1的行数乘以T2的行数，看看本例，T1有4行，T2有4行，结果确只有6行，这就更不对了。难道就没有一种办法可以解决我的问题了吗？       想想以前没有SQLServer2005的行时候，连行列互换这样的问题都能够解决，难道现在还搞不定一个小小的连接查询？这两张表的结构相差不多，大不了我把所有的数据都整到一个表里去再来处理，一个表总比两个表处理起来好吧。咦，慢着，放到一个表里去处理？这到是个办法，没想到我从一句随口说的气话里面忽然看到了一丝希望。可是怎么放呢，最简单的方法就是union，可是列字段不一样呀。没关系，不存在的列咱给它补上，补成什么就看个人看喜好了，我这里就用null代替了。

       说干就干，我马上就写下了第一步中的SQL:    

select A,B,C,D,null as E from T1     
union     
select A,B,C,null as D,E from T2 

 

结果是什么呢：
        T:       A         B           C          D         E
                  a1       b1        c1         d1       null
                  a2       b2        c2         d2       null
                  a3       b3        c3         d3       null
                  a4       b4        c4         d4       null 
                  a1       b1        c1         null     e1   
                  a2       b2        c2         null     e2
                  a3       b3        c5         null     e3
                  a4       b4        c6         null     e4
这样总算是把两个表的数据合到一个表了，可是结果是4+4=8行，不是我要的结果6行，怎么办呢。这时我又想到了，我要的结果里A，B，C是联合唯一的，现在的结果是A，B，C三列不唯一，怎么办呢，直觉告诉我该group by上场了。把不唯一的分组变成唯的分组（以前还从来没有想过group by还有这等用处，呵呵），分组的列好说，就是A，B，C，可是未分组的列需要一个聚合函数呀，我们这里哪用得上聚合呢？看看不唯一的分组内D和E只有有值和无值两种状态。我又想到了以前分组查询的时候经常用到的min(datetime),max(datetime)这样的用法，取一个分组里的最大，最小时间，避免了把时间带到group by中去。这里不也是一样的嘛。有值和无值比较当然是有值的大了。
于是对SQL稍加修改变成了下面这样：

select A,B,C,max(D) as D, max(E) as E from
(     
select A,B,C,D,null as E from T1
union    
select A,B,C,null as D,E from T2 
) as T

 
这样的结果就是我想要的，o了！看似连接的问题不用连接搞定了。
         完成后回过头来想想这个真的就不能用连接来做了吗？怎么说也是脱离不了“从两个表里选出不同的列查询”的模式呀。直觉告诉我，如果可行的话只能是用到left join或right join，可是这两种方式无论是哪一种直接作用于两张表上都会丢失一部分数据。既然两个表直接连接搞不定，能不能临时生成第三张表，再和这两张表作两次连接，不让原来不该丢失的数据丢掉呢？如果可行的话该怎么构造这个临时表呢？带着这么多的疑问我又仔细考虑了一下，所有的问题最关键的地方是查询结果里面C的所有值必须全部出现，嗯就是它了。构造一个只有C一列的表把所有的C的值都装进去，这下再连接的话，C的值就不会丢了。SQL如下

select T1.A,T1.B,T.C,T1.D,T2.E from
( 
select C from T1 
union 
select C from T2
) as T     
left join T1 on T.C = T1.C    
left join T2 on T.C = T2.C

 
查询的结果跟上面的一样。
         旧的问题解决了，新的问题又来了，既然出现了两个答案，两个答案各自的本质是什么呢？谁才是最佳选择呢？对于第一个疑问，我的想法是:group by的方式的本质是把两个表的数据拿出来，按结果的列的唯一性分组，每组对应于结果集里的一行，每个组里只取一行，每列尽量选择不为空的值填充。后者实质上是先把结果集的行数定下来，也就是选出一个有效的列（本例是C），再用连接的方式取出相应列中的值。顺便说一句，虽然两种方式都用到了union，但是还是有些细微的差别的，前一种还可以用带all的方式，后一种绝对不能带all，想想也能明白，第一种里面就算是带了all，在后面的group by的时候也会被合并掉的，不会影响最后的结果。至于两者的性能嘛，在数据量少的时候完全可以不考虑的，如果非要问个究竟的话，我也只能说去看看查询计划生成的图表了。前一种方式做了两次表扫描（T1，T2各一次），一次排序，一次聚合；后一种做了四次表扫描（两次查询，两次连接），一次排序，两次嵌套循环。谁好谁坏各位自己分析吧。
       本以为这件事到些就结束了，可是接下来的一天我又发现了以前一直没有注意到的一点----在MSDN里看到了“全连接”这个概念。以前在刚学连接的时候见到过，记忆里有这么个印象，只因为平时遇到的问题基本上都能解决，所以对于这个不常用的东东具体含意是什么，什么时候使用，也就不清楚了，时间一长也忘得差不多了。现在想想全连接的作用就是把参加连接的左表和右表里各自没有的部分也都取到结果集中，放到我现在的这个问题上来看不是正合适的嘛。正所谓“会者不难，难者不会”，一旦明白了原理，相应的SQL也就不难写出来了。

 1select     
 2case 
 3        when T1.A is null then T2.A
 4         else T1.A
 5end as A,     
 6case
 7         when T1.B is null then T2.B
 8         else T1.B     
 9end as B,     
case
         when T1.C is null then T2.C
         else T1.C     
end as C,     
T1.D, T2.E 
from T1
     full join T2 on T1.A = T2.A and T1.B = T2.B and T1.C = T2.C

 

        没有用到union，没有用到临时表，一个连接搞定，代码看着也比较正统，比较优雅。至于这种方式的本质嘛，不用多说了，都在全联接的定义里了。性能方面，看看查询计划的图表，我实在是没有搞懂怎么一个全联接会进行四次表扫描，一次嵌套循环。难道按照最接近于“查询结果的要求”的定义写出来的SQL性能还不如第一种用“旁门左道”搞出来的SQL？是我的认知有问题还是本来就是这样的？暂时只能希望有这方面的高手能解答一下了。看来我对于全连接的认识还是不够呀，以后一定得多多注意，抽个时间好好补上一课。