也算是编过几年程序的，这个问题也没有搞定真是汗…… help!

[xiaoyan21]

找出一段时间内某产品的最近一次售价。
即，按时间倒序，找出每种产品的最后一次售价，产品去除重复行


产品编号 币种 售价 日期
p1001 RMB 18.2 2015-5-7 08:45:09
p1002 EUR 3.05 2015-5-7 12:30:00
p1001 RMB 21.3 2015-7-7 09:45:19
------------------------------------------------------------------
declare @dt1 dateTime
declare @dt2 dateTime
set @dt1 = '2015-01-01'
set @dt2 = GETDATE()
with cte1
as
(
--产品编号 , 币种, 售价, 日期
select pcode,moneyType,sellPrice,submitDate,
ROW_NUMBER() OVER(Order by submitDate) as id
from billDetail
where submitDate between @dt1 and @dt2
),
cte2
as
(
select max(id) as 'tempID' from cte1
group by pcode,moneyType
)
select pcode,moneyType,sellPrice from cte1
where id in (select tempID from cte2)
------------------------------------------
本为信心满满的，对以上代码感良好。
一调试结果大出所料，产品不能去除重复行，而且每次运行的结果居然都不相同……
这是什么情况？

[道玄希言]

declare @dt1 dateTime
declare @dt2 dateTime
set @dt1 = '2015-01-01'
set @dt2 = GETDATE()
;with cte1
  as
  (
--产品编号 , 币种, 售价, 日期
select pcode,moneyType,sellPrice,submitDate,
ROW_NUMBER() OVER(partition by pcode Order by submitDate desc, sellPrice) as id
from billDetail
where submitDate between @dt1 and @dt2
  )
select * from cte1 where id = 1

[Landa_Tiny_cui]

我也运行了你的代码 除了分组多了一个moneytype,写的没有问题，但两个CTE可以合成一个，如楼上

 
CREATE TABLE billdetail
(
		pcode VARCHAR(50),
		moneyType VARCHAR(50),
		sellPrice NUMERIC(18,2),
		submitDate DATETIME
)
INSERT INTO billdetail
SELECT  'p1003'  ,  'RM'     ,181222.2,    '2015-7-15 08:45:09' UNION ALL
select 'p1002' ,   'EUR',     3.05  ,  '2015-5-7 12:30:00' UNION ALL
SELECT  'p1001',    'RMB',     21.3  , '2015-7-7 09:45:19'
 
declare @dt1 dateTime
declare @dt2 dateTime
set @dt1 = '2015-01-01'
set @dt2 = GETDATE()
;with cte1
  as
  (
--产品编号 , 币种, 售价, 日期
select pcode,moneyType,sellPrice,submitDate,
ROW_NUMBER() OVER(partition by pcode,[moneyType] Order by submitDate desc) as id
from billDetail
where submitDate between @dt1 and @dt2
  )
select * from cte1 where id = 1