插入一个新的排名列错误

Question

我们有一个表，TABLE_1：

DATA 
1 
2 
3 
4 
5 
NULL 

我们希望基于数据列

DATA,RANK 
1,1 
2,2 
3,3 
4,4 
5,5 
NULL,0 

下生成一个RANK列是我们的代码：

ALTER TABLE ` TABLE_1` 

ADD COLUMN `RANK` DOUBLE NOT NULL AFTER `DATA`; 

INSERT INTO TABLE_1 

SELECT B.DATA, 

CASE WHEN B.DATA IS NULL THEN 0 ELSE @number := @number + 1 

END RANK 

FROM TABLE_1 B, (SELECT @number := 0 AS BOO) C 

ORDER BY B.RANK; 

以上查询只会在RANK列中生成0

DATA,RANK 
1,0 
2,0 
3,0 
4,0 
5,0 
NULL,0 

但是，如果我们只是一起运行查询，而不ALTER TABLE和INSERT INTO像下面，HeidiSQL将返回我们正在寻找

SELECT B.DATA, 

CASE WHEN B.DATA IS NULL THEN 0 ELSE @number := @number + 1 

END RANK 

FROM TABLE_1 B, (SELECT @number := 0 AS BOO) C 

ORDER BY B.RANK; 

表以上脚本将生成一个表，我们正在寻找

DATA,RANK 
1,1 
2,2 
3,3 
4,4 
5,5 
NULL,0 

任何大师能够启发为什么ALTER TABLE/INSERT INTO不起作用？

的SQL小提琴，请访问：与内

http://sqlfiddle.com/#!9/d2837/2

Answer 1

使用更新查询加入

select * from bb; 
    +------+ 
    | data | 
    +------+ 
    | 1 | 
    | 2 | 
    | 3 | 
    | NULL | 
    +------+ 
    4 rows in set (0.00 sec) 
    mysql> ALTER TABLE `bb` ADD COLUMN `RANK` DOUBLE NOT NULL AFTER `DATA`; 
    Query OK, 0 rows affected (0.72 sec) 
    Records: 0 Duplicates: 0 Warnings: 0 


    mysql> UPDATE bb INNER JOIN (SELECT B.DATA as data ,CASE WHEN B.DATA IS NULL THEN 0 ELSE @number := @number + 1 END RANK FROM bb B, (SELECT @number := 0 AS BOO) C ORDER BY B.RANK) TT ON TT.DATA=bb.data set bb.rank=TT.rank; 
    Query OK, 3 rows affected (0.04 sec) 
    Rows matched: 3 Changed: 3 Warnings: 0 

    mysql> select * from bb; 
    +------+------+ 
    | data | RANK | 
    +------+------+ 
    | 1 | 1 | 
    | 2 | 2 | 
    | 3 | 3 | 
    | NULL | 0 | 
    +------+------+ 
    4 rows in set (0.00 sec)