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

Java查询数据库，使用count统计数据后，如何查询得到多少条count

发布于 2022-09-13 00:03:41 字数 7665 浏览 18 评论 0

待解决的问题

我使用count统计每辆车当天的记录数，具体查询语句在下面

现在遇到的问题如下

1、统计这个数据的总数，一共有多少条（现在肉眼能看到是12条）

2、怎么将warnTime的时间转换，去掉 小时 分钟 秒速，数据类型不变都是Timestamp

使用

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 begin_time
        when driving_type = 1 then entry_time end AS warnTime
        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 warnTime DESC

新学的

统计每条进出总数

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,
    count(
        *
    ) AS totalCount,  -- 此处修改
    DATE_FORMAT(  -- 此处修改
        case when driving_type = 0 then begin_time when driving_type = 1 then end_time end
        ,'%Y-%m-%d'
    )
  AS warnTime
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
    warnTime DESC

表的结构

-- ----------------------------
-- 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=74 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 18:53:26', '2021-05-01 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
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 18:53:20', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-05-30 11:49:29', 0, 1);
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);
INSERT INTO `bulldozer_overview` VALUES (71, '京A01', 0, '2021-06-03 19:15:37', '2021-06-03 19:15:49', '2021-06-02 10:49:29', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, 0, 0);
INSERT INTO `bulldozer_overview` VALUES (72, '京LAA101', 0, '2021-06-03 23:46:43', '2021-06-03 23:46:55', '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 (73, '京LAA101', 0, '2021-06-03 23:46:48', '2021-06-03 23:47:04', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2021-03-24 10:49:29', 0, 1);
COMMIT;

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

扫码二维码加入Web技术交流群

发布评论

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

评论（2）

暮年慕年 2022-09-20 00:03:41
select count(*)
from (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 warnTime
      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 warnTime DESC
     ) cun

12

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 warnTime
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 warnTime DESC
licenseNumberleaveCountentryCountwarnTime
京A01102021/06/02
京A01322021/06/01
京B02102021/06/01
京MFV786012021/05/31
京LAA101112021/05/30
京MFV786012021/05/02
京LAA101102021/05/01
京CAB975102021/03/24
京LAA101512021/03/24
京MFV786102021/03/22
京MBE065102021/03/21
京Q89HX3102021/03/20
回复
怎言笑 2022-09-20 00:03:41
-- 统计总数
SELECT
    count(*) AS totalCount
FROM
    (
        SELECT
            license_number AS licenseNumber
        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
    ) AS count

-- 时间格式化 %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,
    DATE_FORMAT( 
        case when driving_type = 0 then begin_time when driving_type = 1 then end_time end
        ,'%Y-%m-%d'
    )
  AS warnTime
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
    warnTime DESC

image.png
image.png

回复
~没有更多了~

关于作者

月下凄凉

暂无简介

文章
评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

丶视觉

文章 0 评论 0

蓝礼

文章 0 评论 0

birdxs

文章 0 评论 0

foonlee

文章 0 评论 0

微信用户

文章 0 评论 0

っ〆星空下的拥抱

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文