try:
select * from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t1
(select no from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t where id='0604') t2
where abs(t1.no-t2.no)<2;
select * from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t1
(select no from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t where id='0604') t2
where abs(t1.no-t2.no)<2;
oracle 10g全文档
select row_number() over(order by gxsj) no,tbname.* from tbname
) t1,
(select no from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t where id='0604') t2
where abs(t1.no-t2.no)<2;
楼上正解
select id,
gxsj,
name,
value
from t
where t.rn >= ( select rn - 1 from t where t.id = '0604')
and t.rn <= ( select rn + 1 from t where t.id = '0604')
(SELECT ROWNUM rn
FROM (SELECT *
FROM table
ORDER BY gxsj)),
t2 AS
(SELECT rn
FROM (SELECT ROWNUM rn, id
FROM (SELECT *
FROM table
ORDER BY gxsj))
WHERE id = '0604')
SELECT id,gxsj,name,value
FROM t1
WHERE rn < (SELECT rn + 2 FROM t2)
AND rn > (SELECT rn - 2 FROM t2);
---------- ---------- -------------------- -------------------- -------------------- ----------
3 0023 2006-8-1 18:12:25 name3 value3 4
4 0604 2006-8-1 18:12:25 name4 value4 4
5 0405 2006-8-1 18:12:26 name5 value5 4
你可以将最外层的*替换成你需要的列名NO ID GXSJ NAME VALUE
select row_number() over(order by gxsj) no,tbname.* from tbname
) t1
(select no from (
select row_number() over(order by gxsj) no,tbname.* from tbname
) t where id='0604') t2
where abs(t1.no-t2.no)<2;