SQL查询实例应用之一

pc520

此题来自于本站VBA开发版的一个帖子：http://club.excelhome.net/thread-539107-1-1.html，原题请到此下载
楼主原意是要用VBA来解决问题的，看到他们的VBA代码，我的羡慕之情犹如滔滔江水，绵延不绝，但是查询和统计是我们透视表的强项，看到题就想着用透视表来解决，当然这里也有我是V白的原因吧，下面就让只会一点点透视表的我试着用透视表来解决下，也拿这个例题跟透视表版块的同仁们一起探讨下有无更好的方法解决此类问题。

SQL代码：
SELECT T3.出险件数, T3.续保台数, (T4.上月合计 - T3.续保台数) AS 丢失台数
FROM (SELECT SUM(IIF(RIGHT(T1.号牌号码,1)='挂',IIF(T1.号牌号码=T2.号牌号码,1,0),IIF(RIGHT(T1.发动机号,6)=RIGHT(T2.发动机号,6),1,0))) AS 续保台数, T1.出险件数
FROM [Sheet1$] T1, [Sheet2$] T2 GROUP BY T1.出险件数) T3, (SELECT COUNT(T1.号牌号码) AS 上月合计, T1.出险件数
FROM [Sheet1$] T1 GROUP BY T1.出险件数) T4
WHERE T3.出险件数=T4.出险件数

拿到此类题目，首先要做的事情是数据源的规范，去除空格以及不可见字符，删除空白行和空白列，然后去掉所有单元格格式的对齐方式，观察数据是否统一，这里楼主的车牌号和发动机号是混合文本和数值的，虽然我们可以在SQL语句中使用兼容，但先统一数据为文本格式对我们调试SQL语句更为方便，不容易出错。所以先使用分列转换格式为文本，至此，数据源的规范化完成。

下面分析楼主的题意和需求。估计是楼主不太了解透视表的原因，把那个流程图，做的N复杂，不过好歹也是说明白了。我理解后把题意简化了下，意思是号牌号码中带“挂”字的，只要Sheet1和Sheet2表中的车号相同，就是续保，不带“挂”字的，虽然楼主说了N多判断方式，但只要是发动机号最后六位相同，就是续保，而最后统计是按“出险件数”进行分组。

理解题意后，SQL语句也就并不复杂，其重点是把Sheet1中的每条记录按是否续保在Sheet2中进行查询，按“出险件数”进行分组，用了两个函数，IIF和RIGHT，IIF(RIGHT(T1.号牌号码,1)='挂',IIF(T1.号牌号码=T2.号牌号码,1,0),IIF(RIGHT(T1.发动机号,6)=RIGHT(T2.发动机号,6),1,0))，很容易理解，首先判断“号牌号码”中最后一位是否等于“挂”，如果是看Sheet1号牌号码是否等于Sheet2号牌号码，是则返回1，否则返回；如果没有“挂”，如果发动机号后六位相等则返回1，否则返回0，然后按“出险件数”分组求和。这个函数再解释就有点那个了......
丢失台数的查询，应该有更好的办法，我是用Sheet1按“出险件数”分组后减去“续保台数”，估计大家有更好的办法。

下面是我所做结果，欢迎大家指正。

实例.rar (8.72 KB, 下载次数: 603)

2010-2-26 00:03 上传

点击文件名下载附件

[ 本帖最后由 pc520 于 2010-2-26 00:33 编辑 ]

蓝桥玄霜

在VBA版看到楼主用数据透视表完美的解决了这个问题，让只会一点点透视表的我，我的羡慕之情犹如滔滔江水，绵延不绝。
楼主是Excel的高手啊。

kiki2008good

我也正在学数据透视表，看完楼主的代码后，更想学了！
支持一下！呵呵

函数菜鸟

PC的钻研精神偶灰常的佩服.

htqccw

高手弄出来的就是好东东，学习中。

小蚁雄兵

试试下面的语句：

1. SELECT (SELECT COUNT(*) FROM [SHEET2$A:E]) AS 当月承保总台数,(SELECT COUNT(*) FROM [SHEET1$A:F]) AS 当月到期台数,IIF(ISNULL(T2.保单号),"丢失","续保") AS 续保与否,T1.* FROM [Sheet1$A:F] T1 LEFT JOIN [SHEET2$A:E] T2 ON ((T1.号牌号码 LIKE '%挂' AND T1.号牌号码=T2.号牌号码) OR (NOT T1.号牌号码 LIKE '%挂' AND RIGHT(T1.发动机号,6)=RIGHT(T2.发动机号,6)))

复制代码

pc520

哈哈，果然有所收获，楼上老师的代码比较强，我代为解释下：
正常我们用到的多表连接查询，其连接关系一般多为=<>之类的简单连接，在老师的代码中，可以看到，连接多表也可以用到复杂的逻辑判断。

dgxsdr

下载学习！

bibisin

谢谢LZ分享，收藏学习

masterexcel

楼主的钻探精神非常强，赞一个！
标记，待研究。