记录如下:
品名 开始日期 截止日期 单价
a 2006-01-02 2012-8-31 2.01
a 2012-9-1 2012-10-31 3.51
b 2006-01-10 2012-8-31 2.55
b 2012-9-01 2012-9-30 3.00
b 2012-10-1 2012-10-31 2.00
c 2006-01-07 NULL 8.55
c 2012-01-1 NULL 8.55要求,根据开始日期和截止日期取最新单价记录,
如果截止日期为空,则根据开始日期取最新记录,如果截止日期不为空,则根据开始日期和截止日期取最新记录请问这样的条件如何设定?谢谢!数据脚本declare @t table(品名 varchar(10),开始日期 varchar(10),截止日期 varchar(10), 单价 dec(6,2))
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55
品名 开始日期 截止日期 单价
a 2006-01-02 2012-8-31 2.01
a 2012-9-1 2012-10-31 3.51
b 2006-01-10 2012-8-31 2.55
b 2012-9-01 2012-9-30 3.00
b 2012-10-1 2012-10-31 2.00
c 2006-01-07 NULL 8.55
c 2012-01-1 NULL 8.55要求,根据开始日期和截止日期取最新单价记录,
如果截止日期为空,则根据开始日期取最新记录,如果截止日期不为空,则根据开始日期和截止日期取最新记录请问这样的条件如何设定?谢谢!数据脚本declare @t table(品名 varchar(10),开始日期 varchar(10),截止日期 varchar(10), 单价 dec(6,2))
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55; with TES as (select *,ROW_NUMBER() over(partition by 品名 order by (cast(开始日期 as datetime))) as rowid from @t)
select a.*
from TES as a inner join (select 品名,MAX(rowid) as rowid from TES group by 品名) as b on a.品名=b.品名 and a.rowid=b.rowid
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55 select t.* from
(
select *,rn=row_number()over(partition by 品名 order by cast(开始时间 as datetime),cast(isnull(截止日期,'0') as datetime) desc) from @t
)as t
where rn=1
品名 开始日期 截止日期 单价
a 2006-01-02 2012-8-31 2.01
a 2012-9-1 2012-10-31 3.51
b 2006-01-10 2012-8-31 2.55
b 2012-9-01 2012-10-31 3.00
b 2012-11-1 2012-12-31 2.00
c 2006-01-07 NULL 8.55
c 2012-01-1 NULL 8.55如果品名b日期改一下(蓝色部分),您的代码就不行了,
我表述也有问题,不应是最新单价,应该是最适合当前日期的单价
declare @t table(品名 varchar(10),开始日期 varchar(10),截止日期 varchar(10), 单价 dec(6,2))
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55 SELECT *
FROM @t t
WHERE 截止日期 IS NULL
AND 开始日期 = (SELECT MAX(CAST(开始日期 AS DATETIME)) from @t where 品名 = t.品名) UNION ALLSELECT *
FROM @t t
WHERE 截止日期 IS NOT NULL
AND 截止日期 = (SELECT MAX(CAST(截止日期 AS DATETIME)) from @t where 品名 = t.品名)
(7 行受影响)
品名 开始日期 截止日期 单价
---------- ---------- ---------- ---------------------------------------
c 2012-01-1 NULL 8.55
a 2012-9-1 2012-10-31 3.51
b 2012-10-1 2012-10-31 2.00
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)
insert into @t select 'a', '2006-01-02' ,'2012-8-31', 2.01
union all select 'a', '2012-9-1' ,'2012-10-31', 3.51
union all select 'b', '2006-01-10' ,'2012-8-31', 2.55
union all select 'b', '2012-9-1' ,'2012-9-30', 3.00
union all select 'b', '2012-10-1' ,'2012-10-31', 2.00
union all select 'c', '2006-01-07' ,null, 8.55
union all select 'c', '2012-01-1' ,null, 8.55; with TES as (
select * ,ROW_NUMBER() over(partition by 品名 order by (cast(开始日期 as datetime))) as rowid
from @t where 开始日期<=GETDATE() and (cast(截止日期 as datetime)+1>=GETDATE() or 截止日期 is null)) select a.*
from TES as a inner join (select 品名,MAX(rowid) as rowid from TES group by 品名) as b on a.品名=b.品名 and a.rowid=b.rowid
(cast(截止日期 as datetime)+1 加1是什么意思啊?
(cast(截止日期 as datetime)+1 等同于dateadd(day,1,cast(截止日期 as datetime))
这样就可以取到截止日期当天的记录了