現有數據表 record 表 成績表
record_id | integer | not null default nextval('record_id_seq'::text)
user_id | integer | not null
section_id | integer | not null
times | integer | not null
submit_datetime | timestamp without time zone | not null 現要根據相同的學生ID(user_id)sum成績scores的總分進行排名。排名可選按user_id從小大到,按sum(scores)最高分,或者最低分三種情況來排。 數據記錄: record_id:38
user_id:1387499
section_id:4
times:2
submit_datetime:2008-09-11 10:10:27
record_id:44
user_id:1387513
section_id:4
times:1
submit_datetime:2008-09-11 15:28:31
record_id:40
user_id:1387498
section_id:3
times:4
submit_datetime:2008-09-12 09:20:19
record_id:41
user_id:1387498
section_id:2
times:8
submit_datetime:2008-09-12 10:13:41
record_id:39
user_id:1387527
section_id:4
times:88
submit_datetime:2008-09-12 12:18:36
record_id:42
user_id:1387498
section_id:1
times:11
submit_datetime:2008-09-16 07:27:49
record_id:45
user_id:1387502
section_id:4
times:2
submit_datetime:2008-09-16 08:39:42
要求顯示結果,(注意:累積分數是按times的值*5得到的,而且當section_id為4時,times要除於2,當有余數就加1,例如如果section_id為4,它的times值為5,那麼它的學習次數應該是3次)
缺省按學生user_id從小大到排名,但要將排名也計算出來
用戶可在界面選擇按累積分數從大到小或者從小到大來排外
我用以下語句
select user_id,sum(case when section_id=4 then ceiling(trunc(times,1)/2) else times end) as studycount from record group by user_id order by user_id
得到每位用戶times學習多少的匯總user_id:1387498
studycount:139
user_id:1387499
studycount:1
user_id:1387502
studycount:1
user_id:1387503
studycount:1
user_id:1387513
studycount:1
user_id:1387527
studycount:44 現在我想用一條語句得到上面的結果外,還想知道每位用戶根據studycount進行成績排行,如果出現同分的情況,他們排行一樣。請問這語句應該怎樣定啊。困惑了我好幾天,明天就要交貨了。著急啊。分不夠,可以加,只要能幫解決就行了。先謝了!
record_id | integer | not null default nextval('record_id_seq'::text)
user_id | integer | not null
section_id | integer | not null
times | integer | not null
submit_datetime | timestamp without time zone | not null 現要根據相同的學生ID(user_id)sum成績scores的總分進行排名。排名可選按user_id從小大到,按sum(scores)最高分,或者最低分三種情況來排。 數據記錄: record_id:38
user_id:1387499
section_id:4
times:2
submit_datetime:2008-09-11 10:10:27
record_id:44
user_id:1387513
section_id:4
times:1
submit_datetime:2008-09-11 15:28:31
record_id:40
user_id:1387498
section_id:3
times:4
submit_datetime:2008-09-12 09:20:19
record_id:41
user_id:1387498
section_id:2
times:8
submit_datetime:2008-09-12 10:13:41
record_id:39
user_id:1387527
section_id:4
times:88
submit_datetime:2008-09-12 12:18:36
record_id:42
user_id:1387498
section_id:1
times:11
submit_datetime:2008-09-16 07:27:49
record_id:45
user_id:1387502
section_id:4
times:2
submit_datetime:2008-09-16 08:39:42
要求顯示結果,(注意:累積分數是按times的值*5得到的,而且當section_id為4時,times要除於2,當有余數就加1,例如如果section_id為4,它的times值為5,那麼它的學習次數應該是3次)
缺省按學生user_id從小大到排名,但要將排名也計算出來
用戶可在界面選擇按累積分數從大到小或者從小到大來排外
我用以下語句
select user_id,sum(case when section_id=4 then ceiling(trunc(times,1)/2) else times end) as studycount from record group by user_id order by user_id
得到每位用戶times學習多少的匯總user_id:1387498
studycount:139
user_id:1387499
studycount:1
user_id:1387502
studycount:1
user_id:1387503
studycount:1
user_id:1387513
studycount:1
user_id:1387527
studycount:44 現在我想用一條語句得到上面的結果外,還想知道每位用戶根據studycount進行成績排行,如果出現同分的情況,他們排行一樣。請問這語句應該怎樣定啊。困惑了我好幾天,明天就要交貨了。著急啊。分不夠,可以加,只要能幫解決就行了。先謝了!
http://blog.chinaunix.net/u/29134/showart_379356.html