;with T as ( select row_number() over (partition by [货号] order by [进货日期] desc) as Row,* from [表格A] ) select * from T where Row = 1
with tablename as ( select 货号, max(进货日期) as max进货日期 from 表格A ) select 价格 from 表格A inner join tablename on tablename.货号 = 表格A.货号 where tablename.max进货日期 = 表格A.进货日期
with 那一段加个group with tablename as ( select 货号, max(进货日期) as max进货日期 from 表格A group by 货号 )
还用top来做。具体把数据和结果贴出来给语句
select distinct b.* from tb a cross apply (select top 1 * from tb where 货号=t.货号 order by 进货日期 desc)b
[code]怎么帖code啊,求教啊[/code]
select distinct b.* from tb a cross apply (select top 1 * from tb where 货号=a.货号 order by 进货日期 desc)b sorry
字段:货号,进货日期,价格select * from tb as a where not exists(select 1 from tb where 货号=a.货号 and 进货日期>a.进货日期)
declare @表A table (货号 varchar(8),上货日期 datetime,吊牌价 float) insert @表A select 'a','2012-01-05',45 union all select 'b','2012-01-05',66 union all select 'a','2012-08-15',11 union all select 'a','2012-05-5',88 union all select 'b','2012-12-01',55 select * from @表A select 货号,上货日期,吊牌价 from @表A tt where 上货日期>=(select top 1 上货日期 from @表A where 货号=tt.货号 order by 货号,上货日期 desc) 货号 上货日期 吊牌价 a 2012-01-05 00:00:00.000 45 b 2012-01-05 00:00:00.000 66 a 2012-08-15 00:00:00.000 11 a 2012-05-05 00:00:00.000 88 b 2012-12-01 00:00:00.000 55结果: 货号 上货日期 吊牌价 a 2012-08-15 00:00:00.000 11 b 2012-12-01 00:00:00.000 55
select * from thetable a where not exists (select 1 from thetable b where b.货号 = a.货号 and b.进货日期 > a.进货日期)
select distinct b.* from tb a cross apply (select top 1 * from tb where 货号=a.货号 order by 进货日期 desc)b
select row_number() over (partition by [货号] order by [进货日期] desc) as Row,* from [表格A]
)
select * from T where Row = 1
with tablename as
(
select 货号, max(进货日期) as max进货日期
from 表格A
)
select 价格
from 表格A
inner join tablename on tablename.货号 = 表格A.货号
where tablename.max进货日期 = 表格A.进货日期
with tablename as
(
select 货号, max(进货日期) as max进货日期
from 表格A
group by 货号
)
distinct b.*
from
tb a
cross apply
(select top 1 * from tb where 货号=t.货号 order by 进货日期 desc)b
select
distinct b.*
from
tb a
cross apply
(select top 1 * from tb where 货号=a.货号 order by 进货日期 desc)b
sorry
insert @表A select 'a','2012-01-05',45
union all
select 'b','2012-01-05',66
union all
select 'a','2012-08-15',11
union all
select 'a','2012-05-5',88
union all
select 'b','2012-12-01',55
select * from @表A
select 货号,上货日期,吊牌价 from @表A tt where 上货日期>=(select top 1 上货日期 from @表A where 货号=tt.货号 order by 货号,上货日期 desc)
货号 上货日期 吊牌价
a 2012-01-05 00:00:00.000 45
b 2012-01-05 00:00:00.000 66
a 2012-08-15 00:00:00.000 11
a 2012-05-05 00:00:00.000 88
b 2012-12-01 00:00:00.000 55结果:
货号 上货日期 吊牌价
a 2012-08-15 00:00:00.000 11
b 2012-12-01 00:00:00.000 55
from thetable a
where not exists (select 1
from thetable b
where b.货号 = a.货号
and b.进货日期 > a.进货日期)
select
distinct b.*
from
tb a
cross apply
(select top 1 * from tb where 货号=a.货号 order by 进货日期 desc)b