软件开发高手须掌握的4大SQL精髓语句（综合篇）

软件开发高手须掌握的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