自己选择的路,摸爬滚打也要走下去

【SQL 笔试面试】SQL经典面试题目(三)

 

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)



一天,一个月,一年。总有一天会变得不一样。

posted on 2011-02-13 23:03 wokaoJune 阅读(3585) 评论(0)  编辑  收藏 所属分类: SQL 笔试面试


只有注册用户登录后才能发表评论。


网站导航:
 
<2011年2月>
303112345
6789101112
13141516171819
20212223242526
272812345
6789101112

导航

统计

公告

GO ,GO,GO
自己选择的路,摸爬滚打也要走下去

常用链接

留言簿

随笔分类(26)

随笔档案(29)

文章分类

最新随笔

搜索

最新评论

阅读排行榜

评论排行榜