-
矫正数据,有以下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 -
竖变横
-- 年季度销售
-- 1991111
-- 1991212
-- 1991313
-- 1991414
-- 1992121
-- 1992222
-- 1992323
-- 1992424
-- 查询结果
-- 年一季度二季度三季度四季度
-- 199111121314
-- 199221222324select 年,
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 年;
-