问答博客资讯标签用户活动
logo极客观点logo项目管理logoHarmonyOS
javascript
前端
python
node.js
react
vue.js
php
laravel
go
人工智能
mysql
linux
ios
java
android
css
typescript
spring
程序员
logoONES 研发管理logo思否企业问答logo安谋科技 XPU

MySQL查询数据库，统计数据总数和转换时间格式

头像
小刘
    53316206389

    待解决的问题

    1、统计这个数据的总数，一共有多少条

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

    3 假如我将warnTime的类型转成date_format(begin_time, '%Y/%m/%d')的VARCHAR类型，使用warnTime排序会出现问题吗？

    当前使用语句

    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

    表的结构

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

    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 UNIX_TIMESTAMP(begin_time, 'yyyy-MM-dd')
          when driving_type = 1 then UNIX_TIMESTAMP(entry_time, 'yyyy-MM-dd') 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

    我尝试用UNIX_TIMESTAMP转换，报错如下

    前端后端java数据库mysql
    阅读 2.3k
    2 个回答
    得票最新
    头像
    极简博客
      44827
      ✓ 已被采纳
      -- 统计总数
SELECT
    count(*) AS totalCount
FROM
    (
        SELECT
            license_number AS licenseNumber
        FROM
            bulldozer_overview
        GROUP BY
            license_number,
            CASE
        WHEN driving_type = 0 THENa
            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,
    UNIX_TIMESTAMP(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

      timestamp显示的时候再用序列化工具去处理一下就可以了。

      头像
      neko_cannon
        118210

        image.png
        常规语法错误。。。自然跑不通啊。。。
        至于日期排序，不影响的。只要你保证YmdHis顺序而且位数固定，怎么排都不会有问题。。。

        另外切日期一般直接 substr(time 1 for 10)
        至于数据类型，导出都当string，不知道你用的框架里怎么设定的，但是mysql传出来的只有string。。。

        撰写回答
        你尚未登录，登录后可以
        • 和开发者交流问题的细节
        • 关注并接收问题和回答的更新提醒
        • 参与内容的编辑和改进，让解决方法与时俱进
        推荐问题