常见的sql面试题
1、一组通话记录（总共500万条）:
ID 主叫号码 被叫号码 通话起始时间 通话结束时间 通话时长
1 98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.00023
2 98290000 021546654666 2007-02-01 09:50:29.000 2007-02-0109:50:41.000 12
3 98290000 021546654666 2007-02-01 09:50:58.000 2007-02-0109:51:12.000 14
4 68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000162
5 78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.00057
6 78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000196
7 78290000 035730928370 2007-02-01 11:30:45.000 2007-02-0111:31:58.000 73
8 78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.00073
9 68290000 035730928379 2007-02-01 11:52:20.000 2007-02-0111:54:56.000 156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19
求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
1 create table tonghua 2 ( 3 ID int , 4 主叫号码 varchar(15), 5 被叫号码 varchar(15), 6 通话起始时间 datetime, 7 通话结束时间 datetime , 8 通话时长 int , 9 ) 10 truncate table tonghua 11 insert into tonghua 12 select 1 , '98290000 ', '0215466546656 ', '2007-02-01 09:49:53.000 ', '2007-02-01 09:50:16.000 ', 23 13 union all select 2, '98290000 ', '021546654666 ', '2007-02-01 09:50:29.000 ', '2007-02-01 09:50:41.000 ', 12 14 union all select 3, '98290000 ', '021546654666 ', '2007-02-01 09:50:58.000 ', '2007-02-01 09:51:12.000 ', 14 15 union all select 4, '68290000 ', '0755133329866 ', '2007-02-01 10:04:31.000 ', '2007-02-01 10:07:13.000 ', 162 16 union all select 5, '78290000 ', '0755255708638 ', '2007-02-01 10:48:26.000 ', '2007-02-01 10:49:23.000 ', 57 17 union all select 6, '78290000 ', '0755821119109 ', '2007-02-01 10:49:39.000 ', '2007-02-01 10:52:55.000 ', 196 18 union all select 7, '78290000 ', '035730928370 ', '2007-02-01 11:30:45.000 ', '2007-02-01 11:31:58.000 ', 73 19 union all select 8, '78290000 ', '0871138889904 ', '2007-02-01 11:33:47.000 ', '2007-02-01 11:35:00.000 ', 73 20 union all select 9, '68290000 ', '035730928379 ', '2007-02-01 11:52:20.000 ', '2007-02-01 11:54:56.000 ', 156 21 union all select 10, '68290000 ', '0298521811199 ', '2007-02-01 12:44:45.000 ', '2007-02-01 12:45:04.000 ', 19
以上是表的创建
1 SELECT A.* FROM tonghua AS A INNER JOIN tonghua AS B ON A.ID = B.ID-1 2 WHERE A.主叫号码=B.主叫号码 AND DATEDIFF(MINUTE,A.通话结束时间,B.通话起始时间)> 10 UNION SELECT B.* FROM tonghua AS B INNER JOIN tonghua AS A ON A.ID = B.ID-1 3 WHERE A.主叫号码=B.主叫号码 AND DATEDIFF(MINUTE,A.通话结束时间,B.通话起始时间)> 10
1 select a.*,b.* from 2 tonghua a,tonghua b where b.id> a.id and b.id=(select min(id) from tonghua where id> a.id 3 and (a.主叫号码=b.主叫号码 or a.主叫号码=b.被叫号码 or a.被叫号码=b.主叫号码 or a.被叫号码=b.被叫号码)) 4 and DATEDIFF(minute,a.通话结束时间,b.通话起始时间)>=10 5 and DATEDIFF(second,a.通话结束时间,b.通话起始时间)>=10
以上是我认为正确的方法。
2、用一条select语句求出所有课程在80分（含80分）以上的学生姓名，请写出所有可行方案。
1 use QQmdb 2 go 3 if OBJECT_ID('sc') is not null 4 drop table sc 5 create table sc 6 ( 7 name char(10), 8 kc char(10), 9 score decimal(5,2) 10 ) 11 12 insert into sc 13 values('张三','语文',80), 14 ('张三','数学',81), 15 ('张三','英语',100), 16 ('李四','语文',90), 17 ('李四','数学',80), 18 ('李四','英语',70), 19 ('王五','语文',100), 20 ('王五','数学',100), 21 ('王五','英语',79)
以上是表的创建
1 select name from sc group by name having min(score)>=80
1 select name from sc where score >=80 group by name having COUNT(score)>=3 --先根分数大于80分的姓名分组，因为是3科，所以要分数count>3
1 select distinct a.name from sc a,sc b, sc c where a.name=b.name and b.name=c.name and a.name=c.name and a.kc<>b.kc and a.kc<>c.kc and b.kc<>c.kc and a.score>=80 and b.score>=80 and c.score>=80
1 select distinct name from sc where name not in (select name from sc where score<80) --不推荐用这种
3、分别要求出每个team粉丝数最多的3个国家
fans(team,nationality,fanCount)
'Barcelona','Germany',12000
'Barcelona','Spain',18000
'Barcelona','China',13000
'Barcelona','USA',15000
'Barcelona','Italy',11000
'Real Madrid','France',19000
'Real Madrid','Germany',13000
'Real Madrid','USA',17000
'Real Madrid','Korea',16000
'Real Madrid','UK',12000
1 select * from fans 表 where 2 ( select count(*) from fans t2 where t1.team=t2.team 3 and t2.fanCount>=t1.fanCount ) <=3 --子查询较牺牲性能
还有一些借书问题，根据书号，时间，做的一些查询。。。
