douqiao4450 2014-03-30 06:41
浏览 96
已采纳

优化MySQL查询以在单个查询中查找用于不同测试的用户的等级

I am using the query to find the rank of a user in a given test id, and this gives me rank for just one test at a time and I have to use foreach to get the test rank of all the test.

SELECT * , t.UserRank
FROM (

SELECT * , (
@rownum := @rownum +1
)UserRank
FROM User_Date_Table, (

SELECT @rownum :=0
)t
WHERE my_test_id  = '$test_id'
ORDER BY test_score DESC
)t
WHERE user_id = '$my_user_id' 

Can a query be generated which can give me user rank for all the test taken by user in single query as this will reduced the db hitting multiple times.

I have to find the all the ranks of user with Test_Type_Id = $my_test_type_id(say), joining a.id with b.my_test_id for a user with user_id = $my_user_id(say)

TABLE STRUCTURE

  My_Test_Table (a)

  id  |  name   |  Test_Type_Id 
  ----------------------------------------------
  1  |  name_1  |  1
  2  |  name_2  |  1    
  3  |  name_3  |  2    
  4  |  name_4  |  1    
  5  |  name_5  |  1
  6  |  name_6  |  2    
  7  |  name_7  |  1    
  8  |  name_8  |  2    
  9  |  name_9  |  1



 User_Date_Table (b)

id  | my_test_id | user_id  | test_score
---------------------------------------------------------
1  | 1  | 32    | 34
2  | 1  | 2     | 345
3  | 2  | 4     | 654
4  | 1  | 76    | 87
5  | 3  | 23    | 453
6  | 2  | 5     | 45
7  | 1  | 43    | 22
8  | 2  | 7     | 987
9  | 2  | 32    | 45
10  | 1     | 1     | 12
11  | 1     | 9 | 35
12  | 3     | 67    | 765
13  | 1     | 88    | 23
14  | 2     | 34    | 76
15  | 3     | 1     | 765
16  | 2     | 54    | 45
17  | 1     | 10    | 87
18  | 1     | 23    | 3
19  | 3     | 44    | 345
20 | 1  | 55    | 232
21  | 2     | 28    | 234
22  | 3     | 32    | 231
  • 写回答

2条回答 默认 最新

  • duancaoqin6683 2014-03-30 07:08
    关注

    Simplest is probably to just remove the user variable for ranking and just do it using a subquery;

    SELECT *, (SELECT COUNT(*)+1 
               FROM User_Date_Table b 
               WHERE a.my_test_id = b.my_test_id
                 AND a.test_score < b.test_score) userrank
    FROM User_Date_Table a
    WHERE user_id = '1';
    

    An SQLfiddle to test with.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题