Question

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

Answer 1

当我们拿到题目的时候，并不是急于作答，那样会得不偿失的，而是分析思路，采用什么方法，达到什么目的，还要思考有没有简单的方法或者通用的方法等等，这样才会达到以一当十的效果，这样的惯性思维其实早在我们度高中的时候就被领教了，所谓“万变不离其宗”吧。以下各题来自日常所见，或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
题目三：原题参见这篇文章：//www.gxlcms.com/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的人数等。不足之处，欢迎各位指点！