Início / dba / Perguntas / 10251
Accepted
roxrook
Asked: 2012-01-06 21:57:21 +0800 CST

O que há de errado com esta consulta RANK de atualização?

  • 772

Ele foi atualizado apenas na primeira vez que o executei, depois disso users_tbl_rankcontinua o mesmo.

$sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC";

Na minha tabela, users_tbl_ranke users_tbl_pointssão dois números inteiros.

insira a descrição da imagem aqui

Alguém poderia me dizer o que estou fazendo de errado aqui? Desde já, obrigado.

mysql query

1 respostas

  1. Best Answer

    Criei uma amostra aleatória de 30 linhas com base na tabela acima da seguinte forma:

    DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;

    Aqui está essa saída

    mysql> DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;
Query OK, 1 row affected (0.04 sec)

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE sample
Database changed
mysql> CREATE TABLE users
    -> (id int not null auto_increment,
    -> users_tbl_points int not null,
    -> users_tbl_rank int not null default 0,
    -> primary key (id),key (users_tbl_points));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO users (users_tbl_points) VALUES
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
Query OK, 30 rows affected (0.08 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 30 rows affected (0.05 sec)
Rows matched: 30  Changed: 30  Warnings: 0

mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 27 |           974504 |              1 |
|  3 |           942712 |              2 |
| 26 |           914442 |              3 |
| 23 |           867887 |              4 |
| 13 |           828882 |              5 |
| 12 |           809094 |              6 |
| 29 |           722458 |              7 |
| 14 |           717128 |              8 |
|  4 |           702457 |              9 |
|  1 |           691932 |             10 |
|  5 |           684147 |             11 |
|  8 |           631802 |             12 |
|  9 |           615874 |             13 |
|  7 |           514378 |             14 |
| 17 |           420973 |             15 |
| 24 |           360579 |             16 |
| 16 |           343592 |             17 |
| 22 |           326285 |             18 |
| 20 |           315427 |             19 |
|  6 |           313363 |             20 |
| 21 |           254513 |             21 |
| 30 |           224710 |             22 |
| 25 |           199236 |             23 |
| 10 |           183963 |             24 |
| 28 |           129194 |             25 |
| 19 |           107540 |             26 |
| 15 |            98995 |             27 |
| 18 |            74092 |             28 |
| 11 |            72197 |             29 |
|  2 |             3702 |             30 |
+----+------------------+----------------+
30 rows in set (0.01 sec)

mysql>

    Deixe-me atualizar as pontuações

    mysql> UPDATE users SET users_tbl_points = FLOOR(rand()*1000000);
Query OK, 30 rows affected (0.08 sec)
Rows matched: 30  Changed: 30  Warnings: 0

    Deixe-me reclassificá-los

    mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 29 rows affected (0.06 sec)
Rows matched: 30  Changed: 29  Warnings: 0

    Deixe-me exibi-los:

    mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 28 |           994943 |              1 |
|  7 |           983596 |              2 |
|  4 |           964909 |              3 |
| 24 |           921117 |              4 |
| 14 |           885399 |              5 |
|  8 |           881325 |              6 |
|  3 |           858830 |              7 |
| 11 |           808514 |              8 |
|  1 |           785523 |              9 |
| 26 |           751842 |             10 |
| 22 |           749931 |             11 |
| 21 |           705158 |             12 |
| 25 |           703038 |             13 |
| 27 |           650093 |             14 |
| 15 |           649229 |             15 |
| 10 |           635204 |             16 |
| 23 |           634182 |             17 |
| 16 |           589948 |             18 |
|  9 |           455836 |             19 |
|  2 |           443080 |             20 |
|  6 |           345553 |             21 |
| 13 |           259255 |             22 |
| 20 |           258620 |             23 |
|  5 |           248056 |             24 |
| 18 |           240429 |             25 |
| 19 |           195981 |             26 |
| 30 |           137355 |             27 |
| 12 |           136960 |             28 |
| 29 |            24437 |             29 |
| 17 |             2055 |             30 |
+----+------------------+----------------+
30 rows in set (0.00 sec)

mysql>

    Parece que funciona comigo!!!

    EMBARGO

    Verifique se você tem um índice na users_tbl_pointscoluna

    • 3

relate perguntas