你给的结果应该是最大的那条记录 ---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([ITEM] varchar(1),[STORE] int,[StartDate] datetime,[EndDate] datetime) insert [tb] select 'A',381003,'2010-4-1','2010-6-1' union all select 'A',381003,'2010-6-2','2010-6-15' union all select 'B',381003,'2010-7-1','2010-12-1' union all select 'C',381003,'2010-8-1','2010-12-31' union all select 'D',381003,'2010-4-1','2010-9-21' union all select 'D',381003,'2010-7-1','2010-12-31'
---查询--- declare @Date datetime set @Date='2010-9-1' ;with josy as ( select item,store, startdate=case when @date between startdate and enddate then startdate end, enddate=case when @date between startdate and enddate then enddate end from tb ) select distinct * from josy t where not exists(select 1 from josy where item=t.item and store=t.store and enddate>t.enddate) ---结果--- item store startdate enddate ---- ----------- ----------------------- ----------------------- A 381003 NULL NULL B 381003 2010-07-01 00:00:00.000 2010-12-01 00:00:00.000 C 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000 D 381003 2010-07-01 00:00:00.000 2010-12-31 00:00:00.000(4 行受影响)
非常感谢,现在原始数据加入了E这个ITEM,看看能不能取消掉重复
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([ITEM] varchar(1),[STORE] int,[StartDate] datetime,[EndDate] datetime) insert [tb] select 'A',381003,'2010-04-01','2010-06-01' union all select 'A',381003,'2010-06-02','2010-06-15' union all select 'B',381003,'2010-07-01','2010-12-01' union all select 'C',381003,'2010-08-01','2010-12-31' union all select 'D',381003,'2010-04-01','2010-09-21' union all select 'D',381003,'2010-08-01','2010-12-31' union all select 'E',381003,'2010-08-01','2010-12-31' union all select 'E',381003,'2011-08-01','2011-12-31' go
---查询--- declare @Date datetime set @Date='2010-9-1' ;with josy as ( select item,store, startdate=case when @date between startdate and enddate then startdate end, enddate=case when @date between startdate and enddate then enddate end from tb ) select item,store,startdate,enddate from( select *,rn=row_Number() over(partition by item,store order by enddate desc) from josy ) t where rn=1---结果--- item store startdate enddate ---- ----------- ----------------------- ----------------------- A 381003 NULL NULL B 381003 2010-07-01 00:00:00.000 2010-12-01 00:00:00.000 C 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000 D 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000 E 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000(5 行受影响)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ITEM] varchar(1),[STORE] int,[StartDate] datetime,[EndDate] datetime)
insert [tb]
select 'A',381003,'2010-4-1','2010-6-1' union all
select 'A',381003,'2010-6-2','2010-6-15' union all
select 'B',381003,'2010-7-1','2010-12-1' union all
select 'C',381003,'2010-8-1','2010-12-31' union all
select 'D',381003,'2010-4-1','2010-9-21' union all
select 'D',381003,'2010-7-1','2010-12-31'
---查询---
declare @Date datetime
set @Date='2010-9-1'
;with josy as
(
select
item,store,
startdate=case when @date between startdate and enddate then startdate end,
enddate=case when @date between startdate and enddate then enddate end
from tb
)
select distinct *
from josy t
where not exists(select 1 from josy where item=t.item and store=t.store and enddate>t.enddate)
---结果---
item store startdate enddate
---- ----------- ----------------------- -----------------------
A 381003 NULL NULL
B 381003 2010-07-01 00:00:00.000 2010-12-01 00:00:00.000
C 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000
D 381003 2010-07-01 00:00:00.000 2010-12-31 00:00:00.000(4 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ITEM] varchar(1),[STORE] int,[StartDate] datetime,[EndDate] datetime)
insert [tb]
select 'A',381003,'2010-04-01','2010-06-01' union all
select 'A',381003,'2010-06-02','2010-06-15' union all
select 'B',381003,'2010-07-01','2010-12-01' union all
select 'C',381003,'2010-08-01','2010-12-31' union all
select 'D',381003,'2010-04-01','2010-09-21' union all
select 'D',381003,'2010-08-01','2010-12-31' union all
select 'E',381003,'2010-08-01','2010-12-31' union all
select 'E',381003,'2011-08-01','2011-12-31'
go
---查询---
declare @Date datetime
set @Date='2010-9-1'
;with josy as
(
select
item,store,
startdate=case when @date between startdate and enddate then startdate end,
enddate=case when @date between startdate and enddate then enddate end
from tb
)
select item,store,startdate,enddate
from(
select *,rn=row_Number() over(partition by item,store order by enddate desc)
from josy
) t
where rn=1---结果---
item store startdate enddate
---- ----------- ----------------------- -----------------------
A 381003 NULL NULL
B 381003 2010-07-01 00:00:00.000 2010-12-01 00:00:00.000
C 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000
D 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000
E 381003 2010-08-01 00:00:00.000 2010-12-31 00:00:00.000(5 行受影响)