34,594
社区成员
发帖
与我相关
我的任务
分享
select * from test d
where not exists(select 1 from test where bus_index=d.bus_index and time>d.time)
AND EXISTS (SELECT bus_index
FROM test WHERE bus_index<>d.bus_index AND ABS(bus_index-d.bus_index)<5 )
--22楼:知道虾扯蛋不?呵呵!看我21楼的代码!
drop table test;
CREATE TABLE test(bus_index INT, time INT, number INT);
INSERT INTO test
SELECT 111, 930, 888 UNION ALL
SELECT 112, 830, 747 UNION ALL
SELECT 111, 999, 151 UNION ALL
SELECT 113, 450, 799 UNION ALL
SELECT 112, 920, 33 UNION ALL
SELECT 113, 540, 44 UNION ALL
SELECT 123, 610, 154 UNION ALL
SELECT 123, 745, 164;
SELECT * FROM test;
SELECT bus_index, time, number
FROM test d
WHERE EXISTS
(SELECT bus_index, MAX(time) AS time
FROM test WHERE bus_index=d.bus_index GROUP BY bus_index HAVING MAX(time)=d.time );
---楼主要的查询语句如下:
SELECT bus_index, time, number
FROM test d
WHERE EXISTS
(SELECT bus_index, MAX(time) AS time
FROM test WHERE bus_index=d.bus_index GROUP BY bus_index HAVING MAX(time)=d.time )
AND EXISTS (SELECT bus_index
FROM test WHERE bus_index<>d.bus_index AND ABS(bus_index-d.bus_index)<5 )