34,571
社区成员
发帖
与我相关
我的任务
分享
id productcode listdate quantity
1 001 2017-02-04 20
2 002 2017-02-05 30
3 001 2017-02-05 40
4 003 2017-01-02 50
5 002 2017-02-04 60
6 001 2017-02-03 70
id productcode listdate quantity
2 002 2017-02-05 30
3 001 2017-02-05 40
4 003 2017-01-02 50
--测试数据
if object_ID('tempdb..#tmp') is not null
drop table #tmp
Go
CREATE TABLE #tmp
(
id int,productcode varchar(200),listdate datetime,quantity decimal(18,4)
)
Insert #tmp
select 1,'001','2017-02-04', 20 UNION ALL
select 2,'002','2017-02-05', 30 UNION ALL
select 3,'001','2017-02-05', 40 UNION ALL
select 4,'003','2017-01-02', 50 UNION ALL
select 5,'002','2017-02-04', 60 UNION ALL
select 6,'001','2017-02-03', 70
--测试数据结束
;WITH temptab AS (
SELECT productcode ,
MAX(listdate) AS listdate
FROM #tmp
GROUP BY productcode
)
SELECT #tmp.*
FROM #tmp
INNER JOIN temptab ON temptab.listdate = #tmp.listdate
AND temptab.productcode = #tmp.productcode
ORDER BY id