sqlserver2005,比如表#t1,数量如下:
code listdate price
001 2011-10-09 20
006 2011-08-03 30
001 2011-08-02 12
003 2011-07-03 14
006 2011-07-31 18我想取出表#t1的listdate最后一次的价格取出来,比如取出数据如下:code listdate price
001 2011-10-09 20
003 2011-07-03 14
006 2011-08-03 30如何写sql语句?
code listdate price
001 2011-10-09 20
006 2011-08-03 30
001 2011-08-02 12
003 2011-07-03 14
006 2011-07-31 18我想取出表#t1的listdate最后一次的价格取出来,比如取出数据如下:code listdate price
001 2011-10-09 20
003 2011-07-03 14
006 2011-08-03 30如何写sql语句?
select *
from tb t
where not exists (select 1 from tb where code = t.code and listdate > t.listdate)
go
--> -->
if not object_ID(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([code] nvarchar(3),[listdate] Datetime,[price] int)
Insert #T
select N'001','2011-10-09',20 union all
select N'006','2011-08-03',30 union all
select N'001','2011-08-02',12 union all
select N'003','2011-07-03',14 union all
select N'006','2011-07-31',18
Go
--II、[code]相同[listdate]最大的记录
--方法1:
Select * from #T a where not exists(select 1 from #T where [code]=a.[code] and [listdate]>a.[listdate])--方法2:
select a.* from #T a join (select max([listdate])[listdate],[code] from #T group by [code]) b on a.[code]=b.[code] and a.[listdate]=b.[listdate] order by [listdate]--方法3:
select * from #T a where [listdate]=(select max([listdate]) from #T where [code]=a.[code]) order by [listdate]--方法4:
select a.* from #T a join #T b on a.[code]=b.[code] and a.[listdate]<=b.[listdate] group by a.[listdate],a.[code],a.[price] having count(1)=1 --方法5:
select * from #T a group by [listdate],[code],[price] having [listdate]=(select max([listdate])from #T where [code]=a.[code])--方法6:
select * from #T a where (select count(1) from #T where [code]=a.[code] and [listdate]>a.[listdate])=0--方法7:
select * from #T a where [listdate]=(select top 1 [listdate] from #T where [code]=a.[code] order by [listdate] desc)--方法8:
select * from #T a where [listdate]!<all(select [listdate] from #T where [code]=a.[code])--方法9(注:[listdate]为唯一时可用):
select * from #T a where [listdate] in(select max([listdate]) from #T group by [code])--SQL2005:--方法10:
select [listdate],[code],[price] from (select *,max([listdate])over(partition by [code]) as Minlistdate from #T a)T where [listdate]=Minlistdate--方法11:
select [listdate],[code],[price] from (select *,row_number()over(partition by [code] order by [listdate] desc) as Minlistdate from #T a)T where Minlistdate=1
drop table t1
go
create table t1(code varchar(10), listdate datetime,price float)
go
insert t1
select '001','2011-10-09',20 union all
select '006','2011-08-03',30 union all
select '001','2011-08-02',12 union all
select '003','2011-07-03',14 union all
select '006','2011-07-31',20
go
select * from t1 as a where
not exists (select 1 from t1 where a.code=code and a.listdate<listdate)
order by code asc
go
drop table t1
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
code varchar(10),
listdate varchar(10),
price int
)
go
insert into tb
select '001','2011-10-09',20 union all
select '006','2011-08-03',30 union all
select '001','2011-08-02',12 union all
select '003','2011-07-03',14 union all
select '006','2011-07-31',18
go
select * from tb a where not exists(select 1 from tb where code=a.code and listdate>a.listdate) order by code
/*
code listdate price
---------- ---------- -----------
001 2011-10-09 20
003 2011-07-03 14
006 2011-08-03 30(3 行受影响)*/