我有两个表名表'hasil'和表'kat_soal'要加入,并在'hasil'表的每个'KatID'字段上给出排名。。
这是我的哈西尔桌:
HasilID KatID UserID JBenar JSalah Nilai
15 1 1000 2 1 66.66666666666666
16 3 1000 2 0 100
17 1 1001 1 2 33.33333333333333
18 3 1001 1 1 50
19 1 1002 3 0 90
20 3 1002 2 0 80
还有我的kat_soal桌
KatID Kategori Lama
1 IPA 30
2 IPS 30
3 Matematika 30
4 Bahasa Indonesia 20
5 Bahasa Inggris 20
此“我的查询”生成排名:
SELECT a.KatID,a.UserID,b.Kategori,c.Nama,a.JBenar,a.JSalah,ROUND(a.Nilai,2) as Nilai,
FIND_IN_SET( a.Nilai, l.list) AS rank
from hasil a
JOIN kat_soal b
ON a.KatID = b.KatID
JOIN datauser c
ON a.UserID=c.UserID
CROSS JOIN
(SELECT GROUP_CONCAT( a2.Nilai ORDER BY a2.Nilai DESC ) as list
FROM hasil a2) l
WHERE a.KatID='1'
ORDER BY a.Nilai DESC;
我的结果
//FOR KatID=1
KatID UserID Kategori Nama JBenar JSalah Nilai rank
1 1002 IPA ratam 3 0 90.00 2
1 1000 IPA Tarsan 2 1 66.67 4
1 1001 IPA wisnu 1 2 33.33 6
//FOR KatID=3
3 1000 Matematika Tarsan 2 0 100.00 1
3 1002 Matematika ratam 2 0 80.00 3
3 1001 Matematika wisnu 1 1 50.00 5
我的预期结果
//FOR KatID=1
KatID UserID Kategori Nama JBenar JSalah Nilai rank
1 1002 IPA ratam 3 0 90.00 1
1 1000 IPA Tarsan 2 1 66.67 2
1 1001 IPA wisnu 1 2 33.33 3
//FOR KatID=3
3 1000 Matematika Tarsan 2 0 100.00 1
3 1002 Matematika ratam 2 0 80.00 2
3 1001 Matematika wisnu 1 1 50.00 3
有人能帮我吗?
最佳答案
解决这个问题的好例子是查看:http://www.fromdual.com/ranking-mysql-results。
这有点复杂:首先获取值,使值成为字符串,然后“在字符串中查找位置”。
从这个例子来看,如果按(未经测试的)方式进行,应该是完全可以的:
SET @rank=0;
SELECT a.KatID,a.UserID,b.Kategori,c.Nama,a.JBenar,a.JSalah,ROUND(a.Nilai,2) as Nilai,
@rank:=@rank+1 AS rank
from hasil a
JOIN kat_soal b
ON a.KatID = b.KatID
JOIN datauser c
ON a.UserID=c.UserID
WHERE a.KatID='1'
ORDER BY rank;
编辑:更改了排序-您希望在决赛中按排名排序。
下面是不使用table
datauser
进行任何1测试的脚本:SET @rank=0;
SELECT a.KatID,a.UserID,b.Kategori,a.JBenar,a.JSalah,ROUND(a.Nilai,2) as Nilai,
@rank:=@rank+1 AS rank
from hasil a
JOIN kat_soal b
ON a.KatID = b.KatID
WHERE a.KatID='1'
ORDER BY rank;