SQL經典面試題目（三）
第二十題：
怎么樣抽取重復記錄
表：
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
查出所有有重復記錄的數據，用一句sql 來實現
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('7','test7')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('8','test8')
insert into D values('10','test4')
select * from D where
--解法一:
--查詢出重復的記錄
select id,name from D group by id,name having count(*)>1
--查詢出重復的記錄及重復次數
select a.id,a.name from D a,(select id,name from D group by id,name having count(*)>1) b where a.id=b.id and a.name=b.name
--解法二:
select t1.* from D t1,
(select sum(1) as Sig,id,name from D group by id,name) as t2
where t1.name=t2.name and t1.id=t2.id and t2.Sig>1
第二十一題：
已知原表(t_salary)
year salary
2000 1000
2001 2000
2002 3000
2003 4000
先要實現顯示結果(salary為以前的工資和)
year salary
2000 1000
2001 3000
請問SQL語句怎么寫?
create table t_salary(
year int,
salary int
)
select * from t_salary
insert into t_salary values(2000,1000)
insert into t_salary values(2001,2000)
insert into t_salary values(2002,3000)
insert into t_salary values(2003,4000)
--解答：
select year, (select sum(salary) from t_salary b where b.year <= a.year) salary from t_salary a group by year
第二十二題：
year month total
1996 1 3000
1996 3 4000
1996 7 5000
1997 3 4000
1997 6 3000
.
要求按如下格式輸出：
year m1,m2,m3,m4
year 為年，m1等為季度，要求按上行輸出
create table Outputs (
year char(4),
month int,
total int
)
insert into Outputs values ('1996',1,3000)
insert into Outputs values ('1996',3,4000)
insert into Outputs values ('1996',7,5000)
insert into Outputs values ('1997',3,4000)
insert into Outputs values ('1997',6,3000)
insert into Outputs values ('1997',2,3000)
insert into Outputs values ('1998',1,3000)
insert into Outputs values ('1998',4,3500)
select * from Outputs
----------------解答-------------------
--解法一：
select year,
sum(case when month<=3 then total else 0 end) as M1,
sum(case when month>3 and month<=6 then total else 0 end) as M2,
sum(case when month>6 and month<=9 then total else 0 end) as M3,
sum(case when month>9 and month<=12 then total else 0 end) as M2
from Outputs group by year
--解法二：
select year,
sum(case when month in(1,2,3) then total else 0 end) as M1,
sum(case when month in(4,5,6) then total else 0 end) as M2,
sum(case when month in(7,8,9) then total else 0 end) as M3,
sum(case when month in(10,11,12) then total else 0 end) as M2
from Outputs group by year
第二十三題：
普通行列轉換
問題：假設有張學生成績表(tb)如下:
姓名 課程 分數
張三 語文 74
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94
想變成(得到如下結果)：
姓名 語文 數學 物理
---- ---- ---- ----
李四 74 84 94
張三 74 83 93
-------------------
create table tb(
姓名 varchar(10) ,
課程 varchar(10) ,
分數 int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
insert into tb values('李四' , '歷史' , 94)
go
-----解法一：SQL SERVER 2000 靜態SQL,指課程只有語文、數學、物理這三門課程。(以下同)---------
select 姓名,
max(case when 課程='語文' then 分數 end) as 語文,
max(case when 課程='數學' then 分數 end) as 數學,
max(case when 課程='物理' then 分數 end) as 物理
from tb
group by 姓名
-----解法二：--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同) ---------
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
-----解法三：SQL SERVER 2005 靜態SQL。
select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b
-----解法四：SQL SERVER 2005 靜態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b')
-----------------------------------
問題：在上述結果的基礎上加平均分，總分，得到如下結果：
姓名 語文 數學 物理 平均分 總分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
張三 74 83 93 83.33 250
--SQL SERVER 2000 靜態SQL。
select 姓名 姓名,
max(case 課程 when '語文' then 分數 else 0 end) 語文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理,
cast(avg(分數*1.0) as decimal(18,2)) 平均分,
sum(分數) 總分
from tb
group by 姓名
--SQL SERVER 2000 動態SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 靜態SQL。
select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (語文,數學,物理)) b) m,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 動態SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 課程 from tb group by 課程
exec ('select m.* , n.平均分 , n.總分 from
(select * from (select * from tb) a pivot (max(分數) for 課程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分數*1.0) as decimal(18,2)) 平均分 , sum(分數) 總分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
問題：如果上述兩表互相換一下：即表結構和數據為：
姓名 語文 數學 物理
張三 74 83 93
李四 74 84 94
想變成(得到如下結果)：
姓名 課程 分數
---- ---- ----
李四 語文 74
李四 數學 84
李四 物理 94
張三 語文 74
張三 數學 83
張三 物理 93
--------------
create table tb(姓名 varchar(10) , 語文 int , 數學 int , 物理 int)
insert into tb values('張三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 靜態SQL。
select * from
(
select 姓名 , 課程 = '語文' , 分數 = 語文 from tb
union all
select 姓名 , 課程 = '數學' , 分數 = 數學 from tb
union all
select 姓名 , 課程 = '物理' , 分數 = 物理 from tb
) t
order by 姓名 , case 課程 when '語文' then 1 when '數學' then 2 when '物理' then 3 end
--SQL SERVER 2000 動態SQL。
--調用系統表動態生態。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [課程] = ' + quotename(Name , '''') + ' , [分數] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名為姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 動態SQL。
select 姓名 , 課程 , 分數 from tb unpivot (分數 for 課程 in([語文] , [數學] , [物理])) t
--SQL SERVER 2005 動態SQL，同SQL SERVER 2000 動態SQL。
--------------------
問題：在上述的結果上加個平均分，總分，得到如下結果：
姓名 課程 分數
---- ------ ------
李四 語文 74.00
李四 數學 84.00
李四 物理 94.00
李四 平均分 84.00
李四 總分 252.00
張三 語文 74.00
張三 數學 83.00
張三 物理 93.00
張三 平均分 83.33
張三 總分 250.00
------------------
select * from
(
select 姓名 as 姓名 , 課程 = '語文' , 分數 = 語文 from tb
union all
select 姓名 as 姓名 , 課程 = '數學' , 分數 = 數學 from tb
union all
select 姓名 as 姓名 , 課程 = '物理' , 分數 = 物理 from tb
union all
select 姓名 as 姓名 , 課程 = '平均分' , 分數 = cast((語文 + 數學 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 課程 = '總分' , 分數 = 語文 + 數學 + 物理 from tb
) t
order by 姓名 , case 課程 when '語文' then 1 when '數學' then 2 when '物理' then 3 when '平均分' then 4 when '總分' then 5 end
第二十四題：
只用一條SQL語句,要求從左表查詢出右表!左表是查詢表,右表是要求查詢出的結果,并不是兩表聯合查詢.
左表： 右表：
ID NAME ID NAME
---------- ------------------
1 A5 1 A5,A8,AF....
2 A8 2 B5,B3,BD....
3 AF 3 C3,CK,CI....
4 B5
5 B3
6 BD
7 C3
8 CK
9 CI
create table leftTable(
id int indentity,
name varchar(20)
)
create table rightTable(
id int indentity,
name varchar(20)
)
insert into leftTable values('A5')
insert into leftTable values('A8')
insert into leftTable values('AF')
insert into leftTable values('B5')
insert into leftTable values('B3')
insert into leftTable values('BD')
insert into leftTable values('C3')
insert into leftTable values('CK')
insert into leftTable values('CI')
--本題答案征集中................
第二十五題：
--如何刪除SQL表中重復的記錄,除ID值不一樣外其它字段都一樣,每兩行記錄重復
create table duplicateTable(
id int IDENTITY,
name varchar(10),
class varchar(10),
address varchar(20),
nationality varchar(30)
)
insert into duplicateTable values('name1','class1','address1','nationality1')
insert into duplicateTable values('name2','class2','address2','nationality2')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name2','class2','address2','nationality2')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name3','class3','address3','nationality3')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name4','class4','address4','nationality4')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name5','class5','address5','nationality5')
insert into duplicateTable values('name6','class6','address6','nationality6')
insert into duplicateTable values('name7','class7','address7','nationality7')
--解答：
delete t from duplicateTable t where exists(select 1 from duplicateTable where name=t.name and class=t.class and address=t.address and nationality=t.nationality and id>t.id)
一天，一個月，一年。總有一天會變得不一樣。