软件开发高手须掌握的4大SQL精髓语句（综合篇）
马根峰
( 广东联合电子收费股份有限公司,广州 510300)
作者博客：
CSDN博客：http://blog.csdn.net/magenfeng
新浪博客： http://blog.sina.com.cn/magenfeng
QQ空间： http://user.qzone.qq.com/630414817/main#!
0 引言
随着计算机在社会各领域应用的深入，软件开发人员不得不面临着或多或少的数据处理、数据库访问。
在SQL（Structured Query Language）语言DML中，有四大语句堪称其精髓部分，分别是CASE WHEN语句，Left Outer Join | Left Join | Right Outer Join | Right Join | Inner Join语句，WITH AS语句，UNION、UNION ALL语句。
本人分别在“软件开发高手须掌握的4大SQL精髓语句（一）”至“软件开发高手须掌握的4大SQL精髓语句（四）” 4篇文章中进行了详细的阐述。
本篇文章用一个本人实际工作中的一个实例，实现了上述4大精髓SQL的综合应用。
1 本例所用工具简单说明
下面就以DB2数据库为例，以“万能数据库查询分析器”中文版本《DB查询分析器》、英文版本《DB Query Analyzer》作为客户端工具来来演示一下4大SQL精髓语句的用法。
之所以选择“万能数据库查询分析器”作为客户端工具，主要是因为，一方面，“万能数据库查询分析器”具有强大的功能、友好的操作界面、良好的操作性、跨越数据库平台。在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。
另一方面，截止到目前，中文版本《DB查询分析器》在国内最著名的软件下载网站“中关村在线”中下载量近9万次，位居整个数据库类排行谤中前20位。
2 建立DB2数据库的 ODBC数据源UNITOLL2
《DB查询分析器》是通过ODBC数据源来访问各种数据库及EXCEL的，所以首先要建立要访问的数据库或EXCEL的ODBC数据源。如果您的主机上没有对应数据库的ODBC驱动程序，那您还要下载（通常该数据库厂家的官方网站上都提供有ODBC驱动程序下载）安装对应数据库的ODBC驱动程序，然后再建立相应的ODBC数据源。
DB2数据库是通过配置助手来完成ODBC数据源的创建，下面7张图是创建ODBC数据源UNITOLL2的完整过程。
图1 第1步
图2 第2步
图3 第3步
图4 第4步
图5 第5步
图6 第6步
图7 第7步
3 实例用到的SQL脚本
下面就以DB2数据库为例，以“万能数据库查询分析器”中文版本《DB查询分析器》在Windows XP上运行，来作为客户端工具来来演示一下4大SQL精髓语句的使用。
运行《DB查询分析器》，连接上数据源UNITOLL2，然后输入以下命令：
with aas
(
select outlistno,uploadtime,weightflag,vehkindflag,
sum(etcmoney)/100 money,sum(vehmoney)/100 vehmoney,sum(etcmoney)/100 money_r
from tb_cardaccuratesplitresult_weight
where squaddate>='2012-08-01' and squaddate<'2012-09-01'and areano=4406
and outroadno>=1 and batchno>=1and splitlevel<=2
and (outroadno,squaddate,batchno,
casewhen OUTLANETYPEin (2,5)then 2when OUTLANETYPEin (4,6)then 4end
)in (select outroadno,squaddate,batchno,OUTLISTTYPE*2
from tb_RoadCardSplitCheck
where squaddate>='2012-08-01' and squaddate<'2012-09-01'
and areano=4406 and result<=0)
groupby outlistno,uploadtime,weightflag,vehkindflag
unionall
select outlistno,uploadtime,weightflag,vehkindflag,
sum(cashmoney+officemoney+freemoney+unpaymoney)/100 money,
sum(vehmoney)/100 vehmoney,sum(cashmoney)/100 money_r
from tb_cashaccuratesplitresult8
where areano=4406 and outroadno>=1
and squaddate>='2012-08-01' and squaddate<'2012-09-01'
and batchno>=1 and splitlevel<=2
and (outroadno,squaddate,batchno) in
( select outroadno,squaddate,batchno
from tb_RoadCashSplitCheck
where squaddate>='2012-08-01'and squaddate<'2012-09-01'
and areano=4406and result<=0
union
select outroadno,squaddate,batchno
from tb_RoadCashSplitCheck_his
where squaddate>='2012-08-01'and squaddate<'2012-09-01'
and areano=4406and result<=0
)
groupby outlistno,uploadtime,weightflag,vehkindflag
) , bas
(
select listno,uploadtime,squaddate,backup5,backup6,AxisCnt,AllWeight,ExLimitWeight,PREVEHMONEY,roadno,vehcount
from tb_outlist_weight
where uploadtime>='2012-07-28-00.00.00' and areano=4406and roadno>=1
and squaddate>='2012-08-01' and squaddate<'2012-09-01'
unionall
select listno,uploadtime,squaddate,backup5,backup6,AxisCnt,AllWeight,ExLimitWeight,PREVEHMONEY,roadno,vehcount
from tb_manualoutlist_weight
where areano=4406 and roadno>=1
and squaddate>='2012-08-01' and squaddate<'2012-09-01'
) , tas
(
selectmin(correctid) correctid,listno,uploadtime
from tb_outlistcorrected
where areano=4406 and squaddate>='2012-08-01'and squaddate<'2012-09-01'
groupby listno,uploadtime
) , cas
(
select listno,uploadtime,backup5,backup6,AxisCnt,AllWeight,ExLimitWeight,PREVEHMONEY
from tb_outlistcorrected
where areano=4406 and squaddate>='2012-08-01'and squaddate<'2012-09-01'
and (correctid,listno,uploadtime) in (select * from t)
) , oas
(
select a.*,b.roadno,b.vehcount,
casewhen c.listnoisNULLthen b.backup5else c.backup5endas backup5,
casewhen c.listnoisNULLthen b.backup6else c.backup6endas backup6,
casewhen c.listnoisNULLthen b.AxisCntelse c.AxisCntendas AxisCnt,
casewhen c.listnoisNULLthen b.AllWeightelse c.AllWeightendas AllWeight,
casewhen c.listnoisNULLthen b.ExLimitWeightelse c.ExLimitWeightendas ExLimitWeight,
casewhen c.listnoisNULLthen b.prevehmoneyelse c.prevehmoneyendas prevehmoney
from b leftouterjoin c
on b.listno=c.listno and b.uploadtime=c.uploadtime
innerjoin a
on b.listno=a.outlistno and b.uploadtime=a.uploadtime
)
select roadno 路段,sum(vehcount) 出口车流量,'',sum(money_r)出口总收入,'',
sum(casewhen vehkindflag=2then vehcountelse 0end)货车车流量,
sum(casewhen vehkindflag=2then money_relse 0end)货车收费额,
sum(casewhen vehkindflag=2and weightflag<4andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+30/100.0)
then vehcountelse 0end)超限车流量,
sum(casewhen vehkindflag=2and weightflag<4andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+30/100.0)
then money_relse 0end)超限车辆收费额,
sum(casewhen vehkindflag=2and weightflag<4andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+30/100.0)
then money_r-vehmoneyelse 0end)超限加收金额,
sum(casewhen vehkindflag=2and weightflag=4then vehcountelse 0end)降档车流量,
sum(casewhen vehkindflag=2and weightflag=4then money_relse 0end)降档收费金额,
sum(casewhen vehkindflag=2and weightflag=4then prevehmoney-money_relse 0end)降档减免金额,
sum(casewhen vehkindflag=2and weightflag<4andabs(AllWeight)<=(abs(AllWeight)-abs(ExLimitWeight))*(1+30/100.0)
then vehcountelse 0end)正常装载及超载30以内车流量,
sum(casewhen vehkindflag=2and weightflag<4
andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+30/100.0)
andabs(AllWeight)<=(abs(AllWeight)-abs(ExLimitWeight))*(1+50/100.0)
then vehcountelse 0end)轻度超载30至50车流量,
sum(casewhen vehkindflag=2and weightflag<4
andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+50/100.0)
andabs(AllWeight)<=(abs(AllWeight)-abs(ExLimitWeight))*(1+100/100.0)
then vehcountelse 0end)中度超载50至100车流量,
sum(casewhen vehkindflag=2and weightflag<4
andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+100/100.0)
andabs(AllWeight)<=(abs(AllWeight)-abs(ExLimitWeight))*(1+150/100.0)
then vehcountelse 0end)重度超载100至150车流量,
sum(casewhen vehkindflag=2and weightflag<4
andabs(AllWeight)>(abs(AllWeight)-abs(ExLimitWeight))*(1+150/100.0)
then vehcountelse 0end)严重超载150以上车流量
from ogroupby roadnoorderby 1with ur;
4 “万能数据库查询分析器”中文版本《DB查询分析器》为例
下面就以DB2数据库为例，以“万能数据库查询分析器”中文版本《DB查询分析器》在Windows XP上运行，来作为客户端工具来来演示一下4大SQL精髓语句的使用。
运行《DB查询分析器》，连接上数据源UNITOLL2，然后输入3中SQL脚本：
图1 输入3中实例脚本（一）
图2 输入3中实例脚本（二）
图3 输入3中实例脚本（三）
图4 开始执行
图5 执行结果（一）
图6 执行结果（二）
图7 执行结果（三）
5 “万能数据库查询分析器”英文版本《DB Query Analzyer》为例
下面就以DB2数据库为例，以“万能数据库查询分析器”英文版本《DB Query Analyzer》在Windows 2000上运行，来作为客户端工具来来演示一下4大SQL精髓语句的使用。
运行《DB Query Analyzer》，连接上数据源UNITOLL2，然后输入3中SQL脚本：
图1 输入3中实例脚本（一）
图2 输入3中实例脚本（二）
图3 输入3中实例脚本（三）
图4 开始执行
图5 执行结果（一）
图6 执行结果（二）
图7 执行结果（三）
软件开发高手须掌握的4大SQL精髓语句 系列：
软件开发高手须掌握的4大SQL精髓语句（四）
http://blog.csdn.net/magenfeng/article/details/8050612
软件开发高手须掌握的4大SQL精髓语句（三）
http://blog.csdn.net/magenfeng/article/details/8015093
软件开发高手须掌握的4大SQL精髓语句（二）
http://blog.csdn.net/magenfeng/article/details/8003558
软件开发高手须掌握的4大SQL精髓语句（一）
http://blog.csdn.net/magenfeng/article/details/7969385
《DB查询分析器》使用技巧系列：
《DB 查询分析器》使用技巧之（一）
http://blog.csdn.net/magenfeng/article/details/7189174
《DB 查询分析器》使用技巧之（二）
http://blog.csdn.net/magenfeng/article/details/7189176
《DB 查询分析器》使用技巧之（三）
http://blog.csdn.net/magenfeng/article/details/7192402
《DB 查询分析器》使用技巧之（四）
http://blog.csdn.net/magenfeng/article/details/7195059
《DB 查询分析器》使用技巧之（五）
http://blog.csdn.net/magenfeng/article/details/7196846
《DB 查询分析器》使用技巧之（六）
http://blog.csdn.net/magenfeng/article/details/7164432
《DB 查询分析器》使用技巧之（七）
http://blog.csdn.net/magenfeng/article/details/7197934
《程序员》“特别推荐”了“万能数据库查询分析器”：
在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。
http://blog.csdn.net/magenfeng/article/details/7192368
“万能数据库查询分析器”所获得的成果
自己开发的“万能数据库查询分析器”终于有了较大的成果
http://blog.csdn.net/magenfeng/article/details/8079396
- 11楼fengzi2009F4分钟前
- 值得收藏和学习
- 10楼M443464606分钟前
- 贴在页面中的代码格式化一下会好看很多。
- 9楼ziyaziya05昨天 12:52
- 执行时间是多久？
- 8楼dragonAhao昨天 09:37
- 敬仰
- 7楼KINKYChen昨天 17:21
- 这。。。。这sql语句，滔滔江水，连绵不绝
- 6楼duckcychen昨天 14:41
- 感谢楼主分享，不过就我目前的水平貌似用不了那么多，先收藏一下，以后要用多就可以先拿出来看看！
- 5楼myhope88昨天 13:49
- 看晕了
- 4楼a8892485昨天 12:42
- 厉害厉害
- 3楼greatneal前天 22:48
- 学习了，一般应用也就到Join。
- 2楼baifuhan前天 21:13
- 表示尚未用到这么复杂的！但是还是学习了！
- 1楼wnzh2012前天 20:19
- 赞！！！！！！