软件开发高手须掌握的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中，有四大语句堪称其精髓部分。下面就讲述一下其中之一，WITH AS语句的用法。
WITH AS短语，也叫做子查询部分，可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNIONALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。
语法如下：
[ WITH table_name ]
AS
(query_definition )
1本例所用工具简单说明
下面就以DB2数据库为例，以“万能数据库查询分析器”中文版本《DB 查询分析器》、英文版本《DB Query Analyzer》作为客户端工具来来演示一下Inner Join和 Outer Join 语句的用法。
之所以选择“万能数据库查询分析器”作为客户端工具，主要是因为，一方面，“万能数据库查询分析器”具有强大的功能、友好的操作界面、良好的操作性、跨越数据库平台。在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。
另一方面，截止到目前，中文版本《DB查询分析器》在国内最著名的软件下载网站“中关村在线”中下载量近9万次，位居整个数据库类排行谤中前20位。
2“万能数据库查询分析器”中文版本《DB查询分析器》为例
下面就以DB2数据库为例，以“万能数据库查询分析器”英文版本《DB 查询分析器》在Windows XP上运行，来作为客户端工具来来演示一下With As结合Outer Join 、 Inner Join语句的使用。
运行《DB查询分析器》，连接上数据源 unit4_2,然后输入以下命令：
withw_XROADLEVEL as
(
select *from TB_XROADLEVELwhere (areano,roadno)in
(select areano,roadnofrom tb_roadwhere level=2)
)
select u.incroadno,r.roadname,squaddate,
sum(casewhen u.areano=4407and u.outroadno=1 then unpaymoney/100.0else 0end)as out__1,
sum(casewhen u.areano=4407and u.outroadno=2 then unpaymoney/100.0else 0end)as out__2,
sum(casewhen u.areano=4407and u.outroadno= 17then unpaymoney/100.0else 0end)as out_17,
sum(casewhen u.areano=4407and u.outroadno= 22then unpaymoney/100.0else 0end)as out_22,
sum(casewhen u.areano=4407and u.outroadno= 27then unpaymoney/100.0else 0end)as out_27,
sum(casewhen u.areano=4407and u.outroadno= 37then unpaymoney/100.0else 0end)as out_37,
sum(casewhen u.areano=4407and u.outroadno= 40then unpaymoney/100.0else 0end)as out_40,
sum(casewhen u.areano=4407and u.outroadno= 41then unpaymoney/100.0else 0end)as out_41,
sum(casewhen u.areano=4407and u.outroadno= 48then unpaymoney/100.0else 0end)as out_48,
sum(casewhen u.areano=4407and u.outroadno= 49then unpaymoney/100.0else 0end)as out_49,
sum(casewhen u.areano=4407and u.outroadno= 50then unpaymoney/100.0else 0end)as out_50,
sum(casewhen u.areano=4407and u.outroadno= 57then unpaymoney/100.0else 0end)as out_57,
sum(casewhen u.areano=4407and u.outroadno= 58then unpaymoney/100.0else 0end)as out_58,
sum(casewhen u.areano=4407and u.outroadno= 60then unpaymoney/100.0else 0end)as out_60,
sum(casewhen u.areano=4407and u.outroadno= 63then unpaymoney/100.0else 0end)as out_63,
sum(casewhen u.areano=4407and u.outroadno= 70then unpaymoney/100.0else 0end)as out_70,
sum(casewhen u.areano=4407and u.outroadno= 71then unpaymoney/100.0else 0end)as out_71,
sum(casewhen u.areano=4407and u.outroadno= 75then unpaymoney/100.0else 0end)as out_75,
sum(casewhen u.areano=4407and u.outroadno= 80then unpaymoney/100.0else 0end)as out_80,
sum(casewhen u.areano=4407and u.outroadno= 83then unpaymoney/100.0else 0end)as out_83,
sum(casewhen u.areano=4407and u.outroadno= 84then unpaymoney/100.0else 0end)as out_84,
sum(casewhen u.areano=4407and u.outroadno=262then unpaymoney/100.0else 0end)as out262,
sum(casewhen u.areano=4407and u.outroadno=263then unpaymoney/100.0else 0end)as out263,
sum(casewhen u.areano=4407and u.outroadno=264then unpaymoney/100.0else 0end)as out264,
sum(casewhen u.areano=4407and u.outroadno=265then unpaymoney/100.0else 0end)as out265,
sum(casewhen u.areano=4407and u.outroadno=266then unpaymoney/100.0else 0end)as out266,
sum(casewhen u.areano=4407and u.outroadno=267then unpaymoney/100.0else 0end)as out267,
sum(casewhen u.areano=4407and u.outroadno=268then unpaymoney/100.0else 0end)as out268,
sum(casewhen u.areano=4407and u.outroadno=269then unpaymoney/100.0else 0end)as out269,
sum(casewhen u.areano=4407and u.outroadno=272then unpaymoney/100.0else 0end)as out272,
sum(casewhen u.areano=4407and u.outroadno=273then unpaymoney/100.0else 0end)as out273
from
(
select a.areano,a.SquadDate,a.unpaymoney,
case ri.levelwhen 0then a.incroadnowhen 3then xi.roadno
endas incroadno,
case ro.levelwhen 0then a.outroadnowhen 3then xo.roadno
endas outroadno
from w_unpay_veh a
innerjoin tb_road rion a.areano=ri.areanoand a.incroadno=ri.roadno
innerjoin tb_road roon a.areano=ro.areanoand a.outroadno=ro.roadno
leftouterjoin w_XROADLEVEL xion xi.areano=a.areanoand xi.SUBROADNO=a.incroadno
leftouterjoin w_XROADLEVEL xoon xo.areano=a.areanoand xo.SUBROADNO=a.outroadno
where SQUADDATE>='2012-08-01' and SQUADDATE<'2012-09-01'
)as u, tb_road r
where r.areano=4407and u.areano=r.areanoand u.incroadno=r.roadno
groupby u.incroadno,r.roadname,squaddateorderby u.incroadno,squaddate;
图1输入SQL语句，准备执行（一）
图2输入SQL语句，准备执行（二）
图3执行结果
3“万能数据库查询分析器”英文版本《DB Query Analzyer》为例
下面就以DB2数据库为例，以“万能数据库查询分析器”英文版本《DB Query Analyzer》在Windows 2000上运行，来作为客户端工具来来演示一下With As结合Outer Join 、 Inner Join语句的使用。
运行《DB Query Analyzer》，连接上数据源unit4_2 ,然后输入以下命令：
withw_XROADLEVEL as
(
select *from TB_XROADLEVELwhere (areano,roadno)in
(select areano,roadnofrom tb_roadwhere level=2)
)
select u.incroadno,r.roadname,squaddate,
sum(casewhen u.areano=4407and u.outroadno=1 then unpaymoney/100.0else 0end)as out__1,
sum(casewhen u.areano=4407and u.outroadno=2 then unpaymoney/100.0else 0end)as out__2,
sum(casewhen u.areano=4407and u.outroadno= 17then unpaymoney/100.0else 0end)as out_17,
sum(casewhen u.areano=4407and u.outroadno= 22then unpaymoney/100.0else 0end)as out_22,
sum(casewhen u.areano=4407and u.outroadno= 27then unpaymoney/100.0else 0end)as out_27,
sum(casewhen u.areano=4407and u.outroadno= 37then unpaymoney/100.0else 0end)as out_37,
sum(casewhen u.areano=4407and u.outroadno= 40then unpaymoney/100.0else 0end)as out_40,
sum(casewhen u.areano=4407and u.outroadno= 41then unpaymoney/100.0else 0end)as out_41,
sum(casewhen u.areano=4407and u.outroadno= 48then unpaymoney/100.0else 0end)as out_48,
sum(casewhen u.areano=4407and u.outroadno= 49then unpaymoney/100.0else 0end)as out_49,
sum(casewhen u.areano=4407and u.outroadno= 50then unpaymoney/100.0else 0end)as out_50,
sum(casewhen u.areano=4407and u.outroadno= 57then unpaymoney/100.0else 0end)as out_57,
sum(casewhen u.areano=4407and u.outroadno= 58then unpaymoney/100.0else 0end)as out_58,
sum(casewhen u.areano=4407and u.outroadno= 60then unpaymoney/100.0else 0end)as out_60,
sum(casewhen u.areano=4407and u.outroadno= 63then unpaymoney/100.0else 0end)as out_63,
sum(casewhen u.areano=4407and u.outroadno= 70then unpaymoney/100.0else 0end)as out_70,
sum(casewhen u.areano=4407and u.outroadno= 71then unpaymoney/100.0else 0end)as out_71,
sum(casewhen u.areano=4407and u.outroadno= 75then unpaymoney/100.0else 0end)as out_75,
sum(casewhen u.areano=4407and u.outroadno= 80then unpaymoney/100.0else 0end)as out_80,
sum(casewhen u.areano=4407and u.outroadno= 83then unpaymoney/100.0else 0end)as out_83,
sum(casewhen u.areano=4407and u.outroadno= 84then unpaymoney/100.0else 0end)as out_84,
sum(casewhen u.areano=4407and u.outroadno=262then unpaymoney/100.0else 0end)as out262,
sum(casewhen u.areano=4407and u.outroadno=263then unpaymoney/100.0else 0end)as out263,
sum(casewhen u.areano=4407and u.outroadno=264then unpaymoney/100.0else 0end)as out264,
sum(casewhen u.areano=4407and u.outroadno=265then unpaymoney/100.0else 0end)as out265,
sum(casewhen u.areano=4407and u.outroadno=266then unpaymoney/100.0else 0end)as out266,
sum(casewhen u.areano=4407and u.outroadno=267then unpaymoney/100.0else 0end)as out267,
sum(casewhen u.areano=4407and u.outroadno=268then unpaymoney/100.0else 0end)as out268,
sum(casewhen u.areano=4407and u.outroadno=269then unpaymoney/100.0else 0end)as out269,
sum(casewhen u.areano=4407and u.outroadno=272then unpaymoney/100.0else 0end)as out272,
sum(casewhen u.areano=4407and u.outroadno=273then unpaymoney/100.0else 0end)as out273
from
(
select a.areano,a.SquadDate,a.unpaymoney,
case ri.levelwhen 0then a.incroadnowhen 3then xi.roadno
endas incroadno,
case ro.levelwhen 0then a.outroadnowhen 3then xo.roadno
endas outroadno
from w_unpay_veh a
innerjoin tb_road rion a.areano=ri.areanoand a.incroadno=ri.roadno
innerjoin tb_road roon a.areano=ro.areanoand a.outroadno=ro.roadno
leftouterjoin w_XROADLEVEL xion xi.areano=a.areanoand xi.SUBROADNO=a.incroadno
leftouterjoin w_XROADLEVEL xoon xo.areano=a.areanoand xo.SUBROADNO=a.outroadno
where SQUADDATE>='2012-08-01' and SQUADDATE<'2012-09-01'
)as u, tb_road r
where r.areano=4407and u.areano=r.areanoand u.incroadno=r.roadno
groupby u.incroadno,r.roadname,squaddateorderby u.incroadno,squaddate;
图1输入SQL语句，准备执行（一）
图2输入SQL语句，准备执行（二）
图3执行结果
软件开发高手须掌握的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
《程序员》“特别推荐”了“万能数据库查询分析器”：
在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。
http://blog.csdn.net/magenfeng/article/details/7192368
《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