今日测试数据:要取同一行数据中较大的日期作为有效值,但是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 ;