比如: SELECT TOP 1 * FROM GuestBook WHERE (Ip = N'192.168.0.1') UNION ALL SELECT TOP 1 * FROM GuestBook WHERE (Ip = N'192.168.0.1') AND id > (SELECT TOP 1 id FROM GuestBook WHERE (Ip = N'192.168.0.1')) UNION ALL SELECT TOP 1 * FROM GuestBook WHERE (Ip = N'192.168.0.1') AND id < (SELECT TOP 1 id FROM GuestBook WHERE (Ip = N'192.168.0.1'))
楼上的 应该是这样子吧?SELECT TOP 1 * FROM GuestBook WHERE (Ip = N'192.168.0.1') UNION ALL SELECT TOP 1 * FROM GuestBook WHERE id > (SELECT TOP 1 id FROM GuestBook WHERE (Ip = N'192.168.0.1')) UNION ALL SELECT TOP 1 * FROM GuestBook WHERE id < (SELECT TOP 1 id FROM GuestBook WHERE (Ip = N'192.168.0.1'))
就是 SELECT 出符合条件的记录附近的前后包括本条记录? 比如有个表:spkfk 条件:spbh='aaa'select identity(int,1,1) as sn,spkfk.* into ##aa from spkfk declare @a int select @a=sn from ##aa where spbh ='aaa'select * from ##aa where sn=@a union select * from ##aa where sn=@a-1 union select * from ##aa where sn=@a+1
多谢楼上的几位了,不过有没有不借助于 Identity 的办法?
用我的试试:以Northwind数据库中的Orders表为例,获取以10330为中心的前后各10条数据。DECLARE @TargetOrder intSET @TargetOrder=10330 select *FROM orders WHERE OrderId=@TargetOrder OR OrderId in(SELECT top 10 OrderId FROM orders WHERE OrderId < @TargetOrder order by OrderId desc)OR OrderId in(SELECT top 10 OrderId FROM orders WHERE OrderId > @TargetOrder order by OrderId)
整个表格查询出来,也是符合条件的记录附近的前后包括本条记录?!
http://www.microsoft.com/china/sql/using/tips/development/setbasedrows.asp
SELECT TOP 1 *
FROM GuestBook
WHERE (Ip = N'192.168.0.1')
UNION ALL
SELECT TOP 1 *
FROM GuestBook
WHERE (Ip = N'192.168.0.1') AND id >
(SELECT TOP 1 id
FROM GuestBook
WHERE (Ip = N'192.168.0.1'))
UNION ALL
SELECT TOP 1 *
FROM GuestBook
WHERE (Ip = N'192.168.0.1') AND id <
(SELECT TOP 1 id
FROM GuestBook
WHERE (Ip = N'192.168.0.1'))
应该是这样子吧?SELECT TOP 1 *
FROM GuestBook
WHERE (Ip = N'192.168.0.1')
UNION ALL
SELECT TOP 1 *
FROM GuestBook
WHERE id >
(SELECT TOP 1 id
FROM GuestBook
WHERE (Ip = N'192.168.0.1'))
UNION ALL
SELECT TOP 1 *
FROM GuestBook
WHERE id <
(SELECT TOP 1 id
FROM GuestBook
WHERE (Ip = N'192.168.0.1'))
比如有个表:spkfk 条件:spbh='aaa'select identity(int,1,1) as sn,spkfk.* into ##aa from spkfk
declare @a int
select @a=sn from ##aa where spbh ='aaa'select * from ##aa where sn=@a
union
select * from ##aa where sn=@a-1
union
select * from ##aa where sn=@a+1
不然,你取的数据就像是随机取数据一样,何况,这样取好像没有什么实际意义! 因为你现在取的A记录前后的记录可能是B和C,但如果有数据插入,下次取A记录前后的可能是C和D记录!也就是说物理位置也在发生变化!
另外,楼上说的 邹捷 是谁呢?恕我孤陋寡闻了。:-)