表名:Detail
字段:bus_index(int),time(int),number(int)
查询 取出每个bus_index的一条记录(Time最大的)后,
还有结果要满足每个bus_index中的number要跟其中另外一个的bus_index的一个差值的绝对值必须小于5.
字段:bus_index(int),time(int),number(int)
查询 取出每个bus_index的一条记录(Time最大的)后,
还有结果要满足每个bus_index中的number要跟其中另外一个的bus_index的一个差值的绝对值必须小于5.
--最最大记录!
*
from
tb t
where
not exists(select 1 from tb where bus_index=t.bus_index and
time>t.time)
select * from Detail a where [Time]=(select max([Time]) from Detail where Bus_index=a.Bus_index and [Time]<>a.[Time] and abs(number-a.number)<=5)
(
SELECT *
FROM Detail AS A
WHERE time = (SELECT MAX(time) FROM Detail
WHERE bus_index = A.bus_index)
)
SELECT *
FROM Liang AS A
WHERE NOT EXISTS(
SELECT * FROM Liang
WHERE A.bus_index <> bus_index
AND ABS(A.number-number) >= 5
);
*
from
tb t
where
not exists(select 1 from tb where bus_index=t.bus_index and time>t.time)
and
abs(t.number-number) >= 5
同一个bus_index,time有没有相同的?【另外一个】是 怎么取得的?
group by出来的还是有很多记录的
取出每个bus_index的一条记录(Time最大的)后的这个记录后的number比较
只要满足其中一个与另一个的绝对值小于5就可以了,不需要全部满足分不够可以加吧
--先取最大,再取Detail不同的number絕對值
select *
from Detail a
where [Time]=(select max([Time]) from Detail where Bus_index=a.Bus_index )
and not exists(select 1 from Detail where Bus_index<>a.Bus_index and abs(number-a.number) <=5)
这个应该是查询到每辆车Time最大的记录吧。
貌似不行
[Time] <>a.[Time]这个判断条件加上后就没有结果显示了。 不加上可能出现相同的结果
4的查询好像没有达到我说的要求,
我把问题在清楚说一次。。表名:Detail
字段:bus_index(int),time(int),number(int)
查询 取出每个bus_index的一条记录(Time最大的)后,(Ps:只要一条。最大值的记录可能有2条)得到记录集后, 对其操作之留下满足下面的条件:
查询后得到的记录集中的任一一个NUMBER要在查询后得到的记录集中只要找出另外一个(只要一个)NUMBER与它的绝对值小于5
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 )
create table a(time int , number int ,bus_index int)
insert into a(time , number ,bus_index) values
select max(time)as time,number,bus_index from Detail order by number—測試:
create table a(time int , number int ,bus_index int)
insert into a values ('09','100','05')
insert into a values ('12','101','02')
insert into a values ('13','103','03')
insert into a values ('14','104','04')
insert into a values ('20','105','01')
select * from a t
where not exists(select 1 from a where bus_index=t.bus_index and time>t.time)
and abs(t.number-number) < 5
—運行結果:
time number bus_index
9 100 5
12 101 2
13 103 3
14 104 4
不知道對不對!
Inner JOin (Select distinct Bus_Index, Max(time) from detail Group by Bus_index) B ON A.Bus_Index=B.Bus_Index And A.Time=B.Time
Select A.* from #tmp A Where A.Bus_Index =(Select Top 1 BusIndex From #tmp Where Abs(A.Number-Number )<5) And (Select Count(1) From #tmp Where Abs(A.Number-Number )<5)=1
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 )