userID dateYM number
001 200901 50
001 200902 60
002 200901 20
002 200902 70
003 200903 80
003 200903 100以上数据,想要查出某用户在某月下的number排名。
在网上查了好多,都实现不了我想要的,请各位帮忙!!谢谢!!
001 200901 50
001 200902 60
002 200901 20
002 200902 70
003 200903 80
003 200903 100以上数据,想要查出某用户在某月下的number排名。
在网上查了好多,都实现不了我想要的,请各位帮忙!!谢谢!!
FROM (SELECT user_id, dateym,
DENSE_RANK () OVER (PARTITION BY dateym ORDER BY "a.number" DESC)
rn
FROM a
WHERE dateym = '200903')
WHERE user_id = '003'
from (select userID,
dateYM,
number,
row_number() over(partition by dateYMorder by number ) number_order
from t
where dateYM = 'your dateYM')
where userID = 'your userId'