当前位置: 动力学知识库 > 问答 > 编程问答 >

order - Can I replace in MySQL all values in the column by their positions in the ordered list of values

问题描述:

I have the following table:

| col1 | col2 | col3 |

| 1 | 1.1 | 9.3 |

| 2 | 7.9 | 1.3 |

| 3 | 3.7 | 7.3 |

| 4 | 9.0 | 5.7 |

I need to order the rows using the weighted sum of the col2 and col3. It can be easily done in the following way:

select * from mytable order by (0.8*col2 + 0.2*col3)

Now I want to do almost the same, the only difference is that I need to replace the actual values in the col2 and col3 by there position in the ordered list of all values in the column. For example, in the second column the following replacements should be done:

(1.1 -> 1, 3.7 -> 2, 7.9 -> 3, 9.0 -> 4)

Does anybody know if there is an easy way to construct a query that would do that. In other words, I would like to have something like that:

select * from mytable order by (0.8*modified(col2) + 0.2*modified(col3))

where the modified function replace the value by its position in the ordered list of all the values from the column.

网友答案:

You can try something like this:

SET @cnt := 0;
update mytable m ,
(
SELECT
    @cnt := @cnt + 1 as newid,
    col1
 from mytable order by (0.8*col2 + 0.2*col3)
) as mysorted
set a.col2 = mysorted.newid
where a.col1 = mysorted.col1
分享给朋友:
您可能感兴趣的文章:
随机阅读: