- 浏览: 749249 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
webcover:
最新的中文网络记事本: 破笔记
网络记事本:http://w ...
五个最佳的免费网络记事本 -
fred_nxh:
很好,长见识了
java中堆(heap)和堆栈(stack)有什么区别 -
efeige:
兄弟,请问一下,为什么我的2003系统 网站属性 里面没有“服 ...
启用IIS Gzip 页面压缩技术 加速网页的浏览速度 -
252401762:
同样的问题啊,不知道楼主是否已经转做售前了
售前和 开发的选择 -
yuan:
膜拜玩静电的现在呢?
来回顾一下,当年的“发烧史”吧:
根据笔者的个人经验
详细介绍了多表查询引出问题的解决
方法,更多内容请读者参看下文:
最近一段时间做公司内部的一个业务
员业绩管理
站点,其中在做报表查询的时候遇到了一个有意思的问题,着实为难了我一下。因为一些不方便的原因,同时也为了便于描述,下面我把这个报表查询简化一下,但是本质上是一样的。
首先是说说表结构吧,一共两张表,暂命名为表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
行,这就更不对了。难道就没有一种办法
可以解决我的问题了吗? 想想以前没有SQLServer
2005
的行时候,连行列互换这样的问题都能够解决,难道现在还搞不定一个小小的连接查询?这两张表的结构相差不多,大不了我把所有的数据都整到一个表里去再来处
理,一个表总比两个表处理起来好吧。咦,慢着,放到一个表里去处理?这到是个办法,没想到我从一句随口说的气话里面忽然看到了一丝希望。可是怎么放呢,最
简单的方法就是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也就不难写出来了。
select
case
when T1.A is null then T2.A
else T1.A
end as A,
case
when T1.B is null then T2.B
else T1.B
end 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?是我的认知有问题还是本来就是这样的?暂时只能希望有这方面的高手能解答一下了。看来我对于全连接的认识还是
不够呀,以后一定得多多注意,抽个时间好好补上一课。
发表评论
-
MP-00058: 遇到 ORACLE 错
2011-01-25 10:15 1147MP-00058: 遇到 ORACLE 错误 12560问题 ... -
oracle登录遇到“无法解析连接描述符中指定的sid”时解决思路
2010-07-30 15:52 3376oracle登录遇到“无法解析连接描述符中指定的sid”时 ... -
oracle10g 在 oraparam.ini 中未找到先决条件检查, 不执行系统先决条件检查
2010-04-07 16:19 120356月13日 oracle10g 在 orapar ... -
oracle 数据库安装失败
2010-03-30 11:29 1190今天在机器上装Oracle 10g,安装完成以后出现错误提示“ ... -
深入浅出SQL之左连接、右连接和全连接-Mssql数据库教程
2010-03-17 12:19 1429深入浅出SQL之左连接、 ... -
完全攻略 Oracle数据库备份与恢复
2010-03-09 23:14 913完全攻略 Oracle数据库备份与恢复 一、 导出/导入(E ... -
MySQL数据库中用GRANT语句增添新用户
2009-08-04 11:28 668下面的例子显示如何使 ... -
dblink的设置
2009-06-30 15:38 961database link,它是用来更方便的一个数据库中访问另 ... -
察看数据库的大小,和空间使用情况
2009-06-29 15:39 902--察看数据库的大小,和空间使用情况 SELECT upp ... -
请问,oracle 有象MSSQL 一样的DEMO 数据库吗?
2009-06-16 14:52 848请问,oracle 有象MSSQL 一样的DEMO ... -
查看oracle版本命令
2009-06-15 11:31 1391查看oracle版本命令 1 查看oracle的版本信息 ... -
如何学习Oracle?
2009-06-12 15:19 1643如何学习Oracle? 经常有一些Oracle的 ... -
ORACLE索引与高性能SQL介绍(转载)
2009-06-09 20:05 840http://www.360doc.com/content/0 ... -
7.3 修改索引 --创建索引
2009-06-09 19:44 785Oracle 11g数据库系统设计、开发、管理与应用 ... -
如何创建oracle函数索引
2009-06-09 19:39 2607如何创建oracle函数索引 Oracle8i的很重要的一个 ... -
ORACLE函数大全
2009-06-09 17:36 787ORACLE函数大全 ============= ... -
Oracle中的数据锁定机制全面解析
2009-06-09 17:26 809http://www.diybl.com/course/7_d ... -
Oracle中查询rownum和rowid的区别
2009-06-09 15:37 1146Oracle中查询rownum和rowid ... -
orcle 获取前 10条数据
2009-06-05 15:43 754orcle 获取前 10条数据 select ... -
oracle dblink 创建过程
2009-06-04 17:19 2402空间管理 您的位置: I ...
相关推荐
一、问题的引出 在做一个公告浏览功能时,只要通过url传递的某参数值中包含 & 或 ,就会出现问题–该变量的值无法显示。 问题定位结果: 遇到&时,该参数的值会自动截断,导致参数值传递有误。 二、问题的解决 java...
铺垫、伏笔、引出下文的区别.doc
matlabsimulink中代数环问题的讲解及解决方法1-解决代数环方法.doc 本帖最后由 小小2008鸟 于 2012-11-30 11:26 编辑 什么是代数环?发生在两个或多个模块在输入端口具有信号直接传递而形成反馈的情况时,直接...
网管qqcc 图文教程 K3_BOM单引出引入方法.doc
EAS基础资料的引入引出流程和代码的实现应该对大家有用,共享一下。
爱普生R230废墨引出方法
一道面试题引出的系列数据库性能,数据安全问题及解决方案.docx
电子政务-炉水泵电机引出线制作方法.zip
水滴石穿C语言之编译器引出的问题.doc水滴石穿C语言之编译器引出的问题.doc
企业文化全套课件--第二讲企业文化的功能作用(问题引出,评价标准)和内容(ppt 30).ppt
六、引出内容包括:科目资料表,核算项目表,记账凭证,核算分类表 使用方法: 1.先打开附带的引出样板.xlsx文件 2.然后再双击打开引出工具V1.0 3.点击按钮选择账套文件,或者用鼠标拖动文件到文本框放开 4.点击...
GBT 2423.60-2008 电工电子产品环境试验 第2部分:试验方法 试验U:引出端及整体安装件强度.pdf
UltraEdit引出的字符编码问题.doc
过程与方法: 通过解决实际问题,体验计算机解决问题的过程。 通过案例实操,让学生学会运用编程环境,调试和运行程序。 通过情境创设,让学生利用计算机解决数学问题,实现学科融合。 情感态度价值观: 培养学生在...
介绍 MASM 下 的源程序格式和开发方法,展开包括常量和变量的数据表达方式,并引出常用的汇编语言 伪指令
PDCA又叫戴明环,它将一个解决问题需要的四个...通过处理这个阶段又引出解决此问题需要解决的诸多彼问题。大圆套小圆,小圆无极限,直到最底层的小圆中不再引出问题为止。如此周而复始的循环,与中国古代的八卦相挥印。
金蝶K3凭证引入引出的指导,适合新手使用!欢迎大家下载
学习电脑信息UltraEdit引出的字符编码问题