面试题四:手写sql
-
矫正数据,有以下2个表,建表语句如下所示
-- 订单表 create table t_order ( id int auto_increment primary key, name varchar(255) null, total int null ); -- 插入数据 insert into sql_test.t_order (id, name, total) values (1, '家电', 1300); insert into sql_test.t_order (id, name, total) values (2, '洗漱', 170); insert into sql_test.t_order (id, name, total) values (3, '餐饮', 200); -- 详情表 create table t_detail ( id int auto_increment primary key, detail varchar(255) null, cost int null, order_id int null ); -- 插入数据 insert into sql_test.t_detail (id, detail, cost, order_id) values (1, '洗衣机', 500, 1); insert into sql_test.t_detail (id, detail, cost, order_id) values (2, '电视机', 800, 1); insert into sql_test.t_detail (id, detail, cost, order_id) values (3, '牙膏', 100, 2); insert into sql_test.t_detail (id, detail, cost, order_id) values (4, '洗衣液', 70, 2); insert into sql_test.t_detail (id, detail, cost, order_id) values (5, '白菜', 200, 3);
由于故障导致
t_order
表中的total
值出现异常,使用一个sql语句进行矫正;update t_order o, (select order_id as oid, sum(cost) as t from t_detail GROUP BY order_id) b set o.total = b.t where o.id = b.oid;
-
分类求和题,有表如下
create table t_type ( id int auto_increment primary key, type int null, num int null ); -- 插入数据 insert into sql_test.t_type (id, type, num) values (1, 1, 100); insert into sql_test.t_type (id, type, num) values (2, 1, 200); insert into sql_test.t_type (id, type, num) values (3, 2, 500); insert into sql_test.t_type (id, type, num) values (4, 2, 200); insert into sql_test.t_type (id, type, num) values (5, 3, 300); insert into sql_test.t_type (id, type, num) values (6, 3, 180); insert into sql_test.t_type (id, type, num) values (7, 4, 50); insert into sql_test.t_type (id, type, num) values (8, 5, 60); insert into sql_test.t_type (id, type, num) values (9, 6, 70);
要求:当
type>3
时type=8
,并且分类求和,要达到的效果如下:type sum 1 300 2 700 3 480 8 180 实现sql语句,需要使用到
case when xxx then xxx else xxx end
语句:select case when type > 3 then 8 else type end as t, sum(case when type > 3 then num else num end) from t_type group by t;
-
学生成绩相关
-- 学生表 create table student( id int unsigned primary key auto_increment, name char(10) not null ); insert into student(name) values('张三'),('李四'); -- 课程表 create table course( id int unsigned primary key auto_increment, name char(20) not null ); insert into course(name) values('语文'),('数学'); -- 学生成绩表 create table student_course( sid int unsigned, cid int unsigned, score int unsigned not null, foreign key (sid) references student(id), foreign key (cid) references course(id), primary key(sid, cid) ); insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);
-
查询重名的学生,按照name,id升序
select id,name from student where name in (select name c from student group by name HAVING count(name) > 1) order by name,id; -- exits写法 select t.id,t.name from student t where EXISTS (select s.name from student s where s.name = t.name GROUP BY name HAVING count(s.name) > 1 ) order by t.name,t.id;
-
在student_course表中查询平均分不及格的学生,列出学生id和平均分
select sid, AVG(score) as a from student_course GROUP BY sid HAVING a < 60;
-
在student_course表中查询每门课成绩都不低于80的学生id
select DISTINCT sid from student_course where sid not in (select sid from student_course where score < 80);
-
查询每个学生的总成绩,结果列出学生姓名和总成绩
select s.name, sum(c.score) from student_course c, student s where c.sid = s.id GROUP BY sid; -- 上述方法会过滤掉没有成绩的人,因此需要使用左连接 select name,sum(score) from student left join student_course on student.id=student_course.sid group by sid;
-
总成绩最高的学生,结果列出学生id和总成绩
select sid, sum(score) as ss from student_course GROUP BY sid order by ss desc limit 1;
-
在student_course表查询课程1成绩第2高的学生,如果第2高的不止一个则列出所有的学生
select * from student_coursewhere cid=1 and score = ( select score from student_course where cid = 1 group by score order by score desc limit 1,1 );
-
在student_course表查询各科成绩最高的学生,结果列出学生id、课程id和对应的成绩
select * from student_course as x where score>= (select max(score) from student_course as y where cid=x.cid);
-
在student_course表中查询每门课的前2名,结果按课程id升序,同一课程按成绩降序
select * from student_course x where 2>(select count(distinct(score)) from student_course y where y.cid=x.cid and y.score>x.score) order by cid,score desc;
-
一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,两两进行比赛,用一条sql语句显示所有可能的比赛组合
select a.name, b.name from team a, team b where a.name < b.name
-
竖变横
-- 年 季度 销售 -- 1991 1 11 -- 1991 2 12 -- 1991 3 13 -- 1991 4 14 -- 1992 1 21 -- 1992 2 22 -- 1992 3 23 -- 1992 4 24 -- 查询结果 -- 年 一季度 二季度 三季度 四季度 -- 1991 11 12 13 14 -- 1992 21 22 23 24 select 年, sum(case when 季度=1 then 销售量 else 0 end) as 一季度, sum(case when 季度=2 then 销售量 else 0 end) as 二季度, sum(case when 季度=3 then 销售量 else 0 end) as 三季度, sum(case when 季度=4 then 销售量 else 0 end) as 四季度 from sales group by 年;
-