查询记有ROW_NUMBER() 怎么再过滤重复呢

查询记有ROW_NUMBER() 如何再过滤重复呢？

ROW_NUMBER() over(order by [id] desc) 并且还有join 建立关系如何过滤重复。

SQL code

select * from                             (select                              ROW_NUMBER() over(order by [pictures].[id] desc)as rownum,                                                         --这个无效distinct [pictures].[id],                             [pictures].[u_id],                             [pictures].[title],                             [pictures].[path],                             [pictures].[postdatetime]                             from [pictures]                             join [picturestags] on [picturestags].[p_id]=[pictures].[id]                             join [tags] on [tags].[id]=[picturestags].[t_id]                             where [tags].[name] like @query)as pictures                             Order by [postdatetime] desc

SQL code

--try;with f as(select * from                             (select                              ROW_NUMBER() over(order by [pictures].[id] desc)as rownum,                                                         --这个无效distinct [pictures].[id],                             [pictures].[u_id],                             [pictures].[title],                             [pictures].[path],                             [pictures].[postdatetime]                             from [pictures]                             join [picturestags] on [picturestags].[p_id]=[pictures].[id]                             join [tags] on [tags].[id]=[picturestags].[t_id]                             where [tags].[name] like @query)as pictures)select * from f t where [rownum]=(select max([rownum]) from f where postdatetime=t.postdatetime)
[解决办法]
group by
[解决办法]
SQL code
 ;with aaa as (select  ROW_NUMBER() over(order by [pictures].[id] desc)as rownum, [pictures].[u_id], [pictures].[title], [pictures].[path], [pictures].[postdatetime] from [pictures] join [picturestags] on [picturestags].[p_id]=[pictures].[id] join [tags] on [tags].[id]=[picturestags].[t_id] where [tags].[name] like @query) select * from aaa as a where not exists (select 1 from aaa  where aaa.u_id=a.u_id and aaa.title=a.title and aaa.[path]=a.[path] and aaa.postdatetime=a.postdatetime and aaa.rownum<a.rownum)