查找每个用户的前10条记录，sql语句怎么写

[springworker]

查找每个用户的前10条记录，sql语句怎么写

[卖水果的net]

-- 2005 + 可以使用这个方法
with m as (
select row_number() over(partition by userid order by mydate desc) rn , * from t 
)
select * from m where rn <= 10

[springworker]

提示这个错误是什么原因

[xiaoxiangqing]

参考楼上的.

[springworker]

我的数据表名称sold，字段有编号，和销售时间，我这样改不对吗 with Sold as ( select row_number() over(partition by 编号 order by 销售时间 desc) rn , * from Sold ) select * from Sold where rn <= 10

[chen8410]

select * from (
select row_number() over(partition by 编号 order by 销售时间 desc) rn , * from Sold  
) T
where rn <= 10

[Ginnnnnnnn]

with Sold1 --这里尽量不要和查询的目标表重名，报的就是这个错误，sql认为你是cte递归了 as ( select row_number() over(partition by 编号 order by 销售时间 desc) rn , * from Sold )