if object_id('[TBB]') is not null drop table [TBB] create table [TBB] (id int,供应商 varchar(1),物料 varchar(2),日期 varchar(3)) insert into [TBB] select 1,'a','aa','5-5' union all select 2,'a','aa','5-4' union all select 3,'b','bb','5-3' union all select 4,'b','bb','5-5' union all select 5,'b','aa','5-6'SELECT A.* FROM TBB A WHERE NOT EXISTS ( SELECT 1 FROM TBB B WHERE A.供应商 = B.供应商 AND A.物料 = B.物料 AND A.日期 < B.日期 )/* id 供应商 物料 日期 1 a aa 5-5 4 b bb 5-5 5 b aa 5-6*/
select * from 表 a where 日期=(select max(日期) from 表 where a.供应商=供应商)
SELECT [供应商],[物料],[日期] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY [供应商],[物料] ORDER BY dbo.GETDATE() DESC) AS id ,[供应商],[物料],[日期] FROM #t )AS a WHERE a.id=1
WITH a1 (id,供应商,物料,日期) AS ( select 1,'a','aa','5-5' union all select 2,'a','aa','5-4' union all select 3,'b','bb','5-3' union all select 4,'b','bb','5-5' union all select 5,'b','aa','5-6' ) SELECT * FROM a1 a WHERE id=(SELECT TOP 1 id FROM a1 WHERE 供应商=a.供应商 AND 物料=a.物料 ORDER BY 日期 DESC)
create table [TBB] (id int,供应商 varchar(1),物料 varchar(2),日期 varchar(3))
insert into [TBB]
select 1,'a','aa','5-5' union all
select 2,'a','aa','5-4' union all
select 3,'b','bb','5-3' union all
select 4,'b','bb','5-5' union all
select 5,'b','aa','5-6'SELECT A.*
FROM TBB A
WHERE NOT EXISTS ( SELECT 1
FROM TBB B
WHERE A.供应商 = B.供应商
AND A.物料 = B.物料
AND A.日期 < B.日期 )/*
id 供应商 物料 日期
1 a aa 5-5
4 b bb 5-5
5 b aa 5-6*/
select * from 表 a
where 日期=(select max(日期) from 表 where a.供应商=供应商)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [供应商],[物料] ORDER BY dbo.GETDATE() DESC) AS id
,[供应商],[物料],[日期]
FROM #t
)AS a
WHERE a.id=1
WITH a1 (id,供应商,物料,日期) AS
(
select 1,'a','aa','5-5' union all
select 2,'a','aa','5-4' union all
select 3,'b','bb','5-3' union all
select 4,'b','bb','5-5' union all
select 5,'b','aa','5-6'
)
SELECT *
FROM a1 a
WHERE id=(SELECT TOP 1 id FROM a1 WHERE 供应商=a.供应商 AND 物料=a.物料 ORDER BY 日期 DESC)