MSSQL綜合技術補習---常見筆試題1
2012-03-16 00:28 海不是藍 閱讀(1349) 評論(5) 收藏 舉報|
為什么寫這篇文章 |
本人最近在找工作，各種面試筆試，很多知識點久了都快忘記了，特別SQL也很久沒有寫了。
為了快速系統的補習下SQL相關的知識，決定寫“MSSQL綜合技術補習”系列文章。開始幾篇為常見sql面試題型，然后慢慢轉向系統的sql知識。
|
考點1---分組聚合條件統計(透視轉換) |
這樣的題先分組，然后根據條件用case語句來區分條件，最后再聚合條件列。
(雖然透視轉換可以使用SQL內置的Pivoting來實現，但是我覺得死語法沒有自己寫那樣思路清楚，所以我沒有使用內置的透視轉換)
透視轉換的步驟：
3個邏輯處理階段：1.分組 2.擴展 3.聚合
|
例題1 |
|
|
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 |
分析：先根據rq分組，然后根據shengfu擴展，最后再根據shengfu聚合
select
rq as 日期,
sum(case shengfu when '勝' then 1 else 0 end) as 勝,
sum(case shengfu when '負' then 1 else 0 end) as 負
from tmp
group by rq
分辨使用透視轉換的場景！
1.數據行是否轉換成列，例如勝負是shengfu這個列的2個狀態，而且展示結果勝負變成了2個列。
2.轉換結果列的數據行是否是聚合結果。
如果上面2個條件都符合，那么就可以使用透視轉換來做！
下面也是網上經常出現的sql面試題，我們用他來分析
|
table1 |
||
|
月份mon |
部門dep |
業績yj |
|
一月份 |
01 |
10 |
|
一月份 |
02 |
10 |
|
一月份 |
03 |
5 |
|
二月份 |
02 |
8 |
|
二月份 |
04 |
9 |
|
三月份 |
03 |
8 |
|
table2 |
|
|
部門dep |
部門名稱dname |
|
01 |
國內業務一部 |
|
02 |
國內業務二部 |
|
03 |
國內業務三部 |
|
04 |
國際業務部 |
請用sql得到下面的結果
|
table3 （result） |
|||
|
部門dep |
一月份 |
二月份 |
三月份 |
|
01 |
10 |
null |
null |
|
02 |
10 |
8 |
null |
|
03 |
null |
5 |
8 |
|
04 |
null |
null |
9 |
分析：table1中的mon列的數據1月份，2月份，3月份在table3里面變成了列，而這些列的數據是table1的yj列的sum聚合，所以這里只用透視轉換
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份'
from table2 a left join table1 b on a.dep=b.dep
注意下這里是left join，因為你不可能知道部門的所有id，所以用部門的表去left join業績表。
另外一個重點
就是在聚合數據的時候，需要考慮聚合數據是否存在NULL，如果存在，為了防止數字和NULL計算結果為NULL，應該使用ISNULL函數進行檢查。
select 1+null
select sum(1+null)
--結果為NULL
select 1+isnull(null,0)
select sum(1+isnull(null,0))
|
考點2---求符合聚合結果的數據(HAVING) |
這樣的題出現在面試題中的機會是很大的，題意的答案幾乎都是先分組聚合，再在分組集上使用Having尋找符合題意的數據。
下面給出個最經典的題目。
為管理崗位業務培訓信息，建立3個表:
|
S-學員表 |
|||
|
S#-學號 |
SN-學員姓名 |
SD-所屬單位 |
SA-學員年齡 |
|
C-課程表 |
|
|
C#-課程編號 |
CN-課程名稱 |
|
SC-成績表 |
||
|
S#-學號 |
C#-課程編號 |
G-學習成績 |
要求：
使用標準SQL嵌套語句查詢選修全部課程的學員姓名和所屬單位
做這樣的題目需要對Group by 和having有比較好的理解。
創建表和插入數據的sql
創建表
create table S
(
S# int identity(1,1) primary key,
SN nvarchar(20) not null,
SD nvarchar(20) not null,
SA int not null
)
create table C
(
C# int identity(1,1) primary key,
CN nvarchar(30) not null
)
create table SC
(
S# int not null,
C# int not null,
G float not null
)
--插入學生表
insert into S (SN,SD,SA) values ('小明','廣電河蟹部門',18)
insert into S (SN,SD,SA) values ('小花','有愛城管部門',18)
insert into S (SN,SD,SA) values ('小蛋','神秘有關部門',18)
--插入課程表
insert into C (CN) values ('數學')
insert into C (CN) values ('英語')
insert into C (CN) values ('語文')
insert into C (CN) values ('物理')
--插入成績表
insert into SC (S#,C#,G) values (1,1,1)
insert into SC (S#,C#,G) values (1,2,8)
insert into SC (S#,C#,G) values (1,3,6)
insert into SC (S#,C#,G) values (1,4,4)
insert into SC (S#,C#,G) values (2,3,0)
insert into SC (S#,C#,G) values (3,4,12)
分析：
實際應用當中我們一般是不知道狀態表會有多少數據的，也就是課程表，所以課程表的課程總數是應該在sql查詢中去得到，然后再結合group by 和having來實現解這種類型題的經典解法。
select * from S
where S# in
(
select SC.S# from SC right join C --注意這里能確定數據的表是課程表，所以用課程表來做連接主表
on SC.C#=C.C# --課程ID是連接的條件
group by SC.S# --根據學生ID分組
having count(distinct(SC.C#)) --注意一個同學可能存在一門課多個成績
=(select count(C#) from C)
)
另外幾個相關題目
列出有二門以上(含兩門)不及格課程的學生姓名及其平均成績
select S.S#,S.SN,SC1.SG from S join
(
select S#,avg(G) as SG from SC
where G<60 group by S#
having count(distinct C#)>=2
) as SC1
on S.S#=SC1.S#
列出既學過“數學”號課程，又學過“英語”號課程的所有學生姓名
select S.S# from S where S# in
(
select SC.S# from SC join C on SC.C#=C.C#
where C.CN in ('數學','英語')
group by SC.S#
having count(distinct C.CN)=2
)
列出“1”號課成績比“2”號課成績高的所有學生的學號及其“1”號課和“2”號課的成績
--這里要考慮到實際應用中可能出現一門課程多條成績，所以取最高的成績比較
select t1.S#,t1.CN,t1.G,t2.CN,t2.G from
(select top 1 SC1.S#,C1.CN,SC1.G from SC as SC1 join C as C1
on SC1.C#=C1.C# where C1.CN='英語' order by SC1.G desc)
as t1
join
(select top 1 SC2.S#,C2.CN,SC2.G from SC as SC2 join C as C2
on SC2.C#=C2.C# where C2.CN='數學' order by SC2.G desc)
as t2
on t1.S#=t2.S# where t1.G>t2.G
|
考點3 取出表中的第31條到40條記錄 |
取出表A中的第31條到40條記錄，ID這里肯定是被破壞了的，不可能讓你那么容易的得到。
這里主要是考你分頁的知識點，不過比較簡單，不多說了。
創建表
create table test1
(
id int identity(1,1) primary key,
number int
)
--插入數據
declare @i int
set @i=1
while @i<50
begin
insert into test1 (number) values(@i)
set @i=@i+1
end
--刪除幾行數據
delete from test1 where id=5
delete from test1 where id=11
delete from test1 where id=26
delete from test1 where id=37
delete from test1 where id=42
select* from test1
注意：雖然這里我們表中有ID和number這2個int字段，但是都是不完整的！而且題目不會讓你怎么簡單的去依靠連續體字段！
--錯誤的方案-注意order by
select top 10 * from
(
select top 40 * from test1
order by id desc
) as t order by t.id asc
--正確的方案1-結果降序
select top 10 * from
(
select top 40 * from test1
order by id asc
) as t order by t.id desc
--正確的方案2-結果升序
select top 10 * from test1
where id not in
(
select top 30 id from test1
order by id asc
) order by id asc
這里的第一個錯誤解決方案是因為最后一個order by把結果給升序了，導致出來的不是正確數據，關鍵在于order by比select 數據列先執行。
|
考點4 數據庫中重復的數據 |
這個題目就是超級經典的面試題了，而且解法比較多，這里我做個總結。
創建表
create table Student
(
id int identity(1,1) primary key,
name nvarchar(20) not null,
age int not null,
sex nchar not null
)
--插入數據
insert into Student (name,age,sex) values ('小明',10,'男')
insert into Student (name,age,sex) values ('小花',9,'女')
insert into Student (name,age,sex) values ('小蛋',3,'女')
insert into Student (name,age,sex) values ('小瓜',4,'男')
insert into Student (name,age,sex) values ('亞克西',15,'男')
insert into Student (name,age,sex) values ('哦買噶',12,'女')
insert into Student (name,age,sex) values ('雅蠛蝶',14,'女')
insert into Student (name,age,sex) values ('小花',9,'女')
insert into Student (name,age,sex) values ('二蛋',2,'男')
insert into Student (name,age,sex) values ('狗蛋',3,'男')
insert into Student (name,age,sex) values ('小明',11,'男')
insert into Student (name,age,sex) values ('小明',10,'男')
insert into Student (name,age,sex) values ('亞克西',15,'男')
|
4-1 查詢姓名相同所有重復數據(單字段重復) |
常見的思路：
1：通過子查詢去查詢每個姓名的分組數據總和，然后篩選大于1的分組。
2：使用exists查詢是否存在相同姓名不同ID的數據。
--判斷重復數據條數
select * from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name] group by S2.[name]
)>1
--判斷ID
select S1.* from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name] and S2.id<>S1.id
)
這2中方法屬于比較常見的，如果園友有其它更好的方法請告知。
|
4-2 查詢重復數據中最早創建的數據(單字段重復) |
分組已經篩選大于1的分組，然后select分組中最小的id
select * from Student where id in
(
select min(id) from Student
group by [name] having count(id)>1
)
|
4-3 查詢重復數據中非最早創建的數據(單字段重復) |
去掉重復數據中最早創建的數據，只查詢出后面創建的重復數據
1：使用row_number函數，根據重復字段name分區生成行號，去大于1的行號。
2：求出重復name最小的id，非最早創建的重復數據大于這個最小的id.
select * from Student where id in
(
select id from
(
select id,row_number() over(partition by [name] order by id)
as num from Student
) as t where num>1
)
select S1.* from Student as S1 where S1.id>
(
select min(S2.id) from Student as S2
where S2.[name]=S1.[name]
)
|
4-3 查詢多字段重復的數據(name,age,sex) |
這個和查詢單字段重復數據思想都是一樣，只是在判斷重復字段的地方要寫上需要判斷的所有字段，如果有更好的方法請聯系我謝謝。
--判斷重復數據條數
select * from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name]
and S2.age=S1.age
and S2.sex=S1.sex
group by S2.[name]
)>1
--判斷ID
select S1.* from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name]
and S2.age=S1.age
and S2.sex=S1.sex
and S2.id<>S1.id
)
|
4-5 刪除重復數據 |
要求：刪除全部重復數據
--判斷重復數據條數
delete from Student where id in
(
select id from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name] group by S2.[name]
)>1
)
--判斷ID
delete from Student where id in
(
select S1.id from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name] and S2.id<>S1.id
)
)
對于要求保留一條重復數據的題目，可以使用重復數據中最早創建的數據的SQL，獲取到id再刪除，多字段重復都是相同思路。
|
作者：海不是藍 博客：http://www.rzrgm.cn/hailan2012/ 郵箱：hailan2012@sina.com 本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責任的權利。 |
浙公網安備 33010602011771號