34,838
社区成员




USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[日期] DATETIME
,[单号] VARCHAR(10)
,[类型] NVARCHAR(10)
,[品号] NVARCHAR(10)
,[单价] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2020-08-03',N'A001',N'外购',N'10010102',N'10')
INSERT INTO dbo.[t] VALUES(N'2020-08-03',N'A002',N'自制',N'10010103',N'11')
INSERT INTO dbo.[t] VALUES(N'2020-08-03',N'A003',N'自制',N'10010104',N'12')
INSERT INTO dbo.[t] VALUES(N'2020-08-03',N'A004',N'外购',N'10010105',N'13')
INSERT INTO dbo.[t] VALUES(N'2020-08-04',N'A001',N'外购',N'10010101',N'14')
INSERT INTO dbo.[t] VALUES(N'2020-08-04',N'A002',N'外购',N'10010101',N'15')
INSERT INTO dbo.[t] VALUES(N'2020-08-04',N'A003',N'自制',N'10010104',N'16')
INSERT INTO dbo.[t] VALUES(N'2020-08-04',N'A004',N'外购',N'10010105',N'17')
---------- 以上为测试表及测试数据 -------------
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY 类型,品号 ORDER BY 日期 DESC,单号 DESC) AS rid,*
FROM dbo.t
) AS tt
WHERE tt.rid=1
ORDER BY 日期,单号