關(guān)于SQL的幾道小題詳解(sql進(jìn)階)

當(dāng)我們拿到題目的時(shí)候，并不是急于作答，那樣會(huì)得不償失的，而是分析思路，采用什么方法，達(dá)到什么目的，還要思考有沒有簡(jiǎn)單的方法或者通用的方法等等，這樣才會(huì)達(dá)到以一當(dāng)十的效果，這樣的慣性思維其實(shí)早在我們度高中的時(shí)候就被領(lǐng)教了，所謂“萬(wàn)變不離其宗”吧。以下各題來(lái)自日常所見，或QQ群，或面試題，或博客園。

題目一：如下表所示，現(xiàn)需要按照收款員統(tǒng)計(jì)收款和退款合計(jì)金額。

實(shí)現(xiàn)結(jié)果需如下顯示：

分析：想要的結(jié)果（記為表B）和源數(shù)據(jù)（記為表A）相比，有共同的列（收款員），不同的是表A的金額根據(jù)標(biāo)記和收款員分成了兩列，所以這個(gè)需求可以用語(yǔ)言表述一下：首先根據(jù)收款員分組（group by）,然后當(dāng)標(biāo)記為“收”時(shí)，金額計(jì)入收款合計(jì)（sum）；當(dāng)標(biāo)記為“退”時(shí)，金額計(jì)入退款合計(jì)（sum）。當(dāng)……時(shí)……，這不就是SQL的條件判斷嘛？盤點(diǎn)SQL的條件語(yǔ)句不多，if……else……和case……when……then……else……end。這樣問題就迎刃而解了。

解決方案如下：

with ta as
(select '收' as 標(biāo)記,'100' as 收款員,150 as 金額
 union
 select '收','100',375
 union
 select '退','100',78
 union select '收','200',74
)

select 收款員,sum(case when 標(biāo)記='收' then 金額 else 0 end) as 收款合計(jì),
 sum(case when 標(biāo)記='退' then 金額 else 0 end) as 退款合計(jì) from ta
 group by 收款員

題目二：如下表A（左邊）職員信息表，其中ID為職員工號(hào)，name為職員姓名；表B（右邊）為職員任務(wù)分配表，其中ID為職員工號(hào)（和表A中ID對(duì)應(yīng)），Task為任務(wù)編號(hào)。

現(xiàn)需求每個(gè)職員的任務(wù)數(shù)。結(jié)果如下顯示：

其實(shí)原題是這樣的：只有一張表B，求求每個(gè)職員的任務(wù)數(shù)。沒有找到比較好的方法實(shí)現(xiàn)，不做討論，歡迎高人指點(diǎn)。

分析：此題的難點(diǎn)在于表B中的ID復(fù)雜表示，其實(shí)這樣有悖于數(shù)據(jù)庫(kù)的設(shè)計(jì)原則，理應(yīng)表A和表B的ID一一對(duì)應(yīng)。既然是題，我們只能從當(dāng)前的條件入手了，攻破難點(diǎn)的關(guān)鍵在于判斷A中ID在B中ID出現(xiàn)與否，如果出現(xiàn)那么如何統(tǒng)計(jì)出現(xiàn)的次數(shù)。判斷出現(xiàn)與否需要用到函數(shù)CHARINDEX。

解決方案如下：

--創(chuàng)建測(cè)試數(shù)據(jù)
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為行政區(qū)域碼（六位數(shù)字，前兩代表省級(jí)，中間兩位代表市級(jí)，最后兩位代表縣級(jí)，不考慮xx00xx情況），city為城市名稱，CCode為該城市所屬的省級(jí)或者市級(jí)行政區(qū)域碼。

現(xiàn)需求如下結(jié)果：

分析：分析表city，code的含義十分明顯，所需要的結(jié)果也很明顯，如果是省就是顯示省份；是市則顯示為所屬省級(jí)+市級(jí)；是縣級(jí)則顯示為所屬省級(jí)+所屬市級(jí)+縣級(jí)。貌似可以用題目一分析中提到的SQL條件語(yǔ)句實(shí)現(xiàn)，但是轉(zhuǎn)念一想，還是有差別，這里需要先判斷city屬于省級(jí)？市級(jí)？縣級(jí)？然后在對(duì)應(yīng)起來(lái)的，這樣還得有參照表，復(fù)雜了。回到結(jié)果表中來(lái)進(jìn)行分析，其實(shí)判定city屬于省市縣的問題并不難，code的含義已經(jīng)說(shuō)明了，只要轉(zhuǎn)換表述：在表City中，當(dāng)code的后四位為“0000”時(shí)，肯定是省級(jí)；當(dāng)code的后兩位為“00”，并且后四位不為“0000”時(shí)，肯定是市級(jí)；當(dāng)code后兩位不為“00”時(shí)，為縣級(jí)。這樣省市縣的判定就一目了然了，然后，根據(jù)市級(jí)編碼追朔所屬的省級(jí)，并得出所屬省級(jí)+市級(jí)，縣級(jí)追朔所屬的市級(jí)，得出所屬省級(jí)+所屬市級(jí)+所屬縣級(jí)，通過(guò)運(yùn)用這種簡(jiǎn)單的遞歸思想，解決方案便躍然紙上了。

解決方案如下：

--測(cè)試數(shù)據(jù)
with ta as
(select '110000' as code, '北京市' city, '110000' Ccode
union
select N'110200', N'西城區(qū)', N'110200'
union
select N'110300', N'崇文區(qū)', N'110300'
union
select N'430000', N'湖南省', N'430000'
union
select N'430100', N'長(zhǎng)沙市', N'430100'
union
select N'430101', N'望城縣', N'430100')

select * into City from ta

select * from City;

--解決方案
with ta
as(
--省級(jí)
select code,city,Ccode,city content from City where right(code,4)='0000'),
tb as(
--市級(jí)
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

通過(guò)上述幾道小題，常思常新，溫故了SQL的部分知識(shí)，當(dāng)然方法很多，變式很多，如題目二統(tǒng)計(jì)表B中每個(gè)Task的人數(shù)等。不足之處，歡迎各位指點(diǎn)！

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com