SQL点滴26—常见T-SQL面试解析
?
它山之石可以攻玉，這一篇是讀別人的博客后寫下的，不是原原本本的轉載，加入了自己的分析過程和演練。sql語句可以解決很多的復雜業務，避免過多的項目代碼，下面幾個語句很值得玩味。
1. 已經知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查詢的到下面的結果，就是累積工資
year salary
2000 1000
2001 3000
2002 6000
2003 10000
思路：這個需要兩個表交叉查詢得到當前年的所有過往年，然后再對過往年進行聚合。代碼如下：
?
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
?
還有一種方法是使用子查詢，第一列是年，第二列是所有小于等于第一列這年的工資總和，也比較直接，代碼如下：
?
?
selects1.years as years,
(select sum(s2.salary) from #salary s2 where s2.years<=s1.years) as salary
from #salary s1
?
?
2. 現在我們假設只有一個table，名為pages，有四個字段，id, url,title,body。里面儲存了很多網頁，網頁的url地址，title和網頁的內容，然后你用一個sql查詢將url匹配的排在最前， title匹配的其次，body匹配最后，沒有任何字段匹配的，不返回。
?
思路：做過模糊搜索對這個應該很熟悉的，可以使用union all依次向一個臨時表中添加記錄。這里使用order by和charindex來是實現，代碼如下：
?
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
只要出現一次就會排在前面，這種情況如果兩行都出現就會比較下一個字段，以此類推。
還有一種實現，類似于記分牌的思想，如下：
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. 表內容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負
2005-05-09 負
2005-05-10 勝
2005-05-10 負
2005-05-10 負
如果要生成下列結果, 該如何寫sql語句?
?????????????????????? 勝負
2005-05-09? 2??? 2
2005-05-10??1??? 2
思路：首先要有group by 時間，然后是使用sum統計勝負的個數。代碼如下：
?
create table #scores(dates varchar(10),score varchar(2))insert into #scores values
('2005-05-09', '勝'),
('2005-05-09', '勝'),
('2005-05-09', '負'),
('2005-05-09', '負'),
('2005-05-10', '勝'),
('2005-05-10', '負'),
('2005-05-10', '負')
select a.dates as [比賽時間],
SUM(case a.score when '勝' then 1 else 0 end) as [勝],
SUM(case a.score when '負' then 1 else 0 end) as [負]
from #scores a
group by a.dates
?
還有一種方法是使用子查詢，先用兩個子查詢得到這些日期中的勝負常數，然后連接查詢，代碼如下：
?
?
selectt1.dates as [比賽時間],
t1.score as [勝],
t2.score as [負]
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='負' group by a.dates) t2 on t1.dates=t2.dates
?
?
?
4. 表中有A B C三列,用SQL語句實現：當A列大于B列時選擇A列否則選擇B列，當B列大于C列時選擇B列否則選擇C列
?
思路：這個字面意思很簡單了，就是二者選其一，使用case就可以實現，代碼如下：
?
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. 請用一個sql語句得出結果
從table1,table2中取出如table3所列格式數據，注意提供的數據及結果不準確，只是作為一個格式向大家請教。
table1
月份????????? 部門業績
一月份????? 01????? 10
一月份????? 02????? 10
一月份????? 03????? 5
二月份????? 02????? 8
二月份????? 04????? 9
三月份????? 03????? 8
?
table2
部門?????部門名稱
01????? 國內業務一部
02????? 國內業務二部
03????? 國內業務三部
04????? 國際業務部
?
table3 （result）
部門部門名稱? 一月份????? 二月份????? 三月份
? 01? 國內業務一部??? 10??????? null????? null
? 02?? 國內業務二部?? 10???????? 8??????? null
??03?? 國內業務三部?? null?????? 5??????? 8
? 04?? 國際業務部?? null????? null????? 9
思路：又是行列轉換，不過這個稍微復雜一點代碼如下：
?
create table #table4([月份] varchar(10),[部門] varchar(10),[業績] 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','國內業務一部'),
('02','國內業務二部'),
('03','國內業務三部'),
('04','國際業務部')
select [部門],[部門名稱],[一月份],[二月份],[三月份]
from(select a.[月份] ,a.[部門] as [部門],b.[部門名稱],a.[業績] from #table4 a join #table5 b on a.[部門]=b.[部門] ) sod
pivot(min(sod.[業績]) for sod.[月份] in([一月份],[二月份],[三月份])) pvt
order by [部門]
注意，這里每個月份每個部門只有一行數據，所以pivot運算的時候可以使用min函數，使用max，min都可以。如果這里有多行數據，那么一般會讓計算合計，只能用sum了
還有一種方法是使用子查詢，這個代碼要多一點，如下：
?
?
select a.[部門] ,b.[部門名稱],SUM(case when a.月份='一月份' then a.[業績] else 0 end) as [一月份],
SUM(case when a.月份='二月份' then a.[業績] else 0 end) as [二月份],
SUM(case when a.月份='三月份' then a.[業績] else 0 end) as [三月份]
from #table4 a inner join #table5 b on a.[部門] =b.[部門]group by a.[部門],b.[部門名稱]
6. 表結構以及數據如下：
?
CREATE TABLE #table6
?
(ID int, 日期 varchar(11), 單據 char(3))
?
INSERT INTO 表 (ID , 日期 , 單據 ) VALUES ( 1 , '2004-08-02' , '001' );
?
INSERT INTO 表 (ID , 日期 , 單據 ) VALUES ( 2 , '2004-09-02' , '001' );
?
INSERT INTO 表 (ID , 日期 , 單據 ) VALUES ( 3 , '2004-10-02' , '002' );
?
INSERT INTO 表 (ID , 日期 , 單據 ) VALUES ( 4 , '2004-09-02' , '002' );
?
要求:設計一個查詢，返回結果如下：
?
ID 日期??????單據
?
1 2004-08-02 001
?
4 2004-09-02 002
?
思路：這個是要找到日期比較小的那一條單據，這個有多種方法實現。第一種方法是相關子查詢，如下：
?
create table #table6(id int, 日期varchar(11), 單據char(3))
insert into #table6 (id , 日期, 單據) values ( 1 , '2004-08-02' , '001' );
insert into #table6 (id , 日期, 單據) values ( 2 , '2004-09-02' , '001' );
insert into #table6 (id , 日期, 單據) values ( 3 , '2004-10-02' , '002' );
insert into #table6 (id , 日期, 單據) values ( 4 , '2004-09-02' , '002' );
select * from #table6 a
where a.[日期] = (select MIN(b.[日期]) from #table6 b where b.[單據] =a.[單據] )
還可以使用join連接，如下：
select a.*from #table6 a join
(select b.[單據] , MIN(b.[日期]) as [日期] from #table6 b group by b.[單據]) c
on a.[日期] = c.[日期] and a.[單據] = c.[單據]
注意最后on條件必須是a.[日期] = c.[日期] and a.[單據] = c.[單據]，因為c表只是找出來兩組符合條件的數據，如果只是a.[日期] = c.[日期]的話會找出多條不符合要求的數據。
還可以不使用join連接，如下：
select a.*from #table6 a ,
(select b.[單據] , MIN(b.[日期]) as [日期] from #table6 b group by b.[單據]) c
where a.[日期] = c.[日期] and a.[單據] = c.[單據]
還可以使用謂詞exist，如下：
select * from #table6 awhere not exists
(select 1 from #table6 where [單據]=a.[單據] and a.[日期]>[日期])
注意not exists查詢篩選得到時間最小的那條記錄，注意這里不能使用exists，exists會得到多條。可以理解為a中的日期不會大于子查詢中所有日期，就是那個最小的日期。還有去掉[單據]=a.[單據],也會得到更多的數據，這個和普通的情況剛好相反。因為加上這個條件整個子查詢會得到更多的數據，否則只保留a.[日期]>[日期]只會得到一條數據。
?
?
7. 已知下面的表
?
id? strvalue type
?
1??? how????? 1
?
2??? are????? 1
?
3??? you????? 1
?
4??? fine???? 2
?
5??? thank??? 2
?
6??? you????? 2
?
要求用sql把它們搜索出來成為這樣的
?
#how are you#fine thank you#
?
思路：這個和上一篇中的最后一題很相似，也是連接有相同字段的字符，上回使用游標實現的，這次用for xml來實現，代碼如下：
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="',''),'"/>', '')+'#')
或者這樣
select '#'+ltrim((select ''+a.strvalue from #table7 a where a.typ=1 for xml path('')))+'#'+
ltrim((select ''+a.strvalue from #table7 a where a.typ=2 for xml path('')))+'#'
或者這樣，用變量來處理
?
declare @value varchar(1000)='#'select @value=''+@value+ a.strvalue+'' from #table7 a where a.typ=1
select @value=@value+'#'
select @value= @value+ a.strvalue+'' from #table7 a where a.typ=2
select @value=@value+'#'
print @value
?
for xml是好東西啊，是解決這類字符連接問題的利刃
?
?
?
posted on 2014-12-03 17:08 NET未來之路 閱讀(...) 評論(...) 編輯 收藏轉載于:https://www.cnblogs.com/lonelyxmas/p/4140582.html
總結
以上是生活随笔為你收集整理的SQL点滴26—常见T-SQL面试解析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 下载，安装 Source Naviga
- 下一篇： 论文模板的定制