ID CARCD ORDERCD INTIME OUTTIME
75 1200 81 2012-01-13 06:28:15.000 2012-01-13 06:30:15.000
76 1200 80 2012-01-13 07:29:47.000 2012-01-13 07:47:18.000
77 1201 81 2012-01-13 08:50:45.000 2012-01-13 08:58:15.000
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000
81 1200 81 2012-01-13 13:43:23.000 2012-01-13 13:49:54.000
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000
我要查询的结果是 ID CARCD ORDERCD INTIME OUTTIME
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000查询 筛选出 每个 CARCD 的ORDERNO为完整的 80 81 的两条数据 。
其他不完整数据丢弃。
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, CARCD int, ORDERCD int, INTIME datetime, OUTTIME datetime)
insert into #
select 75, 1200, 81, '2012-01-13 06:28:15.000', '2012-01-13 06:30:15.000' union all
select 76, 1200, 80, '2012-01-13 07:29:47.000', '2012-01-13 07:47:18.000' union all
select 77, 1201, 81, '2012-01-13 08:50:45.000', '2012-01-13 08:58:15.000' union all
select 78, 1200, 80, '2012-01-13 09:54:17.000', '2012-01-13 10:10:18.000' union all
select 79, 1200, 81, '2012-01-13 11:12:50.000', '2012-01-13 11:27:50.000' union all
select 80, 1201, 80, '2012-01-13 12:22:52.000', '2012-01-13 12:40:51.000' union all
select 81, 1200, 81, '2012-01-13 13:43:23.000', '2012-01-13 13:49:54.000' union all
select 82, 1201, 81, '2012-01-15 06:08:15.000', '2012-01-15 06:09:45.000' union all
select 83, 1200, 80, '2012-01-15 06:59:25.000', '2012-01-15 07:27:26.000' union all
select 84, 1200, 81, '2012-01-15 08:26:58.000', '2012-01-15 09:33:01.000';with cte as
(
select *, flag = row_number()over(partition by CARCD order by INTIME) - ORDERCD from #
)
select * from cte t where exists (select 1 from cte where CARCD=t.CARCD and flag=t.flag and ID<>t.ID)
/*
ID CARCD ORDERCD INTIME OUTTIME flag
----------- ----------- ----------- ----------------------- ----------------------- --------------------
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000 -77
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000 -77
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000 -74
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000 -74
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000 -78
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000 -78
*/
不一样的啊 flag 就不相等了 这个条件不能用了
不同的,我不知道他的规则是什么。ORDERCD(minV,maxV)这两个值如果是查询条件,那就简单,flag减ORDERCD变为减sign(ORDERCD-minV)如果是ORDERCD按INTIME顺序大于上记录,flag就不用减了,两个left join就解决问题了。
INTIME 是 没有顺序的 另外 ORDERCD 是 查询条件 但是 取值 两个 三个 都可能 比如 10 20 30
CARCD 是条件 ORDERCD 也是条件 如果 CARCD 是 1200 ORDERCD 是 10 20 30 的话 需要查出 所有 CARCD 为 1200 满足 10 20 30 的 三条记录 (上面例子 是 2 条记录)
create table rmi
(ID int, CARCD int, ORDERCD int, INTIME datetime, OUTTIME datetime)insert into rmi
select 75, 1200, 81, '2012-01-13 06:28:15.000', '2012-01-13 06:30:15.000' union all
select 76, 1200, 80, '2012-01-13 07:29:47.000', '2012-01-13 07:47:18.000' union all
select 77, 1201, 81, '2012-01-13 08:50:45.000', '2012-01-13 08:58:15.000' union all
select 78, 1200, 80, '2012-01-13 09:54:17.000', '2012-01-13 10:10:18.000' union all
select 79, 1200, 81, '2012-01-13 11:12:50.000', '2012-01-13 11:27:50.000' union all
select 80, 1201, 80, '2012-01-13 12:22:52.000', '2012-01-13 12:40:51.000' union all
select 81, 1200, 81, '2012-01-13 13:43:23.000', '2012-01-13 13:49:54.000' union all
select 82, 1201, 81, '2012-01-15 06:08:15.000', '2012-01-15 06:09:45.000' union all
select 83, 1200, 80, '2012-01-15 06:59:25.000', '2012-01-15 07:27:26.000' union all
select 84, 1200, 81, '2012-01-15 08:26:58.000', '2012-01-15 09:33:01.000'
with t as
( select row_number() over(partition by CARCD order by INTIME) rn, *
from rmi
)
select e.* from
(select a.ID, a.CARCD, a.ORDERCD, a.INTIME, a.OUTTIME
from t a
left join t b
on a.CARCD=b.CARCD and a.rn=b.rn-1
and a.ORDERCD=80 and b.ORDERCD=81
where b.ID is not null
union all
select b.ID, b.CARCD, b.ORDERCD, b.INTIME, b.OUTTIME
from t a
left join t b
on a.CARCD=b.CARCD and a.rn=b.rn-1
and a.ORDERCD=80 and b.ORDERCD=81
where b.ID is not null) e
order by e.IDID CARCD ORDERCD INTIME OUTTIME
----------- ----------- ----------- ----------------------- -----------------------
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000(6 row(s) affected)
先理解一下9楼代码,不明白的话上测试数据.
如果是这样的话,变通一下都不会吗--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, CARCD int, ORDERCD int, INTIME datetime, OUTTIME datetime)
insert into #
select 75, 1200, 81, '2012-01-13 06:28:15.000', '2012-01-13 06:30:15.000' union all
select 76, 1200, 80, '2012-01-13 07:29:47.000', '2012-01-13 07:47:18.000' union all
select 77, 1201, 81, '2012-01-13 08:50:45.000', '2012-01-13 08:58:15.000' union all
select 78, 1200, 80, '2012-01-13 09:54:17.000', '2012-01-13 10:10:18.000' union all
select 79, 1200, 81, '2012-01-13 11:12:50.000', '2012-01-13 11:27:50.000' union all
select 80, 1201, 80, '2012-01-13 12:22:52.000', '2012-01-13 12:40:51.000' union all
select 81, 1200, 81, '2012-01-13 13:43:23.000', '2012-01-13 13:49:54.000' union all
select 82, 1201, 81, '2012-01-15 06:08:15.000', '2012-01-15 06:09:45.000' union all
select 83, 1200, 80, '2012-01-15 06:59:25.000', '2012-01-15 07:27:26.000' union all
select 84, 1200, 81, '2012-01-15 08:26:58.000', '2012-01-15 09:33:01.000'declare @v1 int = 80
declare @v2 int = 81 -- @vn随你;with v as
(
select n=row_number()over(order by getdate()),* from (values(@v1),(@v2)) t(v) -- ORDERCD查询条件,顺序都随你调了: order by getdate()
),
cte as
(
select *, flag = row_number()over(partition by CARCD order by INTIME) - n from #, v where ORDERCD=v-- and CARCD=??
)
select ID,CARCD,ORDERCD,INTIME,OUTTIME from cte t where exists (select 1 from cte where CARCD=t.CARCD and flag=t.flag and ID<>t.ID)
/*
ID CARCD ORDERCD INTIME OUTTIME
----------- ----------- ----------- ----------------------- -----------------------
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000
*/
我说两个left join是不知道他有3个值,如果超过2个值就不是这样玩了,10个岂不玩残SQL。ORDERCD作为查询条件输入的话,#11的是通用的,N个都没问题。
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, CARCD int, ORDERCD int, INTIME datetime, OUTTIME datetime)
insert into #
select 75, 1200, 81, '2012-01-13 06:28:15.000', '2012-01-13 06:30:15.000' union all
select 76, 1200, 80, '2012-01-13 07:29:47.000', '2012-01-13 07:47:18.000' union all
select 77, 1201, 81, '2012-01-13 08:50:45.000', '2012-01-13 08:58:15.000' union all
select 78, 1200, 80, '2012-01-13 09:54:17.000', '2012-01-13 10:10:18.000' union all
select 79, 1200, 81, '2012-01-13 11:12:50.000', '2012-01-13 11:27:50.000' union all
select 80, 1201, 80, '2012-01-13 12:22:52.000', '2012-01-13 12:40:51.000' union all
select 81, 1200, 81, '2012-01-13 13:43:23.000', '2012-01-13 13:49:54.000' union all
select 82, 1201, 81, '2012-01-15 06:08:15.000', '2012-01-15 06:09:45.000' union all
select 83, 1200, 80, '2012-01-15 06:59:25.000', '2012-01-15 07:27:26.000' union all
select 84, 1200, 81, '2012-01-15 08:26:58.000', '2012-01-15 09:33:01.000'declare @v1 int = 80
declare @v2 int = 81
declare @v3 int -- @vn随你;with v as
(
select n=row_number()over(order by getdate()), cnv=count(v)over(), v from (values(@v1),(@v2),(@v3)) t(v) -- ORDERCD查询条件,顺序都随你调了: order by getdate()
where v is not null
), cte as
(
select *, flag = row_number()over(partition by CARCD order by INTIME) - n from #, v where ORDERCD=v-- and CARCD=??
), rs as
(
select *, cn=count(1)over(partition by CARCD,flag) from cte
)
select ID,CARCD,ORDERCD,INTIME,OUTTIME from rs where cn = cnv
/*
ID CARCD ORDERCD INTIME OUTTIME
----------- ----------- ----------- ----------------------- -----------------------
78 1200 80 2012-01-13 09:54:17.000 2012-01-13 10:10:18.000
79 1200 81 2012-01-13 11:12:50.000 2012-01-13 11:27:50.000
83 1200 80 2012-01-15 06:59:25.000 2012-01-15 07:27:26.000
84 1200 81 2012-01-15 08:26:58.000 2012-01-15 09:33:01.000
80 1201 80 2012-01-13 12:22:52.000 2012-01-13 12:40:51.000
82 1201 81 2012-01-15 06:08:15.000 2012-01-15 06:09:45.000
*/