我有一个MySQL表,其中包含有关程序功能使用情况的统计信息.我从中检索到的基本上是这样的(这里总共前15位)：
SELECT function_id, data_timer, SUM( data_counter ) total
FROM data
GROUP BY function_id
ORDER BY total DESC
+-------------+------------+-------+
| function_id | data_timer | total |
+-------------+------------+-------+
| 56 | 567 | 4389 |
| 23 | 7880 | 1267 |
| 7 | 145 | 812 |
| ... | ... | ... |
+-------------+------------+-------+
由于这些结果在网站模块中使用,用户可以在其中选择要用于ORDER BY的列以及在ASC和DESC之间使用的列,因此我需要检索结果每一行的排名.
借助这个question,我能够为结果的每一行分配一个等级：
SET @rank = 0;
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data
WHERE client_id = 2
GROUP BY function_id
ORDER BY total DESC
+------+-------------+------------+-------+
| rank | function_id | data_timer | total |
+------+-------------+------------+-------+
| 1 | 56 | 567 | 4389 |
| 2 | 23 | 7880 | 1267 |
| 3 | 7 | 145 | 812 |
| ... | ... | ... | ... |
+------+-------------+------------+-------+
我现在在尝试反转此表时遇到了一些困难,这意味着我希望首先对使用最少的函数进行排序.大概是这样(假设有76个函数)：
+------+-------------+------------+-------+
| rank | function_id | data_timer | total |
+------+-------------+------------+-------+
| 76 | 44 | 346 | 1 |
| 75 | 2 | 3980 | 4 |
| 74 | 13 | 612 | 7 |
| ... | ... | ... | ... |
+------+-------------+------------+-------+
这是我的SQL查询尝试：
SELECT rank, function_id, data_timer, total
FROM
(
SET @rank = 0;
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data
WHERE client_id = 2
GROUP BY function_id
ORDER BY total DESC
)
ORDER BY rank DESC
它不断弹出我这个：
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SET @rank = 0' at line 4
由于我不太懂SQL,所以我想我缺少明显的东西.
任何帮助将不胜感激,谢谢！
解决方法:
您正在尝试在子查询中分配一个变量.这行不通.将分配移到子查询之外,它将运行.
SET @rank = 0;
SELECT rank, function_id, data_timer, total
FROM
(
SELECT @rank:=@rank+1 AS rank, function_id, data_timer, SUM( data_counter ) total
FROM data
WHERE client_id = 2
GROUP BY function_id
ORDER BY total DESC
)
ORDER BY rank DESC