当前位置：文江博客话题详情

SpringBoot根据日期查询，我希望按天查询所有车牌号当天的驶入次数与驶出次数

发布于 2022-09-13 00:01:09 字数 11774 浏览 21 评论 0

预先解释

有一张车辆出入总览表

license_number 是车牌号

driving_type 字段是行驶状态
0代表驶出
1代表输入

begin_time 是驶出的出发时间
entry_time 是驶入的进场时间

需求说明

我希望按天查询所有车牌号当天的驶入次数与驶出次数

举例

假如

begin_time为5月30日的有车辆A、车辆B、车辆C这三量车的记录

车辆A驶出3次，车辆B驶出2次、车辆C驶出1次

假如entry_time为5月30日的有车辆A、车辆B这两辆车的记录

车辆A驶入2次，车辆B驶入2次

然后

begin_time为5月31日的有车辆B、车辆C这三量车的记录

车辆B驶出3次、车辆C驶出3次

假如entry_time为5月31日的有车辆A、车辆B、车辆C这两辆车的记录

车辆A驶入1次，车辆B驶入5次，车辆C驶入3次

那么我希望查到数据是

A beginCount 3 enterCount 2 5月30
B beginCount 2 enterCount 2 5月30
C beginCount 1 enterCount 0 5月30
A beginCount 0 enterCount 1 5月31
B beginCount 3 enterCount 5 5月31
C beginCount 3 enterCount 3 5月31

表的数据

-- ----------------------------
-- Table structure for bulldozer_overview
-- ----------------------------
DROP TABLE IF EXISTS `bulldozer_overview`;
CREATE TABLE `bulldozer_overview` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '车辆ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `data_source` int(2) DEFAULT '0' COMMENT '数据来源',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `begin_time` varchar(50) DEFAULT NULL COMMENT '出发时间',
  `end_time` varchar(50) DEFAULT NULL COMMENT '到达时间',
  `elimination` varchar(50) DEFAULT NULL COMMENT '消纳地点',
  `rubbish_type` varchar(50) DEFAULT NULL COMMENT '垃圾类型',
  `order_number` varchar(100) DEFAULT NULL COMMENT '运单编号',
  `site_name` varchar(50) DEFAULT NULL COMMENT '工地名称',
  `site_area` varchar(20) DEFAULT NULL COMMENT '工地所属区',
  `elimination_area` varchar(20) DEFAULT NULL COMMENT '消纳地点所属区',
  `transport_name` varchar(50) DEFAULT NULL COMMENT '运输企业',
  `transport_area` varchar(20) DEFAULT NULL COMMENT '运输企业所属区',
  `transport_volume` float(10,2) DEFAULT NULL COMMENT '运输量(吨)',
  `driving_status` int(11) DEFAULT '2' COMMENT '车辆行驶状态',
  `entry_time` timestamp NULL DEFAULT NULL COMMENT '进消纳厂时间',
  `flag_delete` int(2) DEFAULT '0' COMMENT '删除标识',
  `driving_type` int(2) DEFAULT '0' COMMENT '行驶状态',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=62 DEFAULT CHARSET=utf8 COMMENT='渣土车出入总览表';

