yujian10962 2019-05-27 19:17 采纳率: 0%
浏览 327

mysql 对日期判断错误

今日测试数据:要取同一行数据中较大的日期作为有效值,但是mysql出现了判断失误的情况,直接比较大小的值是正确的,if(expr1,expr2,expr3)和case语句、greatest函数
返回的结果都有问题,将日期转化成unixtimestamp比较也是有这个问题:

初始化数据:

SET FOREIGN_KEY_CHECKS=0;


-- Table structure for sorder_extend


DROP TABLE IF EXISTS sorder_extend;
CREATE TABLE sorder_extend (
sorder_id varchar(32) NOT NULL COMMENT '主键:sorder的id',
collect_time datetime DEFAULT NULL COMMENT '收藏时间',
add_cart_time datetime DEFAULT NULL COMMENT '加购时间',
PRIMARY KEY (sorder_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单信息扩展表';


-- Records of sorder_extend


INSERT INTO sorder_extend VALUES ('1905231749479YJME', '2019-05-23 18:31:41', '2019-05-23 18:21:07');
INSERT INTO sorder_extend VALUES ('190524112637GL6A7', '2019-05-27 16:36:59', '2019-05-27 16:37:12');
INSERT INTO sorder_extend VALUES ('190524113032MAYK4', '2019-05-28 19:40:05', '2019-05-29 19:40:05');
INSERT INTO sorder_extend VALUES ('190524113042HEUNR', '2019-05-27 16:38:53', '2019-05-27 16:39:00');
INSERT INTO sorder_extend VALUES ('190524113103DFPUE', null, null);
INSERT INTO sorder_extend VALUES ('190524114012ZKSFS', '2019-05-24 11:46:33', '2019-05-24 11:55:05');
INSERT INTO sorder_extend VALUES ('190524115905GEHQT', '2019-05-24 11:59:39', null);
INSERT INTO sorder_extend VALUES ('190524134532WAV2A', null, null);
INSERT INTO sorder_extend VALUES ('190524142915HSJPZ', null, null);
INSERT INTO sorder_extend VALUES ('190524142920LMVY4', null, null);
INSERT INTO sorder_extend VALUES ('190524143130H79DG', null, null);
INSERT INTO sorder_extend VALUES ('190524143146KQHQ2', null, null);
INSERT INTO sorder_extend VALUES ('190524150522ST93R', null, null);
INSERT INTO sorder_extend VALUES ('1905241519073GBAU', null, null);
INSERT INTO sorder_extend VALUES ('1905241558577JFUY', null, null);
INSERT INTO sorder_extend VALUES ('190524160245HFXRB', '2019-05-24 16:04:06', '2019-05-24 16:04:22');
INSERT INTO sorder_extend VALUES ('190524162015SC4NR', '2019-05-24 16:23:25', '2019-05-24 16:23:41');
INSERT INTO sorder_extend VALUES ('190524162329JJU6J', null, '2019-05-24 16:24:42');
INSERT INTO sorder_extend VALUES ('1905241625544F969', '2019-05-24 16:26:25', '2019-05-24 16:26:30');
INSERT INTO sorder_extend VALUES ('1905241630369M344', null, null);
INSERT INTO sorder_extend VALUES ('190524171220LV572', null, null);
INSERT INTO sorder_extend VALUES ('190524194425PAGBL', null, null);
INSERT INTO sorder_extend VALUES ('1905241944578AMEM', '2019-05-24 19:45:20', '2019-05-24 19:45:25');
INSERT INTO sorder_extend VALUES ('190525101340737XC', '2019-05-25 10:25:22', '2019-05-25 10:34:21');
INSERT INTO sorder_extend VALUES ('190525101351KKYVH', null, null);
INSERT INTO sorder_extend VALUES ('1905251036579HR7D', null, null);
INSERT INTO sorder_extend VALUES ('190525103720WUB8A', null, null);
INSERT INTO sorder_extend VALUES ('190525110126LAKVP', null, '2019-05-25 11:03:52');
INSERT INTO sorder_extend VALUES ('190525110235B8D6Y', null, null);
INSERT INTO sorder_extend VALUES ('190525110508R4RRU', null, null);
INSERT INTO sorder_extend VALUES ('190525110626B5GGK', '2019-05-25 11:06:40', '2019-05-25 11:06:45');
INSERT INTO sorder_extend VALUES ('1905251107172A8EF', null, '2019-05-25 11:20:45');
INSERT INTO sorder_extend VALUES ('190527161719HBJUU', '2019-05-27 16:45:27', null);
INSERT INTO sorder_extend VALUES ('19052717554864NTC', '2019-05-27 17:56:06', '2019-05-27 18:02:32');
INSERT INTO sorder_extend VALUES ('190527180251V8YU6', '2019-05-27 18:03:09', '2019-05-27 18:03:14');
INSERT INTO sorder_extend VALUES ('190527180637VSV75', null, null);

查询 语句:

SELECT sorder_id, add_cart_time,collect_time,IF(add_cart_time > collect_time,collect_time,add_cart_time),add_cart_time >collect_time from sorder_extend ;

结果展示:

图片说明

  • 写回答

2条回答

  • 洋葱头的博客 2019-05-27 19:58
    关注

    没问题呢,这个结果正确的呢,仔细检查sql

    评论

报告相同问题?

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)