關於SQL的幾道小題詳解(sql進階)

關於SQL的幾道小題詳解(sql進階)。本站提示廣大學習愛好者：（關於SQL的幾道小題詳解(sql進階)）文章只能為提供參考，不一定能成為您想要的結果。以下是關於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.jb51.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的人數等。缺乏的地方，迎接列位指導！