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

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 收款员

--创建测试数据
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

--测试数据
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