時(shí)間:2024-03-12 20:33作者:下載吧人氣:13
我就廢話(huà)不多說(shuō)了,大家還是直接看代碼吧~
select * from (select ROW_NUMBER () OVER (ORDER BY fat desc nulls last) AS xuhao,foodnum,foodname,fat from ek_food where isdel=0) food where foodnum = ‘Ss192008’
1.排序時(shí),字段值為null的會(huì)排在前面,導(dǎo)致數(shù)據(jù)不準(zhǔn)確,解決辦法 在order by后面增加 nulls last
2.給查詢(xún)的結(jié)果增加序號(hào) select ROW_NUMBER () OVER (ORDER BY fat desc nulls last) AS xuhao
補(bǔ)充:利用 PostgreSQL 實(shí)現(xiàn)對(duì)數(shù)據(jù)進(jìn)行排名
user_id | name | score |
1 | john | 1000 |
2 | mike | 1200 |
3 | jelly | 1300 |
4 | brook | 1500 |
5 | nanny | 1200 |
需要知道 user_id = k 的用戶(hù)對(duì)應(yīng)的積分排名
SELECT user_id, name, score, RANK() OVER (ORDER BY score DESC) FROM user;
user_id | name | score | rank |
4 | brook | 1500 | 1 |
3 | jelly | 1200 | 2 |
2 | mike | 1300 | 3 |
5 | nanny | 1500 | 3 |
1 | john | 1200 | 5 |
如要獲取排名 < 3 的用戶(hù):
SELECT user_id, name, score, user_rank FROM (SELECT user_id, name, score, RANK() OVER (ORDER BY score DESC) AS user_rank FROM user) AS T WHERE user_rank < 3;
— 注意子查詢(xún)?cè)趂rom中需要寫(xiě)別名
user_id | name | score | rank |
4 | brook | 1500 | 1 |
3 | jelly | 1200 | 2 |
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教。
網(wǎng)友評(píng)論