sql学习及理解_4

junkaiw

                               
登录/注册后可看大图

                               
登录/注册后可看大图

目录：
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

对于这种问题要注意考虑重复数据

                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图