34,593
社区成员
发帖
与我相关
我的任务
分享
select t.* from 采购记录表 t where 采购日期 = (select max(采购日期) from 采购记录表 where 编号 = t.编号) order by t.编号
select t.* from 采购记录表 t where not exists(select 1 from 采购记录表 where 编号 = t.编号 and 采购日期 > t.采购日期) order by t.编号
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
商品编号 varchar(10),
采购日期 varchar(10),
采购价 int
)
go
insert into tb
select '10001','2011-02-03',10 union all
select '10002','2011-02-03',15 union all
select '10001','2011-03-05',12 union all
select '10003','2011-03-05',14 union all
select '10002','2011-03-14',16 union all
select '10003','2011-03-14',13
go
select * from tb a where not exists(select 1 from tb where 商品编号=a.商品编号 and 采购日期>a.采购日期)
/*
商品编号 采购日期 采购价
---------- ---------- -----------
10001 2011-03-05 12
10002 2011-03-14 16
10003 2011-03-14 13
(3 行受影响)
*/
SELECT [商品编号],[采购日期],[采购价]
FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [商品编号] ORDER BY [采购日期] desc) AS row FROM #T)t
WHERE row=1
select * from t1 a where
[日期]=(select max([日期]) from t1 where a.[商品编号]=[商品编号])
Select * from tb t WHERE 采购日期=(SELECT MAX(采购日期) FROM tb WHERE 商品编号=t.商品编号)
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([商品编号] int,[采购日期] Datetime,[采购价] int)
Insert #T
select 10001,'2011-02-03',10 union all
select 10002,'2011-02-03',15 union all
select 10001,'2011-03-05',12 union all
select 10003,'2011-03-05',14 union all
select 10002,'2011-03-14',16 union all
select 10003,'2011-03-14',13
Go
--方法2
Select * from #T AS a WHERE [采购日期]=(SELECT MAX([采购日期]) FROM #T WHERE [商品编号]=a.[商品编号])
--方法3
SELECT [商品编号],[采购日期],[采购价]
FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [商品编号] ORDER BY [采购日期] desc) AS row FROM #T)t
WHERE row=1
/*
商品编号 采购日期 采购价
10001 2011-03-05 00:00:00.000 12
10002 2011-03-14 00:00:00.000 16
10003 2011-03-14 00:00:00.000 13
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([商品编号] int,[采购日期] Datetime,[采购价] int)
Insert #T
select 10001,'2011-02-03',10 union all
select 10002,'2011-02-03',15 union all
select 10001,'2011-03-05',12 union all
select 10003,'2011-03-05',14 union all
select 10002,'2011-03-14',16 union all
select 10003,'2011-03-14',13
Go
Select * from #T AS a WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [商品编号]=a.[商品编号] AND [采购日期]>a.[采购日期])
/*
商品编号 采购日期 采购价
10001 2011-03-05 00:00:00.000 12
10002 2011-03-14 00:00:00.000 16
10003 2011-03-14 00:00:00.000 13
*/