mysql 练习 exercise 4:meicaiwang 司机
2021/10/7 2:11:01
本文主要是介绍mysql 练习 exercise 4:meicaiwang 司机,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
create database meicaiwang; use meicaiwang; create table driver_daily( driver_id varchar(10), driver_name varchar(10), city_id varchar(10), city_name varchar(10), ordre_id varchar(10), d_year int, d_month int, d_day int ); insert into driver_daily values ('111','王**','32','厦门市','12233',2017,7,1), ('111','王**','32','厦门市','12234',2017,7,1), ('111','王**','32','厦门市','12235',2017,7,1), ('111','王**','32','厦门市','12236',2017,7,1), ('111','王**','32','厦门市','12237',2017,7,1), ('111','王**','32','厦门市','12238',2017,7,1), ('111','王**','32','厦门市','12239',2017,7,1), ('111','王**','32','厦门市','12240',2017,7,1), ('111','王**','32','厦门市','12241',2017,7,1), ('111','王**','32','厦门市','12242',2017,7,1), ('111','王**','32','厦门市','12243',2017,7,1), ('111','王**','32','厦门市','12244',2017,7,1), ('111','王**','32','厦门市','12245',2017,7,1), ('111','王**','32','厦门市','12246',2017,7,1), ('111','王**','32','厦门市','12247',2017,7,1), ('111','王**','32','厦门市','12248',2017,7,1), ('111','王**','32','厦门市','12249',2017,7,1), ('111','王**','32','厦门市','12250',2017,7,1), ('111','王**','32','厦门市','12251',2017,7,1), ('111','王**','32','厦门市','12252',2017,7,1), ('202','林**','32','厦门市','32234',2017,7,1), ('202','林**','32','厦门市','32235',2017,7,1), ('202','林**','32','厦门市','32236',2017,7,2), ('202','林**','32','厦门市','32237',2017,7,2), ('202','林**','32','厦门市','32238',2017,7,3), ('202','林**','32','厦门市','32239',2017,7,3), ('202','林**','32','厦门市','32240',2017,7,4), ('202','林**','32','厦门市','32241',2017,7,4), ('202','林**','32','厦门市','32242',2017,7,5), ('202','林**','32','厦门市','32243',2017,7,5), ('202','林**','32','厦门市','32244',2017,7,6), ('202','林**','32','厦门市','32245',2017,7,6), ('202','林**','32','厦门市','32246',2017,7,7), ('202','林**','32','厦门市','32247',2017,7,7), ('202','林**','32','厦门市','32248',2017,7,7), ('202','林**','32','厦门市','32249',2017,7,8), ('202','林**','32','厦门市','32250',2017,7,8), ('202','林**','32','厦门市','32251',2017,7,8), ('202','林**','32','厦门市','32252',2017,7,9), ('202','林**','32','厦门市','32253',2017,7,9), ('202','林**','32','厦门市','32254',2017,7,10), ('202','林**','32','厦门市','32255',2017,7,11); create table driver_info( driver_id varchar(10), driver_name varchar(10), driver_phone varchar(20) ); insert into driver_info values('110','王**','159****4134'), ('111','林**','159****7134'), ('222','张**','159****8134'); create table driver_collect( driver_id varchar(10), order_id varchar(10), d_year int, d_month int, d_day int ); insert into driver_collect values('111','111',2017,7,1), ('222','112',2017,7,1), ('222','113',2017,7,2), ('222','114',2017,7,3), ('222','115',2017,7,4), ('222','116',2017,7,5), ('222','117',2017,7,6), ('222','118',2017,7,7), ('222','119',2017,7,8), ('222','120',2017,7,9), ('222','121',2017,7,10), ('222','122',2017,7,11), ('222','123',2017,7,12), ('222','124',2017,7,13), ('222','125',2017,7,14), ('222','126',2017,7,15), ('222','127',2017,7,16), ('222','128',2017,7,17), ('222','129',2017,7,18), ('222','130',2017,7,19), ('222','131',2017,7,20), ('222','132',2017,7,21), ('222','133',2017,7,22), ('222','134',2017,7,23), ('222','135',2017,7,24), ('222','136',2017,7,25), ('222','137',2017,7,26), ('222','138',2017,7,27), ('222','139',2017,7,28), ('222','140',2017,7,29), ('222','141',2017,7,30), ('222','142',2017,7,31), ('222','143',2017,9,31); select * from driver_daily; select * from driver_info; select * from driver_collect; -- 2017年7月1日-2017年7月31日,有过10天以上的完单并且总完单量在20单以上的司机id,司机姓名,司机完单天数、司机完单数 select driver_id , driver_name, count(distinct d_day) as 司机完单天数, count(distinct ordre_id) as 司机完单数 from driver_daily where (d_year,d_month)=(2017,7) and count(distinct d_day)>10 and count(distinct ordre_id)>20;-- 报错 Invalid use of grounp function,where 不能用聚合函数,having+ 聚合函数 select driver_id , driver_name, count(distinct d_day) as 司机完单天数, count(distinct ordre_id) as 司机完单数 from driver_daily where (d_year,d_month)=(2017,7) group by driver_id,driver_name having count(distinct d_day)>10 and count(distinct ordre_id)>20; #老师答案 select driver_id, driver_name, count(distinct d_day) as 完单天数, count(distinct ordre_id) as 完单总数 from driver_daily where d_year=2017 and d_month=7 group by driver_id,driver_name having count(distinct d_day)>10 and count(distinct ordre_id)>20; -- 根据司机信息表(driver_info)和司机汇总表(driver_collect)取出近2017.07.01-2017.07.31完单大于30单的司机姓名和电话 select driver_name, driver_phone from driver_collect left join driver_info on driver_info.driver_id=driver_collect.driver_id where(d_year,d_month)=(2017,7) group by driver_name,driver_phone-- 根据题目要求应该以司机姓名和电话进行分组 having count(distinct order_id)>30; #老师答案 select driver_name,driver_phone from driver_collect left join driver_info on driver_info.driver_id=driver_collect.driver_id where d_year=2017 and d_month=7 group by driver_collect.driver_id,driver_name,driver_phone having count(distinct order_id)>30;
这篇关于mysql 练习 exercise 4:meicaiwang 司机的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-25如何部署MySQL集群资料:新手入门教程
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南