อันนี้เป็นตัวอย่างที่ผมเขียน แต่มันไม่ได้ครับ ตอนที่มัน SUM มันจะ SUM ทั้งหมดเลย
Code
select distinct(tr.s_code),s_fname,s_lname,(select sum(point) as sum_point from time_result as trs where trs.s_code=tr.s_code order by trs.point DESC limit 4) as total from time_result as tr inner join student_fobissea as sf on (tr.s_code=sf.s_code)
where 1 and tr.s_year in ($s_year) and tr.s_gender='$sc_gender' order by total DESC
no s_code s_gender s_year s_house sp_id point
1 156 Male 5 HIS 4 13
2 46 Male 5 BISP 4 12
3 7 Male 5 BISJ 3 11
4 81 Male 5 BPS 4 10
5 116 Male 5 GIS 4 9
6 48 Male 5 BISP 3 7
7 79 Male 5 BPS 3 5
8 226 Male 5 SHB 4 4
9 151 Male 5 HIS 3 3
10 262 Male 5 TTS 4 2
11 117 Male 5 GIS 3 1
12 261 Male 5 TTS 3 0
13 187 Male 5 KLASS 4 0
14 224 Male 5 SHB 3 0
15 189 Male 5 KLASS 3 0
16 189 Male 5 KLASS 4 10
17 7 Male 5 BISJ 10 8
17 7 Male 5 BISJ 11 2
Date :
2012-06-07 08:02:35
By :
sober
No. 4
Guest
น่าจะประมาณนี้ครับ ลองไปแก้ไขดูครับ
select s_code,s_fname,s_lname,
((select point from time_result as tr where tr.s_code=trs.s_code order by point desc limit 0,1)
+(select point from time_result as tr where tr.s_code=trs.s_code order by point desc limit 1,1)
+(select point from time_result as tr where tr.s_code=trs.s_code order by point desc limit 2,1)
+(select point from time_result as tr where tr.s_code=trs.s_code order by point desc limit 3,1))as total
from student_fobissea as trs
where tr.s_year in ($s_year) and tr.s_gender='$sc_gender' order by total DESC
Load balance : Server 00
