0

## SQL 每月每日結算排名Ranking

Ks 2020-09-27 13:26:451276 瀏覽

daily profit table結構 這個表回去結算每天的daily profit

1       testuser01    20            2020/09/27 14:00:00
2       testuser02    30            2020/09/27 14:00:00
3       testuser03    40            2020/09/27 14:00:00
4       testuser02    20            2020/09/26 14:00:00
5       testuser03    30            2020/09/26 14:00:00
6       testuser03    30            2020/08/27 14:00:00
7       testuser01    20            2020/08/27 14:00:00
8       testuser03    30            2019/12/01 14:00:00
9       testuser01    20            2019/12/01 14:00:00

testuser03       40              2020/09/27 14:00:00       1
testuser02       30              2020/09/27 14:00:00       2
testuser01       20              2020/09/27 14:00:00       3
testuser03       30              2020/09/26 14:00:00       1
testuser02       20              2020/09/26 14:00:00       2
testuser03       30              2020/08/27 14:00:00       1
testuser01       20              2020/08/27 14:00:00       2

testuser03       70              2020/09       1
testuser02       50              2020/09       2
testuser01       20              2020/09       3
testuser03       30              2020/08       1
testuser01       20              2020/08       2
testuser03       30              2019/12       1
testuser01       20              2019/12       2

Ks iT邦新手 4 級 ‧ 2020-09-27 13:56:22 檢舉

https://stackoverflow.com/questions/48760256/rank-by-date-and-qty-in-sql

### 2 個回答

4
rogeryao
iT邦高手 1 級 ‧ 2020-09-27 15:27:49

CREATE TABLE daily
(id nvarchar(30),
netprofit nvarchar(30),
date datetime
);

insert into daily
values
('1','testuser01','20','2020/09/27 14:00:00'),
('2','testuser02','30','2020/09/27 14:00:00'),
('3','testuser03','40','2020/09/27 14:00:00'),
('4','testuser02','20','2020/09/26 14:00:00'),
('5','testuser03','30','2020/09/26 14:00:00'),
('6','testuser03','30','2020/08/27 14:00:00'),
('7','testuser01','20','2020/08/27 14:00:00'),
('8','testuser03','30','2019/12/01 14:00:00'),
('9','testuser01','20','2019/12/01 14:00:00');

select *
from daily;
-- 每天 : row_number()
row_number()
over(partition by date order by date desc,netprofit desc,username) as rank
from daily
order by date desc,netprofit desc
-- 每月 : row_number()
row_number()
over(partition by date_format(date,'%Y-%m') order by date_format(date, '%Y-%m') desc,netprofit desc) as rank
from daily
order by date_format(date,'%Y-%m') desc,netprofit desc

Demo

Ks iT邦新手 4 級 ‧ 2020-09-27 20:19:06 檢舉

demo

rogeryao iT邦高手 1 級 ‧ 2020-09-27 21:14:52 檢舉

('4','testuser02','50','2020/09/26 14:00:00'),
('5','testuser03','60','2020/09/26 14:00:00'),

==> '50','60' 不見了

Ks iT邦新手 4 級 ‧ 2020-09-27 21:28:19 檢舉

demo

1
skyksl066
iT邦新手 5 級 ‧ 2020-09-27 14:03:21
SELECT
`netprofit`,
DATE_FORMAT(`date`,'%Y/%m'),
RANK() OVER(
PARTITION BY YEAR(`date`),
MONTH(`date`)
ORDER BY
`netprofit`
DESC
) rank
FROM
`rank` #日結完可以直接放在這邊轉月
Ks iT邦新手 4 級 ‧ 2020-09-27 20:21:03 檢舉