SQL四个小题

发布网友发布时间：2022-04-07 22:16

共4个回答

懂视网时间：2022-04-08 02:37

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

技术分享

实现结果需如下显示：

技术分享

分析：想要的结果（记为表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.cnblogs.com/Lumia1020/p/4571301.html

如下表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的人数等。不足之处，欢迎各位指点！

关于SQL的几道小题详解

标签：sql 面试题

热心网友时间：2022-04-07 23:45

1.select s.sno,s.sn from s join sc on sc.sno=s.sno join c on c.cno=sc.cno where c.cn='数据库概论'
2.select s.sno,s.sn from s where s.sno in (select sc.sno from sc group by sno having count(*)=(select count(*) from c))
3.select s.sno,s.sn from s where s.sno in (select sc.sno from sc group by sno having count(*)>=5)
4.delete from sc where sc.cno in (select c.cno from c where c.cn='数据结构'
delete from c where c.cn='数据结构'

热心网友时间：2022-04-08 01:03

select sno,sn from table where cn='数据库概论’
select sno,sn from table where cn in all

select sno from table where count(cn)>5
delete cn='数据结构' from table

热心网友时间：2022-04-08 02:38

1.关系代数的演算比较复杂，也无法在文本状态下书写，（我也忘的差不多了）请参阅《数据库原理》等相关书籍
2.(1) select sc_cnt.sno from （select sno, count(*) as cnt from sc group by sno hanving count(*) >= 5) sc_cnt;
(2) delete from sc where exist(select * from c where c.cno = sc.cno and c.cn = '数据结构'); delete from c where c.cn = '数据结构';

希望对你有帮助