MYSQL 之前最近的行，另一列具有特定值

Question

我一直在尝试根据 TIMESTAMP(最接近该行之前)和另一列 STABILIZATION(值为 1)将表与其自身进行 LEFT JOIN

查询测试表:

DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `UID` varchar(40) NOT NULL,  `CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `STABILISATION` tinyint(4) NOT NULL DEFAULT '0',  PRIMARY KEY (`UID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;TRUNCATE `test`;INSERT INTO `test` (`UID`, `CREATED`, `STABILISATION`) VALUES('1',   '2014-03-12 09:22:41',  1),('2',   '2014-03-12 09:24:20',  0),('3',   '2014-03-12 09:24:35',  0),('4',   '2014-03-12 09:24:39',  1),('5',   '2014-03-12 09:24:41',  0),('6',   '2014-03-12 10:02:17',  1),('7',   '2014-03-12 10:02:24',  0),('8',   '2014-03-12 11:00:15',  0),('9',   '2014-03-12 11:01:08',  1),('10',  '2014-03-12 11:01:17',  0);

我正在尝试像这样获取 t2_UID COLUMN

ID       CREATED                STABILISATION     t2_UID'1',    '2014-03-12 09:22:41',  1,                1'2',    '2014-03-12 09:24:20',  0,                1'3',    '2014-03-12 09:24:35',  0,                1'4',    '2014-03-12 09:24:39',  1,                4'5',    '2014-03-12 09:24:41',  0,                4'6',    '2014-03-12 10:02:17',  1,                6'7',    '2014-03-12 10:02:24',  0,                6'8',    '2014-03-12 11:00:15',  0,                6'9',    '2014-03-12 11:01:08',  1,                9'10',   '2014-03-12 11:01:17',  0,                9

每当 STABILIZATION 改变时，t2_UID 也应该改变!

我得到的最接近的是这个查询:

SELECT t1.*,t2.UID AS t2_UIDFROM test AS t1LEFT JOIN test as t2ON t2.UID = (SELECT UIDFROM test as t3WHERE t3.STABILISATION = 1ORDER BY ABS(TIMEDIFF(t1.CREATED, t3.CREATED))LIMIT 1)

最近的

Answer 1

这是一种快速而肮脏的方式:

SELECT *,
    (SELECT t2.UID 
    FROM test t2 
    WHERE STABILISATION=1 AND t2.CREATED <= t.CREATED 
    ORDER BY t2.CREATED DESC LIMIT 1) AS t2_id
FROM test t
ORDER BY t.CREATED;

SQL Fiddle