常見(jiàn)的sql練習(xí)題可以訓(xùn)練一下你的思路，個(gè)人感覺(jué)還不錯(cuò)！不妨做做試試看自己的水平怎么樣了，后續(xù)會(huì)把自己的面試題更新到上面去。
1. 已經(jīng)知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查詢的到下面的結(jié)果，就是累積工資
year salary
2000 1000
2001 3000
2002 6000
2003 10000
思路：這個(gè)需要兩個(gè)表交叉查詢得到當(dāng)前年的所有過(guò)往年，然后再對(duì)過(guò)往年進(jìn)行聚合。代碼如下：
create table #salary(years int ,salary int )
insert into #salary values
(2000, 1000),
(2001, 2000),
(2002, 3000),
(2003, 4000)
select b.years,SUM(a.salary)
from #salary a,#salary b
where a.years<=b.years
group by b.years
order by b.years
還有一種方法是使用子查詢，第一列是年，第二列是所有小于等于第一列這年的工資總和，也比較直接，代碼如下：
select
s1.years as years,
(select sum(s2.salary) from #salary s2 where s2.years<=s1.years) as salary
from #salary s1
2. 現(xiàn)在我們假設(shè)只有一個(gè)table，名為pages，有四個(gè)字段，id, url,title,body。里面儲(chǔ)存了很多網(wǎng)頁(yè)，網(wǎng)頁(yè)的url地址，title和網(wǎng)頁(yè)的內(nèi)容，然后你用一個(gè)sql查詢將url匹配的排在最前， title匹配的其次，body匹配最后，沒(méi)有任何字段匹配的，不返回。
思路：做過(guò)模糊搜索對(duì)這個(gè)應(yīng)該很熟悉的，可以使用union all依次向一個(gè)臨時(shí)表中添加記錄。這里使用order by和charindex來(lái)是實(shí)現(xiàn)，代碼如下：
create table #page(id int, url varchar(100),title varchar(100), body varchar(100))
insert into #page values
(1,null,'abcde','abcde'),
(2,null,'abcde',null),
(3,'abcde','e',null)
select *
from #page
where url like '%e%' or title like '%e%' or body like '%e%'
order by
case when (charindex('e', url)>0) then 1 else 0 end desc,
case when (charindex('e', title)>0) then 1 else 0 end desc,
case when (charindex('e', body)>0) then 1 else 0 end desc
只要出現(xiàn)一次就會(huì)排在前面，這種情況如果兩行都出現(xiàn)就會(huì)比較下一個(gè)字段，以此類推。
還有一種實(shí)現(xiàn)，類似于記分牌的思想，如下：
select a.[id],sum(a.mark) as summark from
(
select #page.*,10 as mark from #page where #page.[url] like '%b%'
union
select #page.*,5 as mark from #page where #page.[title] like '%b%'
union
select #page.*,1 as mark from #page where #page.[body] like '%b%'
) as a group by id order by summark desc
3. 表內(nèi)容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負(fù)
2005-05-09 負(fù)
2005-05-10 勝
2005-05-10 負(fù)
2005-05-10 負(fù)
如果要生成下列結(jié)果, 該如何寫sql語(yǔ)句?
勝 負(fù)
2005-05-09 2 2
2005-05-10 1 2
思路：首先要有g(shù)roup by 時(shí)間，然后是使用sum統(tǒng)計(jì)勝負(fù)的個(gè)數(shù)。代碼如下：
create table #scores(dates varchar(10),score varchar(2))
insert into #scores values
('2005-05-09', '勝'),
('2005-05-09', '勝'),
('2005-05-09', '負(fù)'),
('2005-05-09', '負(fù)'),
('2005-05-10', '勝'),
('2005-05-10', '負(fù)'),
('2005-05-10', '負(fù)')
select a.dates as [比賽時(shí)間],
SUM(case a.score when '勝' then 1 else 0 end) as [勝],
SUM(case a.score when '負(fù)' then 1 else 0 end) as [負(fù)]
from #scores a
group by a.dates
還有一種方法是使用子查詢，先用兩個(gè)子查詢得到這些日期中的勝負(fù)常數(shù)，然后連接查詢，代碼如下：
select
t1.dates as [比賽時(shí)間],
t1.score as [勝],
t2.score as [負(fù)]
from
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='勝' group by a.dates) t1 inner join
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='負(fù)' group by a.dates) t2 on t1.dates=t2.dates
4. 表中有A B C三列,用SQL語(yǔ)句實(shí)現(xiàn)：當(dāng)A列大于B列時(shí)選擇A列否則選擇B列，當(dāng)B列大于C列時(shí)選擇B列否則選擇C列
思路：這個(gè)字面意思很簡(jiǎn)單了，就是二者選其一，使用case就可以實(shí)現(xiàn)，代碼如下：
create table #table3(A int, B int ,C int)
insert into #table3 values
(2,1,3),
(4,2,5)
select
case when A>B then A else B end as AB,
case when B>C then B else C end as BC
from #table3
5. 請(qǐng)用一個(gè)sql語(yǔ)句得出結(jié)果
從table1,table2中取出如table3所列格式數(shù)據(jù)，注意提供的數(shù)據(jù)及結(jié)果不準(zhǔn)確，只是作為一個(gè)格式向大家請(qǐng)教。
table1
月份 部門 業(yè)績(jī)
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部門 部門名稱
01 國(guó)內(nèi)業(yè)務(wù)一部
02 國(guó)內(nèi)業(yè)務(wù)二部
03 國(guó)內(nèi)業(yè)務(wù)三部
04 國(guó)際業(yè)務(wù)部
table3 （result）
部門 部門名稱 一月份 二月份 三月份
01 國(guó)內(nèi)業(yè)務(wù)一部 10 null null
02 國(guó)內(nèi)業(yè)務(wù)二部 10 8 null
03 國(guó)內(nèi)業(yè)務(wù)三部 null 5 8
04 國(guó)際業(yè)務(wù)部 null null 9
思路：又是行列轉(zhuǎn)換，不過(guò)這個(gè)稍微復(fù)雜一點(diǎn)代碼如下：
create table #table4([月份] varchar(10),[部門] varchar(10),[業(yè)績(jī)] int)
insert into #table4 values
('一月份','01','10'),
('一月份','02','10'),
('一月份','03','5'),
('二月份','02','8'),
('二月份','04','9'),
('三月份','03','8')
create table #table5([部門] varchar(10),[部門名稱] varchar(50))
insert into #table5 values
('01','國(guó)內(nèi)業(yè)務(wù)一部'),
('02','國(guó)內(nèi)業(yè)務(wù)二部'),
('03','國(guó)內(nèi)業(yè)務(wù)三部'),
('04','國(guó)際業(yè)務(wù)部')
select [部門],[部門名稱],[一月份],[二月份],[三月份]
from(select a.[月份] ,a.[部門] as [部門],b.[部門名稱],a.[業(yè)績(jī)] from #table4 a join #table5 b on a.[部門]=b.[部門] ) sod
pivot(min(sod.[業(yè)績(jī)]) for sod.[月份] in([一月份],[二月份],[三月份])) pvt
order by [部門]
注意，這里每個(gè)月份每個(gè)部門只有一行數(shù)據(jù)，所以pivot運(yùn)算的時(shí)候可以使用min函數(shù)，使用max，min都可以。如果這里有多行數(shù)據(jù)，那么一般會(huì)讓計(jì)算合計(jì)，只能用sum了
還有一種方法是使用子查詢，這個(gè)代碼要多一點(diǎn)，如下：
select a.[部門] ,b.[部門名稱],
SUM(case when a.[部門]='01' then a.[業(yè)績(jī)] else 0 end) as [一月份],
SUM(case when a.[部門]='02' then a.[業(yè)績(jī)] else 0 end) as [二月份],
SUM(case when a.[部門]='03' then a.[業(yè)績(jī)] else 0 end) as [三月份],
SUM(case when a.[部門]='04' then a.[業(yè)績(jī)] else 0 end) as [四月份],
SUM(case when a.[部門]='05' then a.[業(yè)績(jī)] else 0 end) as [伍月份]
from #table4 a inner join #table5 b on a.[部門] =b.[部門]
group by a.[部門],b.[部門名稱]
6. 表結(jié)構(gòu)以及數(shù)據(jù)如下：
CREATE TABLE #table6
(ID int, 日期 varchar(11), 單據(jù) char(3))
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 4 , '2004-09-02' , '002' );
要求:設(shè)計(jì)一個(gè)查詢，返回結(jié)果如下：
ID 日期 單據(jù)
1 2004-08-02 001
4 2004-09-02 002
思路：這個(gè)是要找到日期比較小的那一條單據(jù)，這個(gè)有多種方法實(shí)現(xiàn)。第一種方法是相關(guān)子查詢，如下：
create table #table6
(id int, 日期varchar(11), 單據(jù)char(3))
insert into #table6 (id , 日期, 單據(jù)) values ( 1 , '2004-08-02' , '001' );
insert into #table6 (id , 日期, 單據(jù)) values ( 2 , '2004-09-02' , '001' );
insert into #table6 (id , 日期, 單據(jù)) values ( 3 , '2004-10-02' , '002' );
insert into #table6 (id , 日期, 單據(jù)) values ( 4 , '2004-09-02' , '002' );
select * from #table6 a
where a.[日期] = (select MIN(b.[日期]) from #table6 b where b.[單據(jù)] =a.[單據(jù)] )
還可以使用join連接，如下：
select a.*
from #table6 a join
(select b.[單據(jù)] , MIN(b.[日期]) as [日期] from #table6 b group by b.[單據(jù)]) c
on a.[日期] = c.[日期] and a.[單據(jù)] = c.[單據(jù)]
注意最后on條件必須是a.[日期] = c.[日期] and a.[單據(jù)] = c.[單據(jù)]，因?yàn)閏表只是找出來(lái)兩組符合條件的數(shù)據(jù)，如果只是a.[日期] = c.[日期]的話會(huì)找出多條不符合要求的數(shù)據(jù)。
還可以不適用join連接，如下：
select a.*
from #table6 a ,
(select b.[單據(jù)] , MIN(b.[日期]) as [日期] from #table6 b group by b.[單據(jù)]) c
where a.[日期] = c.[日期] and a.[單據(jù)] = c.[單據(jù)]
還可以使用謂詞exist，如下：
select * from #table6 a
where not exists
(select 1 from #table6 where [單據(jù)]=a.[單據(jù)] and a.[日期]>[日期])
注意not exists查詢篩選得到時(shí)間最小的那條記錄，注意這里不能使用exists，exists會(huì)得到多條?？梢岳斫鉃閍中的日期不會(huì)大于子查詢中所有日期，就是那個(gè)最小的日期。
7. 已知下面的表
id strvalue type
1 how 1
2 are 1
3 you 1
4 fine 2
5 thank 2
6 you 2
要求用sql把它們搜索出來(lái)成為這樣的
#how are you#fine thank you#
思路：這個(gè)和上一篇中的最后一題很相似，也是連接有相同字段的字符，上回使用游標(biāo)實(shí)現(xiàn)的，這次用for xml來(lái)實(shí)現(xiàn)，代碼如下：
create table #table7(id int,strvalue varchar(20),typ int)
insert into #table7 values
(1,'how',1),
(2,'are',1),
(3,'you',1),
(4,'fine',2),
(5,'thank',2),
(6,'you',2)
select * from #table7
select
(select '#'+replace(replace((select strvalue from #table7 t where typ = 1 for xml auto),'<t strvalue="',' '),'"/>', ' ')+'#')
+
(select replace(replace((select strvalue from #table7 t where typ = 2 for xml auto),'<t strvalue="',' '),'"/>', ' ')+'#')
for xml是好東西啊，是解決這類字符連接問(wèn)題的利刃
