你可以这样做:
SQL Fiddle
MySQL5.6架构设置
:
CREATE TABLE fruit
(`text` varchar(11), `id` int, `time_end` int, `parent_id` int)
;
INSERT INTO fruit
(`text`, `id`, `time_end`, `parent_id`)
VALUES
('banana tree', 23, 200, 7),
('tomato vine', 84, 500, 7),
('pear tree', 13, 800, 7),
('apple tree', 40, 1000, 7),
('grape vine', 15, 1800, 7)
;
查询1
:
SELECT a.text,a.id, a.time_end,
IFNULL((select max(time_end) from fruit where time_end < a.time_end),0) as time_start,
a.parent_id
FROM fruit a WHERE a.text LIKE '%tree'
Results
:
| text | id | time_end | time_start | parent_id |
|-------------|----|----------|------------|-----------|
| banana tree | 23 | 200 | 0 | 7 |
| pear tree | 13 | 800 | 500 | 7 |
| apple tree | 40 | 1000 | 800 | 7 |