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个回答

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

结果正确if(条件,A,B)函数,条件成立是返回A,不成立才返回B,所以你这取的是时间小的那个

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问