|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
本帖最后由 junkaiw 于 2020-12-8 18:49 编辑
目录:
1. 订单取消率计算
2. 多条件查询问题
3. 简单分组统计问题
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');
复制代码
用户表:
- 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');
复制代码
期望效果:
- 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后面可以加多条件进行过滤
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);
复制代码
期望结果:
- SELECT 国家名称, 面积, 人口
- from 国家信息表
- where 面积 >3000000 or 人口 > 25000000
复制代码
没什么好说的就很简单~
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','计算机');
复制代码
期望效果:
- select 选课课程, count(*) as 被选择次数
- from ( select distinct 学生编号,选课课程 from 学生选课信息表) as a
- GROUP BY 选课课程
- having count(*) >= 5
复制代码
对于这种问题要注意考虑重复数据
|
|