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),

order_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);

select * from driver_daily;

-- 2017年7月1日-2017年7月31日，有过10天以上的完单并且总完单量在20单以上的司机id，司机姓名，司机完单天数、司机完单数

select driver_id,driver_name, count(distinct d_day) 司机完单天数,count(distinct order_id) 司机完单数

from driver_daily

where d_year=2017 and d_month=7 and d_day between 1 and 31

group by driver_id

having count(distinct d_day)>10 and count(distinct order_id)>20;

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_info;

select * from driver_collect;

-- 近2017.07.01-2017.07.31完单大于30单的司机姓名及电话

select driver_name 司机姓名,driver_phone 电话

from driver_info di left join driver_collect dc on di.driver_id=dc.driver_id

where d_year=2017 and d_month=7 and d_day between 1 and 31

group by di.driver_id

having count(distinct order_id)>30;