-- ----------------------------
-- Records of bulldozer_overview
-- ----------------------------
BEGIN;
INSERT INTO `bulldozer_overview` VALUES (0, '京Q89HX3', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:25', '2021-03-20 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (1, '京MBE065', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:29', '2021-03-21 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (2, '京MFV786', 0, '2021-04-08 16:49:37', '2021-06-03 16:33:34', '2021-03-22 10:59:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (3, '京CAB975', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:15', '2021-03-24 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (4, '京LAA101', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:17', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (5, '京LAA101', 0, '2021-06-03 16:30:30', '2021-06-03 16:30:37', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (6, '京LAA101', 0, '2021-06-03 16:32:04', '2021-06-03 16:32:16', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (59, '京LAA101', 0, '2021-06-03 16:32:55', '2021-06-03 16:33:12', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (60, '京LAA101', 0, '2021-06-03 16:33:02', '2021-06-03 16:33:47', '2021-05-30 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (61, '京LAA101', 0, '2021-06-03 16:34:12', '2021-06-03 16:34:37', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-30 10:49:29', 0, 1);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

补充问题

我改了下查询语句

select license_number                                  车牌
     , count(case when driving_type = 0 then true end) 驶出次数
     , count(case when driving_type = 1 then true end) 驶入次数
     , date_format(begin_time, '%Y年%m月%d日')          驶出日期
         , date_format(entry_time, '%Y年%m月%d日')          驶入日期
from bulldozer_overview
group by license_number, date_format(begin_time, '%Y年%m月%d日')

新的数据结构

-- ----------------------------
-- Table structure for bulldozer_overview
-- ----------------------------
DROP TABLE IF EXISTS `bulldozer_overview`;
CREATE TABLE `bulldozer_overview` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '车辆ID',
  `license_number` varchar(50) NOT NULL COMMENT '车牌号',
  `data_source` int(2) DEFAULT '0' COMMENT '数据来源',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `begin_time` varchar(50) DEFAULT NULL COMMENT '出发时间',
  `end_time` varchar(50) DEFAULT NULL COMMENT '到达时间',
  `elimination` varchar(50) DEFAULT NULL COMMENT '消纳地点',
  `rubbish_type` varchar(50) DEFAULT NULL COMMENT '垃圾类型',
  `order_number` varchar(100) DEFAULT NULL COMMENT '运单编号',
  `site_name` varchar(50) DEFAULT NULL COMMENT '工地名称',
  `site_area` varchar(20) DEFAULT NULL COMMENT '工地所属区',
  `elimination_area` varchar(20) DEFAULT NULL COMMENT '消纳地点所属区',
  `transport_name` varchar(50) DEFAULT NULL COMMENT '运输企业',
  `transport_area` varchar(20) DEFAULT NULL COMMENT '运输企业所属区',
  `transport_volume` float(10,2) DEFAULT NULL COMMENT '运输量(吨)',
  `driving_status` int(11) DEFAULT '2' COMMENT '车辆行驶状态',
  `entry_time` timestamp NULL DEFAULT NULL COMMENT '进消纳厂时间',
  `flag_delete` int(2) DEFAULT '0' COMMENT '删除标识',
  `driving_type` int(2) DEFAULT '0' COMMENT '行驶状态',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=utf8 COMMENT='渣土车出入总览表';

-- ----------------------------
-- Records of bulldozer_overview
-- ----------------------------
BEGIN;
INSERT INTO `bulldozer_overview` VALUES (0, '京Q89HX3', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:25', '2021-03-20 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (1, '京MBE065', 0, '2021-04-08 16:48:36', '2021-06-03 16:33:29', '2021-03-21 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (2, '京MFV786', 0, '2021-04-08 16:49:37', '2021-06-03 16:33:34', '2021-03-22 10:59:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (3, '京CAB975', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:15', '2021-03-24 10:59:10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (4, '京LAA101', 0, '2021-04-08 16:49:37', '2021-06-03 16:30:17', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (5, '京LAA101', 0, '2021-06-03 16:30:30', '2021-06-03 16:30:37', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (6, '京LAA101', 0, '2021-06-03 16:32:04', '2021-06-03 16:32:16', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (59, '京LAA101', 0, '2021-06-03 16:32:55', '2021-06-03 16:33:12', '2021-03-24 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (60, '京LAA101', 0, '2021-06-03 16:33:02', '2021-06-03 16:33:47', '2021-05-30 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (61, '京LAA101', 0, '2021-06-03 16:34:12', '2021-06-03 17:15:27', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-01 10:49:29', 0, 1);
INSERT INTO `bulldozer_overview` VALUES (62, '京MFV786', 0, '2021-06-03 17:15:05', '2021-06-03 17:15:48', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-02 10:49:29', 0, 1);
INSERT INTO `bulldozer_overview` VALUES (63, '京MFV786', 0, '2021-06-03 17:15:09', '2021-06-03 17:15:50', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-31 10:49:29', 0, 1);
INSERT INTO `bulldozer_overview` VALUES (64, '京LAA101', 0, '2021-06-03 17:28:24', '2021-06-03 17:28:49', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-30 11:49:29', 0, 0);
INSERT INTO `bulldozer_overview` VALUES (65, '京A01', 0, '2021-06-03 18:03:27', '2021-06-03 18:03:59', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (66, '京A01', 0, '2021-06-03 18:03:31', '2021-06-03 18:04:02', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (67, '京A01', 0, '2021-06-03 18:03:36', '2021-06-03 18:04:05', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (68, '京A01', 0, '2021-06-03 18:03:39', '2021-06-03 18:04:18', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-06-01 10:49:29', 0, 1);
INSERT INTO `bulldozer_overview` VALUES (69, '京A01', 0, '2021-06-03 18:03:42', '2021-06-03 18:04:22', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-06-01 10:49:29', 0, 1);
INSERT INTO `bulldozer_overview` VALUES (70, '京B02', 0, '2021-06-03 18:06:50', '2021-06-03 18:06:55', '2021-06-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

继续补充

select license_number                                  车牌
     , count(case when driving_type = 0 then true end) 驶出次数
     , count(case when driving_type = 1 then true end) 驶入次数
     , date_format(begin_time, '%Y年%m月%d日')          驶出日期
         , date_format(entry_time, '%Y年%m月%d日')          驶入日期
from bulldozer_overview
group by license_number, date_format(begin_time, '%Y年%m月%d日'), date_format(entry_time, '%Y年%m月%d日')

我的修改

select
        license_number AS licenseNumber
        ,count(case when driving_type = 0 then true end) AS leaveCount
        ,count(case when driving_type = 1 then true end) AS entryCount
        ,case
        when driving_type = 0 then date_format(begin_time, '%Y/%m/%d')
        when driving_type = 1 then date_format(entry_time, '%Y/%m/%d') end AS updateTime
        from bulldozer_overview
        group by license_number,
        case
        when driving_type = 0 then date_format(begin_time, '%Y/%m/%d')
        when driving_type = 1 then date_format(entry_time, '%Y/%m/%d') end
                
                ORDER BY updateTime DESC
                limit 0, 200

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼前雾蒙蒙 2022-09-20 00:01:09

查询车牌每日的驶出次数和驶入次数

select license_number                                                          车牌
     , count(case when driving_type = 0 then true end)                         驶出次数
     , count(case when driving_type = 1 then true end)                         驶入次数
     , case
           when driving_type = 0 then date_format(begin_time, '%Y年%m月%d日')
           when driving_type = 1 then date_format(entry_time, '%Y年%m月%d日') end 日期
from bulldozer_overview
group by license_number
       , case
             when driving_type = 0 then date_format(begin_time, '%Y年%m月%d日')
             when driving_type = 1 then date_format(entry_time, '%Y年%m月%d日') end

车牌	驶出次数	驶入次数	日期
京Q89HX3	1	0	2021年03月20日
京MBE065	1	0	2021年03月21日
京MFV786	1	0	2021年03月22日
京CAB975	1	0	2021年03月24日
京LAA101	4	0	2021年03月24日
京LAA101	1	1	2021年05月30日