请问,比如我有一个表
year month player plays
2005 1 1 20
2005 1 2 30
2005 3 1 20 这样,就是说,有不同的年份,不同的月份,不同的player,如果我想查询每年每月plays次数最多两个的player,我想使用row_number() over (partition ...)比较合适, 但是这里,我不确定partition 应该对哪个字段进行处理? 是对player处理? 还是partition by year, month两个字段一起处理??? 谁能解释一下? 谢谢!
year month player plays
2005 1 1 20
2005 1 2 30
2005 3 1 20 这样,就是说,有不同的年份,不同的月份,不同的player,如果我想查询每年每月plays次数最多两个的player,我想使用row_number() over (partition ...)比较合适, 但是这里,我不确定partition 应该对哪个字段进行处理? 是对player处理? 还是partition by year, month两个字段一起处理??? 谁能解释一下? 谢谢!
insert @t select 2005, 1, 1, 20
insert @t select 2005, 1, 1, 30
insert @t select 2005, 1, 1, 40
insert @t select 2005, 1, 1, 20
insert @t select 2005, 1, 2, 30
insert @t select 2005, 3, 1, 20
select * from (
select *,px = row_number() over(partition by [year], [month] order by plays desc) from @t) t
where px<3year month player plays px
----------- ----------- ----------- ----------- --------------------
2005 1 1 40 1
2005 1 1 30 2
2005 3 1 20 1(3 行受影响)要年月同时分组
insert @t select 2005, 1, 1, 20
insert @t select 2005, 1, 1, 30
insert @t select 2005, 1, 1, 40
insert @t select 2005, 1, 1, 20
insert @t select 2005, 1, 2, 30
insert @t select 2005, 3, 1, 20
select * from (
select *,px = row_number() over(partition by [year], [month] order by plays desc) from @t) t
where px<3year month player plays px
----------- ----------- ----------- ----------- --------------------
2005 1 1 40 1
2005 1 1 30 2
2005 3 1 20 1(3 行受影响)要年月同时分组