按条件分组后取其中某一行

ALTER VIEW [dbo].[vi_dx_xsprice_1]
as
select * from (select ROW_NUMBER() over (partition by ccuscode,cinvcode order by ccode desc) as rownum,ccode,ccuscode,ccusname,cinvcode,cinvname,cguige,cdanwei,ccusprice,cprice,ddate from vi_dx_xsprice_1list) t where t.rownum='1'

GO

 

注意：别名不能作为where查询条件。