关于SQL的几道小题详解(sql进阶)


当我们拿到题目的时候,并不是急于作答,那样会得不偿失的,而是分析思路,采用什么方法,达到什么目的,还要思考有没有简单的方法或者通用的方法等等,这样才会达到以一当十的效果,这样的惯性思维其实早在我们度高中的时候就被领教了,所谓“万变不离其宗”吧。以下各题来自日常所见,或QQ群,或面试题,或博客园。

题目一:如下表所示,现需要按照收款员统计收款和退款合计金额。

实现结果需如下显示:

分析:想要的结果(记为表B)和源数据(记为表A)相比,有共同的列(收款员),不同的是表A的金额根据标记和收款员分成了两列,所以这个需求可以用语言表述一下:首先根据收款员分组(group by),然后当标记为“收”时,金额计入收款合计(sum);当标记为“退”时,金额计入退款合计(sum)。当……时……,这不就是SQL的条件判断嘛?盘点SQL的条件语句不多,if……else……和case……when……then……else……end。这样问题就迎刃而解了。

解决方案如下:

with ta as
(select '收' as 标记,'100' as 收款员,150 as 金额
 union
 select '收','100',375
 union
 select '退','100',78
 union select '收','200',74
)

select 收款员,sum(case when 标记='收' then 金额 else 0 end) as 收款合计,
 sum(case when 标记='退' then 金额 else 0 end) as 退款合计 from ta
 group by 收款员

题目二:如下表A(左边)职员信息表,其中ID为职员工号,name为职员姓名;表B(右边)为职员任务分配表,其中ID为职员工号(和表A中ID对应),Task为任务编号。

现需求每个职员的任务数。结果如下显示:

其实原题是这样的:只有一张表B,求求每个职员的任务数。没有找到比较好的方法实现,不做讨论,欢迎高人指点。

分析:此题的难点在于表B中的ID复杂表示,其实这样有悖于数据库的设计原则,理应表A和表B的ID一一对应。既然是题,我们只能从当前的条件入手了,攻破难点的关键在于判断A中ID在B中ID出现与否,如果出现那么如何统计出现的次数。判断出现与否需要用到函数CHARINDEX。

解决方案如下:

--创建测试数据
WITH TA
AS
(SELECT '1,2' AS ID,'job1' AS task
UNION SELECT '1,2,3','job3'
UNION SELECT '2,3','job2'
UNION SELECT '3,4,5','job4')
,TB AS 
(SELECT '1' AS ID,'张三' as name
UNION SELECT '2','王二'
UNION SELECT '3','李四'
UNION SELECT '4','李明'
UNION SELECT '5','王五')

SELECT B.ID,B.name,COUNT(1) AS TASKS
 FROM TA A,TB B
 WHERE CHARINDEX(B.ID,A.ID)>0
 GROUP BY B.ID,B.name
 order by B.ID

题目三:原题参见这篇文章:http://www.phpstudy.net/article/67885.htm

如下表City所示,code为行政区域码(六位数字,前两代表省级,中间两位代表市级,最后两位代表县级,不考虑xx00xx情况),city为城市名称,CCode为该城市所属的省级或者市级行政区域码。

现需求如下结果:

分析:分析表city,code的含义十分明显,所需要的结果也很明显,如果是省就是显示省份;是市则显示为所属省级+市级;是县级则显示为所属省级+所属市级+县级。貌似可以用题目一分析中提到的SQL条件语句实现,但是转念一想,还是有差别,这里需要先判断city属于省级?市级?县级?然后在对应起来的,这样还得有参照表,复杂了。回到结果表中来进行分析,其实判定city属于省市县的问题并不难,code的含义已经说明了,只要转换表述:在表City中,当code的后四位为“0000”时,肯定是省级;当code的后两位为“00”,并且后四位不为“0000”时,肯定是市级;当code后两位不为“00”时,为县级。这样省市县的判定就一目了然了,然后,根据市级编码追朔所属的省级,并得出所属省级+市级,县级追朔所属的市级,得出所属省级+所属市级+所属县级,通过运用这种简单的递归思想,解决方案便跃然纸上了。

