常见的sql面试题

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  --子查询较牺牲性能

展开代码

还有一些借书问题，根据书号，时间，做的一些查询。。。

常见的sql面试题

标签：