mysql一列值排序后的序列号更新到另一列内

现在数据库有一表
id   X
1    20
2    10
3    30
新增加一列Y
id     X      Y
1      20
2      10   
3       30


然后每行Y值为X值在X列排序的结果
即结果为：
id     X       Y
1      20     2
2      10     1

3       30     3

方法：
UPDATE test a INNER JOIN (
SELECT a.x ,COUNT(*) AS pm FROM `test` a LEFT JOIN  `test` b ON a.`x`>=b.`x`
GROUP BY a.`x`) c
ON a.`x`=c.x

SET a.y=c.pm

如果是按降序排列则

SELECT a.x ,COUNT(*) AS pm FROM `test` a LEFT JOIN  `test` b ON a.`x`>=b.`x`  改为

SELECT a.x ,COUNT(*) AS pm FROM `test` a LEFT JOIN  `test` b ON a.`x`<=b.`x`

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UPDATE link a INNER JOIN (
SELECT a.link_viewCount ,COUNT(*) AS pm FROM `link` a LEFT JOIN  `link` b ON a.`link_viewCount`<=b.`link_viewCount` 
where a.link_viewCount>100
GROUP BY a.`link_id`) c
ON a.`link_viewCount`=c.link_viewCount
SET a.link_rank=c.pm

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

临时添加一列 用来表示另一列的排序值

select   (@i:=@i+1)   as   i,l.* 
from   link l,(select   @i:=0)   as   it 
order by l.link_count1+l.link_count2+l.link_count3+l.link_count4+l.link_count5+l.link_count6+l.link_count7 desc