我有两个表名表'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;

编辑:更改了排序-您希望在决赛中按排名排序。
下面是不使用tabledatauser进行任何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;

10-07 19:24
查看更多