SQL 与此同时查询出目标数据的前后两条数据 #100%结贴

2013-09-28

SQL 同时查询出目标数据的前后两条数据 ####################100%结贴如下图，我的查询语句是：SQL子查询数

SQL 同时查询出目标数据的前后两条数据 ####################100%结贴
如下图，我的查询语句是：

SQL 子查询数据
[解决办法]
你把那个日期改成变量，加上自己的条件就可以了

----------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-23 17:47:34
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[T]
if object_id('[T]') is not null drop table [T]
go
create table [T]([id] int,[i_op_id] varchar(9),[i_datetime] varchar(10),[i_op_situs_no] datetime)
insert [T]
select 10500,'转帐:入金','2013-08-02',' 16:36:02.623' union all
select 10501,'转帐:入金','2013-08-02',' 16:39:45.233' union all
select 10502,'转帐:出金','2013-08-02','16:44:36.733' union all
select 10503,'转帐:入金','2013-08-02',' 16:45:58.357' union all
select 10504,'转帐:出金','2013-08-02','16:46:10.873' union all
select 10505,'转帐:入金','2013-08-02',' 16:54:46.357' union all
select 10506,'转帐:出金','2013-08-02','16:55:12.937' union all
select 10507,'转帐:入金','2013-08-02',' 16:58:17.547' union all
select 10605,'转帐:入金','2013-08-05',' 10:37:56.437' union all
select 10606,'转帐:入金','2013-08-05',' 10:45:54.467' union all
select 10607,'转帐:出金','2013-08-05','10:49:35.107' union all
select 10608,'转帐:出金','2013-08-05','10:49:51.187' union all
select 10609,'转帐:出金','2013-08-05','10:50:01.217' union all
select 10734,'转帐:入金','2013-08-08',' 10:31:58.680' union all
select 10735,'转帐:出金','2013-08-08','10:33:44.543' union all
select 10736,'转帐:出金','2013-08-08','10:34:03.610' union all
select 10742,'转帐:入金','2013-08-08',' 13:48:15.300' union all
select 10743,'转帐:入金','2013-08-08',' 14:01:10.020' union all
select 10744,'转帐:入金','2013-08-08',' 14:01:19.007' union all
select 10745,'转帐:入金','2013-08-08',' 14:02:11.123' union all
select 10746,'转帐:入金','2013-08-08',' 14:02:28.673' union all
select 10747,'转帐:入金','2013-08-08',' 14:07:43.147' union all
select 10748,'转帐:入金','2013-08-08',' 14:08:02.760' union all
select 10749,'转帐:入金','2013-08-08',' 14:08:17.747' union all
select 10751,'转帐:出金','2013-08-08','14:11:34.253' union all
select 10752,'转帐:出金','2013-08-08','14:14:56.310' union all
select 10754,'转帐:入金','2013-08-08',' 14:33:37.460' union all
select 10756,'转帐:出金','2013-08-08','14:34:48.453' union all
select 10758,'转帐:入金','2013-08-08',' 15:03:34.820' union all
select 10760,'转帐:入金','2013-08-08 ','15:03:52.790' union all
select 10762,'转帐:出金','2013-08-08','15:21:00.213' union all
select 10764,'转帐:入金','2013-08-08 ','15:22:48.507' union all
select 10954,'转帐:入金','2013-08-15',' 10:12:39.690' union all
select 10956,'转帐:出金','2013-08-15','10:14:10.577' union all
select 10979,'转帐:入金','2013-08-15',' 10:33:41.383' union all
select 10981,'转帐:出金','2013-08-15','10:34:47.783' union all
select 11030,'转帐:入金','2013-08-16',' 11:06:44.260' union all
select 11031,'转帐:入金','2013-08-16 ','11:06:45.683' union all
select 11032,'转帐:入金','2013-08-16',' 11:06:47.653' union all
select 11034,'转帐:入金','2013-08-16',' 11:08:17.360' union all
select 11036,'转帐:入金','2013-08-16',' 11:08:21.267' union all
select 11040,'转帐:出金','2013-08-16','11:30:55.503' union ALL

select 11041,'转帐:出金','2013-08-17','11:30:55.503'union all
select 11050,'转帐:入金','2013-08-16 ','17:32:26.527' union all
select 11195,'转帐:出金','2013-08-21','10:32:09.730' union all
select 11210,'转帐:入金','2013-08-21 ','15:17:05.060' union all
select 11212,'转帐:出金','2013-08-21','15:23:49.370' union all
select 11214,'转帐:入金','2013-08-21 ','15:57:10.183' union all

select 11268,'转帐:入金','2013-08-22',' 15:13:45.933' union all
select 11270,'转帐:入金','2013-08-22 ','16:34:04.620' union all
select 11272,'转帐:出金','2013-08-22','16:34:46.717' union all
select 11274,'转帐:出金','2013-08-22','16:44:39.747' union all
select 11406,'转帐:入金','2013-08-26',' 17:01:10.507' union all
select 11537,'转帐:入金','2013-08-30',' 11:08:16.970' union all
select 11541,'转帐:出金','2013-08-30','11:10:38.833' union all
select 11697,'转帐:入金','2013-09-04','15:19:03.453'
--------------开始查询--------------------------

select * from [T]a
WHERE EXISTS(SELECT 1 FROM (SELECT id FROM [t] where i_datetime BETWEEN '2013-08-16' AND '2013-08-16') b WHERE a.id BETWEEN b.id-1 AND b.id+1)

--select * from [T]a

----------------结果----------------------------
/*

*/

[解决办法]

sselect
 b.* 
from
 oplogsTb a,oplogsTb b
where
 a.i_datetime > '2013-07-07' and  a.i_op_id like '%转帐%'
and
 abs(a.id-b.id)<=1

热点排行