sql学习及理解_4
本帖最后由 junkaiw 于 2020-12-8 18:49 编辑static/image/hrline/1.gifstatic/image/hrline/1.gif
目录：
1. 订单取消率计算
2. 多条件查询问题
3. 简单分组统计问题
static/image/hrline/1.gifstatic/image/hrline/1.gif
1. 订单取消率计算
订单 表中存所有出租车的行程信息。每段行程有唯一键 订单编号。乘客编号 和 司机编号 是 用户 表中 用户编号 的外键；意为司机和乘客都是用户，都保存在用户表中，通过角色这一字段列区分司机或是乘客。订单 表中的订单状态列反应订单状态，是已经完成，被司机取消亦或是被乘客取消。
用户 表存所有用户。每个用户有唯一键 乘客编号。被禁止 表示这个用户是否被禁止，角色 则是一个表示（‘乘客’, ‘司机’）的枚举类型。
需求：写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间未被禁止用户的取消率
数据准备：
订单表：
create table 订单 (
订单编号 int(20),
乘客编号 int(20),
司机编号 int(20),
城市 int(20),
状态 varchar(20),
日期 date);
insert into 订单 values
(1, 1, 10, 1, 'completed', '2013-10-01'),
(2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
(3, 3 ,12 ,6, 'completed','2013-10-01'),
(4, 4 ,13 ,6, 'cancelled_by_client','2013-10-01'),
(5, 1 ,10 ,1, 'completed','2013-10-02'),
(6, 2 ,11 ,6, 'completed','2013-10-02'),
(7, 3 ,12 ,6, 'completed','2013-10-02'),
(8, 2 ,12 ,12, 'completed','2013-10-03'),
(9, 3 ,10 ,12, 'completed','2013-10-03'),
(10, 4 ,13 ,12, 'cancelled_by_driver','2013-10-03');
https://xxx.ilovefishc.com/album/202012/08/182725sclw36mccp3lt68c.png
用户表：
create table 用户 (
用户编号 int(20),
被禁止 Varchar(10),
角色 Varchar(10));
insert into 用户 values
(1, 'No', 'client'),
(2, 'Yes', 'client'),
(3, 'No', 'client'),
(4, 'No', 'client'),
(10 ,'No', 'driver'),
(11 ,'No', 'driver'),
(12 ,'No', 'driver'),
(13 ,'No', 'driver');
https://xxx.ilovefishc.com/album/202012/08/182725l12v82pch9vhc83t.png
期望效果：
https://xxx.ilovefishc.com/album/202012/08/182725ko7s5s5t53cfss3t.png
select a.日期, sum(if(a.状态 in ('cancelled_by_driver', 'cancelled_by_client'),1,0))/count(*) as 取消率
from 订单 as a
left join 用户 as b on a.乘客编号 = b.用户编号 and b.被禁止 = 'no'
left join 用户 as c on a.司机编号 = c.用户编号 and c.被禁止 = 'no'
where a.日期 between date('2013-10-01') and date('2013-10-03')
GROUP BY a.日期
总结：on后面可以加多条件进行过滤
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
2. 多条件查询问题
在国家表中，如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
数据准备：
国家表：
create table 国家信息表 (
国家名称 varchar(20),
大洲 varchar(20),
面积int(20),
人口 int(20),
GDP int(20));
insert into 国家信息表 values
('Afghanistan','Asia',652230,25500100,20343000),
('Albania','Europe28748',28748,2831741,12960000),
('Algeria','Africa',2381741,37100000,188681000),
('Andorra','Europe',468,78115,712000),
('Angola','Africa',1246700,20609294,100990000);
https://xxx.ilovefishc.com/album/202012/08/183445iq64k4wjckk12k31.png
期望结果：
https://xxx.ilovefishc.com/album/202012/08/183445yz8bubxzyuo8nx22.png
SELECT 国家名称, 面积, 人口
from 国家信息表
where 面积 >3000000 or 人口 > 25000000
没什么好说的就很简单～
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
3. 简单分组统计问题
有一个学生选课信息 表 ，有: student (学生) 和 class (课程)。
请列出所有被学生选择超过或等于5次的课程。
Note:学生在每个课中不应被重复计算。
数据准备：
学生选课表：
create table 学生选课信息表(
学生编号 varchar(20),
选课课程 varchar(20));
insert into 学生选课信息表 values
('01','数学'),
('02','英语'),
('03','数学'),
('04','地理'),
('05','数学'),
('06','计算机'),
('07','数学'),
('08','数学'),
('09','数学');
#增加点重复数据
insert into 学生选课信息表 values ('09','数学');
insert into 学生选课信息表 values ('06','计算机');
https://xxx.ilovefishc.com/album/202012/08/184741kyivtttgz0stgmpi.png
期望效果：
https://xxx.ilovefishc.com/album/202012/08/184740xinc6c9jidsi7s7l.png
select 选课课程, count(*) as 被选择次数
from ( select distinct 学生编号,选课课程 from 学生选课信息表) as a
GROUP BY 选课课程
having count(*) >= 5
对于这种问题要注意考虑重复数据
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
页:
[1]