解决方案如下:

--测试数据
with ta as
(select '110000' as code, '北京市' city, '110000' Ccode
union
select N'110200', N'西城区', N'110200'
union
select N'110300', N'崇文区', N'110300'
union
select N'430000', N'湖南省', N'430000'
union
select N'430100', N'长沙市', N'430100'
union
select N'430101', N'望城县', N'430100')

select * into City from ta

select * from City;

--解决方案
with ta
as(
--省级
select code,city,Ccode,city content from City where right(code,4)='0000'),
tb as(
--市级
select b.code,b.city,b.Ccode,a.city+','+b.city as content from ta a,City b where left(a.Ccode,2)=left(b.Ccode,2)
and right(b.code,2)='00' and right(b.code,4)<>'0000'),
tc as(
select c.code,c.city,c.Ccode,b.content+','+c.city content from tb b,City c where left(b.Ccode,4)=left(c.Ccode,4)
and right(c.code,2)<>'00')
select * from ta
union
select * from tb
union
select * from tc

通过上述几道小题,常思常新,温故了SQL的部分知识,当然方法很多,变式很多,如题目二统计表B中每个Task的人数等。不足之处,欢迎各位指点!



相关阅读:
JavaScript中的值类型详细介绍
jquery遍历checkbox的注意事项说明
微软确认:Win10正式版将保留Feedback反馈应用
关于DDD:管理"工作单元实例"的两种模式的使用方法
Java字母加数字组合比较大小
MySQL 5.5.49 大内存优化配置文件优化详解
JavaScript中Function函数与Object对象的关系
firefox不显示border通过清除float便可解决
jQuery Mobile开发中日期插件Mobiscroll使用说明
使用asp.net调用谷歌地图api示例
Win10更新补丁失败后出现 无法更新正在撤销该怎么办?
如何处理小图标与文字混排的多种解决方案
jQuery平滑旋转幻灯片特效代码分享
JavaScript简单实现弹出拖拽窗口(二)
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 一个人的时候,不是不想你。 职场秘笈:五大标准辨清老板的密友 快乐中秋节。作文300字 给在远方爷爷的一封信 高山流水缘遇心灵知音,梅兰竹菊长出美丽心事 保护母亲河550字作文 通信监理月工作总结 精致中秋祝福语短信 学科带头人高级研修班教师培训心得 2009年销售工作计划 受罪350字 李开复《世界因你不同》读后感 2015内蒙古高考语文作文题目 一片草坪作文400字 敬老工作总结 XXX局电脑使用管理制度 时光老人的礼物作文400字 2016年度教师个人工作计划 别样故乡在黄梅——写故乡黄梅的作文 黎明与抉择 网神——这是“天下第一”吗?(4) 经典语录 珍惜你现在拥有的一切你就会快乐 热闹的水果市场 2016年酒店聘用合同范本 办公室年终工作总结暨表彰会议上的讲话稿 珠江口崛起的新城作文900字 【冬之韵】现代诗:北雪南雁 古镇如诗,你如画 只要笑到最后,胜利就在眼前——西游记读后感 留守不孤独作文1000字 关于文字 冲压工作职责和工作规范 网站管理规定 观看《开学第一课》有感300字 职场靠山提高心理正能量 扩写:双龙洞内洞作文400字 记住自己流泪的时候 新过五关斩六将作文3000字 纪念逝者的句子 老屋传 2013端午节的甜言蜜语短信祝福语 幼儿园老师外出学习心得 令人心酸的笑容作文700字 史上最幸福的女孩 数学家卡当 报考公务员的自我介绍 下一次暗恋作文500字 结婚典礼开场词 做个受欢迎的人(三) 生命需要阳光

Copyright © 2016 phpStudy |