面试遇到数据库题目总结

面试时遇到的sql题目，虽然很简单，但是当时想不出来怎么写，编写sql的能力还是有待提高啊，现在这里总结一下，以待日后复习使用，活到老，学到老！  

NO.1 想给一张表建立索引，但是这张表中存在重复数据，表结构如下(这里用sql为例)

create table fruit(
id int(11) primary key auto_increment,
    name varchar(20)
);

insert into fruit (`name`) values ('苹果');
insert into fruit (`name`) values ('梨子');
insert into fruit (`name`) values ('葡萄');
insert into fruit (`name`) values ('西瓜');
insert into fruit (`name`) values ('苹果');
insert into fruit (`name`) values ('西瓜');
insert into fruit (`name`) values ('西瓜');
insert into fruit (`name`) values ('苹果');
insert into fruit (`name`) values ('梨子');
insert into fruit (`name`) values ('苹果');

1 查找表中存在重复的字段

 思路：这里存在重复是指'name'属性存在重复，可以使用group by取出所有的name分组，

 取出有重复的，可以这样group by name having count(name) > 1

所以整条sql可以这样写

select * from fruit where name in (select name from fruit group by name having count(name) > 1)

2 对于有重复的字段，删除掉多余的，只保留一行

思路 存在重复的 但是还要保留一个记录，可以这样

1 取出有重复的id

select id from fruit where name in (select name from fruit group by name having count(name) > 1)

要保留一条，先找出这些有重复字段的第一条即可

select min(id) from fruit group by name having count(name) > 1)

于是整个语句可以是这样

delete from fruit where id  in 
(select a.id as id from 
(select id from fruit where id in 
(select id from fruit where name in (select name from fruit group by name having count(name) > 1)) and id not in (select min(id) from fruit group by name having count(name) > 1)) a);

理论上这样是可以，但是mysql却报了

Error Code: 1093. You can't specify target table 'fruit' for update in FROM clause

原因是mysql认为你从表中找出的集合删除不安全，必须把该集合看成一个临时表，再删除

delete from fruit where id  in 
(select a.id as id from 
(select id from fruit where id in 
(select id from fruit where name in (select name from fruit group by name having count(name) > 1)) and id not in (select min(id) from fruit group by name having count(name) > 1)) a);

NO.2 (这个题目是在论坛上碰到别人问的，顺便收藏一下)

这里的字段名是用中文的，我不习惯，改成英文了，建表语句如下：

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

insert into t_student (`name`, `course`, `score`) values ('张三', '数学', 9);
insert into t_student (`name`, `course`, `score`) values ('张三', '语文', 7);
insert into t_student (`name`, `course`, `score`) values ('张三', '英语', 4);
insert into t_student (`name`, `course`, `score`) values ('张三', '物理', 2);
insert into t_student (`name`, `course`, `score`) values ('李四', '数学', 2);
insert into t_student (`name`, `course`, `score`) values ('李四', '语文', 9);
insert into t_student (`name`, `course`, `score`) values ('李四', '英语', 8);
insert into t_student (`name`, `course`, `score`) values ('李四', '物理', 5);
insert into t_student (`name`, `course`, `score`) values ('王五', '数学', 1);
insert into t_student (`name`, `course`, `score`) values ('王五', '语文', 8);
insert into t_student (`name`, `course`, `score`) values ('王五', '英语', 8);
insert into t_student (`name`, `course`, `score`) values ('王五', '物理', 6);
insert into t_student (`name`, `course`, `score`) values ('赵六', '数学', 8);
insert into t_student (`name`, `course`, `score`) values ('赵六', '语文', 2);
insert into t_student (`name`, `course`, `score`) values ('赵六', '英语', 6);
insert into t_student (`name`, `course`, `score`) values ('赵六', '物理', 9);

3.1 咋一看这道题貌似挺简单，不就是一个求最大值的函数调用嘛，于是很多人就这么写

select name max(score) as score from t_student where course = '数学' order by score

得到的'分数'是正确的，但是姓名永远都是第一个人，至少在mysql中是这样，别的数据库没尝试，于是，在mysql中，正确的写法是这样

select name, score from t_student where course = '数学' and score = (select max(score) as score from t_student where course = '数学' order by score);

3.2这个有多中写法，我这里写出了两种，先保存下来，给日后复习使用

1 这个比较简单

select name, sum(score) as score from t_student group by name order by score desc limit 1;

2 可以用多表查询

select b.name as name, b.score as score from (select name, sum(score) as score from t_student group by name) b where b.score = 
(select max(a.score) as score from (select name, sum(score) as score from t_student group by name) a);

3.3 这个我暂时没想出来，但是有哥们是这么写的

select * from student t where t.学科='数学' and t.分数=(select max(t.分数) from student t where t.学科='数学');
with temp as(select t.姓名  name, sum(t.分数) score from student t group by t.姓名)
select * from temp where score=(select  max(score) from temp );
select  t.学科,
       sum(case t.姓名 when '李四' then t.分数 else 0 end) as 李四,
       sum(case t.姓名 when '张三' then t.分数  else 0 end) as 张三,
       sum(case t.姓名 when '张三' then t.分数  else 0 end) as 王五,
       sum(case t.姓名 when '张三' then t.分数  else 0 end) as 赵六       
       from student t group by t.学科;

这个写法还未实践，先上班，不过我觉得这种枚举拼凑的写法即使能完成功能，但，如果有一万个人，就要拼一万次了，不够优雅啊

有时间再整理