按日期查，没有数据的日期补0

drop table if exists edpp_car;
create table edpp_car (
  id                bigint(20)      not null auto_increment     comment '序号',
  car_num           varchar(20)     not null                    comment '车牌号',
  car_ident         char(1)         default ''                  comment '车辆标识（内部车辆1,外来车辆2）',
  pass_ident        char(1)         not null                    comment '进出标识（进1,出2）',
  pass_name         varchar(50)     not null                    comment '进出场位置',
  in_out_time       datetime                                    comment '进出场时间',
  create_time         datetime                                    comment '创建时间',
  create_by         varchar(64)     default ''                  comment '创建者',
  update_time       datetime                                    comment '更新时间',
  update_by         varchar(64)     default ''                  comment '更新者',
  primary key (id)
) engine=innodb auto_increment=1 comment = '车辆出入表';

-- ----------------------------
-- 初始化-车辆出入表数据
-- ----------------------------
insert into edpp_car values(1,'陕A.8P8F8',  '1', '1','南',  '2023-08-24 22:45:53', '2023-08-24 22:45:53','admin',null,''); 
insert into edpp_car values(2,'陕U.2FGF8',  '1', '1','东',  '2023-08-23 22:45:53', '2023-08-23 22:45:53','admin',null,'');  
insert into edpp_car values(3,'陕A.3SSL8',  '1', '1','东',  '2023-08-22 22:45:53', '2023-08-22 22:45:53','admin',null,'');  
insert into edpp_car values(4,'陕A.7TTP4',  '1', '1','北',  '2023-08-20 22:45:53', '2023-08-20 22:45:53','admin',null,'');  
insert into edpp_car values(5,'陕A.2MMT1',  '1', '1','北',  '2023-08-19 22:45:53', '2023-08-19 22:45:53','admin',null,'');  
insert into edpp_car values(6,'陕A.4VGM0',  '1', '1','北',  '2023-08-18 22:45:53', '2023-08-18 22:45:53','admin',null,'');  
insert into edpp_car values(7,'陕A.7OUG0',  '1', '1','北',  '2023-08-25 22:45:53', '2023-08-25 22:45:53','admin',null,'');  
insert into edpp_car values(8,'陕A.7JJG0',  '1', '1','北',  '2023-08-26 22:45:53', '2023-08-26 22:45:53','admin',null,'');  
insert into edpp_car values(9,'陕A.7GHF0',  '1', '1','北',  '2023-08-17 22:45:53', '2023-08-17 22:45:53','admin',null,'');  
insert into edpp_car values(10,'陕A.7JFF0', '1', '1','北',  '2023-08-29 22:45:53', '2023-08-29 22:45:53','admin',null,''); 
insert into edpp_car values(11,'陕A.8P8F8',  '1', '2','南',  '2023-09-07 22:45:53', '2023-09-07 22:45:53','admin',null,''); 
insert into edpp_car values(14,'陕A.8P8F8',  '1', '1','南',  '2023-09-07 23:45:53', '2023-09-07 23:45:53','admin',null,''); 
insert into edpp_car values(12,'陕U.2FGF8',  '1', '2','东',  '2023-09-06 22:45:53', '2023-09-06 22:45:53','admin',null,''); 
insert into edpp_car values(13,'陕A.7JFF0',  '1', '2','北',  '2023-09-05 22:45:53', '2023-09-05 22:45:53','admin',null,''); 

insert into edpp_car values(15,'陕A.7JFF0',  '1', '2','北',  '2023-09-08 22:45:53', '2023-09-08 22:45:53','admin',null,''); 

insert into edpp_car values(16,'陕A.7JFF0',  '1', '2','北',  '2023-09-08 22:45:53', '2023-09-08 22:45:53','admin',null,''); 
insert into edpp_car values(11,'陕A.8P8F8',  '1', '2','南',  '2023-09-07 21:45:53', '2023-09-07 22:45:53','admin',null,''); 
commit;

--车辆滞留时间查询

select a.*, TIMESTAMPDiFF(hour ,in_out_time,sysDate()) retention_time from edpp_car a  RIGHT JOIN 
(select  car_num , max(in_out_time) max_time from edpp_car   group By car_num  ) cars on  cars.car_num = a.car_num
where  a.pass_ident = '1' and max_time = in_out_time
ORDER BY in_out_time asc 
limit 0,5;

--当天进出车辆数据查询
select * from 
(select count(a.car_num) out_num from edpp_car a 
where a.pass_ident ='2' and cast(a.in_out_time as date) = CURDATE()) incar,
(select count(b.car_num) in_num from edpp_car b 
where b.pass_ident ='1' and cast(b.in_out_time as date) = CURDATE()) outcar

--7天内进出车辆数据查询

select * from 
(select count(a.car_num) out_num from edpp_car a 
where a.pass_ident ='2' and a.in_out_time >= CURDATE()-INTERVAL 7 DAY ) incar,
(select count(b.car_num) in_num from edpp_car b 
where b.pass_ident ='1' and b.in_out_time >= CURDATE()-INTERVAL 7 DAY ) outcar

--当天每小时进出车辆数据查询

select HOUR(in_out_time) as hours, count(car_num) as car_count from edpp_car 
where cast(in_out_time as date) = CURDATE() and pass_ident ='1'
GROUP BY HOUR(in_out_time) 
ORDER BY hours

select day(in_out_time) as day, count(car_num) as car_count from edpp_car 
where cast(in_out_time as date) >= CURDATE()-INTERVAL 7 DAY
GROUP BY day(in_out_time) 
ORDER BY day

--按日期查询

select DATE(in_out_time) as date,
IFNULL(count(car_num),0) as car_count from edpp_car 
where cast(in_out_time as date) >= CURDATE()-INTERVAL 30 DAY
GROUP BY DATE(in_out_time) 
ORDER BY date

--按日期查，没有数据的日期补0

SELECT date,IFNULL(data.num, 0) AS inCount
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM edpp_car limit 30
              ) day
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT  COUNT(1) AS num, DATE(create_time) AS time
    FROM edpp_car
    WHERE 
    pass_ident ='1' and 
            create_time >= CURDATE() - INTERVAL 7 DAY
    GROUP BY DATE(create_time)
) data 
ON time = date

ORDER BY date;