鱼C论坛

 找回密码
 立即注册
查看: 2098|回复: 0

[见证历程] sql学习及理解_4

[复制链接]
发表于 2020-12-9 07:49:13 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能^_^

您需要 登录 才可以下载或查看,没有账号?立即注册

x
本帖最后由 junkaiw 于 2020-12-8 18:49 编辑


                               
登录/注册后可看大图

                               
登录/注册后可看大图

目录:
1. 订单取消率计算
2. 多条件查询问题
3. 简单分组统计问题


                               
登录/注册后可看大图

                               
登录/注册后可看大图


1. 订单取消率计算

订单 表中存所有出租车的行程信息。每段行程有唯一键 订单编号。乘客编号 和 司机编号 是 用户 表中 用户编号 的外键;意为司机和乘客都是用户,都保存在用户表中,通过角色这一字段列区分司机或是乘客。订单 表中的订单状态列反应订单状态,是已经完成,被司机取消亦或是被乘客取消。

用户 表存所有用户。每个用户有唯一键 乘客编号。被禁止 表示这个用户是否被禁止,角色 则是一个表示(‘乘客’, ‘司机’)的枚举类型。

需求:写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间未被禁止用户的取消率

数据准备:

订单表:
  1. create table 订单 (
  2. 订单编号 int(20),
  3. 乘客编号 int(20),
  4. 司机编号 int(20),
  5. 城市 int(20),
  6. 状态 varchar(20),
  7. 日期 date);

  8. insert into 订单 values
  9. (1, 1, 10, 1, 'completed', '2013-10-01'),
  10. (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
  11. (3, 3 ,12 ,6, 'completed','2013-10-01'),
  12. (4, 4 ,13 ,6, 'cancelled_by_client','2013-10-01'),
  13. (5, 1 ,10 ,1, 'completed','2013-10-02'),
  14. (6, 2 ,11 ,6, 'completed','2013-10-02'),
  15. (7, 3 ,12 ,6, 'completed','2013-10-02'),
  16. (8, 2 ,12 ,12, 'completed','2013-10-03'),
  17. (9, 3 ,10 ,12, 'completed','2013-10-03'),
  18. (10, 4 ,13 ,12, 'cancelled_by_driver','2013-10-03');
复制代码



                               
登录/注册后可看大图


用户表:
  1. create table 用户 (
  2. 用户编号 int(20),
  3. 被禁止 Varchar(10),
  4. 角色 Varchar(10));

  5. insert into 用户 values
  6. (1, 'No', 'client'),
  7. (2, 'Yes', 'client'),
  8. (3, 'No', 'client'),
  9. (4, 'No', 'client'),
  10. (10 ,'No', 'driver'),
  11. (11 ,'No', 'driver'),
  12. (12 ,'No', 'driver'),
  13. (13 ,'No', 'driver');
复制代码



                               
登录/注册后可看大图


期望效果:

                               
登录/注册后可看大图


  1. select a.日期, sum(if(a.状态 in ('cancelled_by_driver', 'cancelled_by_client'),1,0))/count(*) as 取消率
  2. from 订单 as a
  3. left join 用户 as b on a.乘客编号 = b.用户编号 and b.被禁止 = 'no'
  4. left join 用户 as c on a.司机编号 = c.用户编号 and c.被禁止 = 'no'
  5. where a.日期 between date('2013-10-01') and date('2013-10-03')
  6. GROUP BY a.日期
复制代码


总结:on后面可以加多条件进行过滤


                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图


2. 多条件查询问题
在国家表中,如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。

编写一个SQL查询,输出表中所有大国家的名称、人口和面积。

数据准备:
国家表:
  1. create table 国家信息表 (
  2. 国家名称 varchar(20),
  3. 大洲 varchar(20),
  4. 面积  int(20),
  5. 人口 int(20),
  6. GDP int(20));

  7. insert into 国家信息表 values
  8. ('Afghanistan','Asia',652230,25500100,20343000),
  9. ('Albania','Europe28748',28748,2831741,12960000),
  10. ('Algeria','Africa',2381741,37100000,188681000),
  11. ('Andorra','Europe',468,78115,712000),
  12. ('Angola','Africa',1246700,20609294,100990000);
复制代码


                               
登录/注册后可看大图


期望结果:

                               
登录/注册后可看大图


  1. SELECT 国家名称, 面积, 人口
  2. from 国家信息表
  3. where 面积 >3000000 or 人口 > 25000000
复制代码


没什么好说的就很简单~


                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图



3. 简单分组统计问题
有一个学生选课信息 表 ,有: student (学生) 和 class (课程)。

请列出所有被学生选择超过或等于5次的课程。

Note:学生在每个课中不应被重复计算。

数据准备:
学生选课表:
  1. create table 学生选课信息表  (
  2. 学生编号 varchar(20),
  3. 选课课程 varchar(20));

  4. insert into 学生选课信息表 values
  5. ('01','数学'),
  6. ('02','英语'),
  7. ('03','数学'),
  8. ('04','地理'),
  9. ('05','数学'),
  10. ('06','计算机'),
  11. ('07','数学'),
  12. ('08','数学'),
  13. ('09','数学');

  14. #增加点重复数据
  15. insert into 学生选课信息表 values ('09','数学');
  16. insert into 学生选课信息表 values ('06','计算机');
复制代码



                               
登录/注册后可看大图


期望效果:

                               
登录/注册后可看大图


  1. select 选课课程, count(*) as 被选择次数
  2. from ( select distinct 学生编号,选课课程 from 学生选课信息表) as a
  3. GROUP BY 选课课程
  4. having count(*) >= 5
复制代码


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

                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图
想知道小甲鱼最近在做啥?请访问 -> ilovefishc.com
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|手机版|Archiver|鱼C工作室 ( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)

GMT+8, 2024-3-29 15:22

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表