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
[tags].[name] 如果有多个字段相似 就够 模糊查询出多个相同结果。 如果过滤 多个相同结果并且显示
------解决方案--------------------
用条件找出唯一。
------解决方案--------------------
- 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)