如何根据一个MySQL查询中的另外两列为每行设置一个数字？

Let's say I have a table named members with this structure:

假设我有一个名为member的表,具有以下结构:

member_id, col1, col2, number

Now, if I run this query:

现在,如果我运行此查询:

SELECT @rownum:=@rownum+1 number, member_id 
FROM members, (SELECT @rownum:=0) r ORDER BY col1 DESC, col2 DESC

I get a number for each row. Is it possible to assign this number to column number for each row?

我得到每一行的数字。是否可以将此数字分配给每行的列号?

For example, if the table contained this data:

例如,如果表包含此数据:

1 1000 1.2 0
2 8700 1.1 0
3 1000 1.1 0

First row number column should be set to 2, second row to 1 and third row to 3.

第一行编号列应设置为2,第二行编号设置为1,第三行设置为3。

2 个解决方案

#1

You may use a temporary table to store the values and UPDATE from it with a join.

您可以使用临时表通过连接存储值和UPDATE。

/* Store query result in a temporary table */
CREATE TEMPORARY TABLE ranks AS
  SELECT @rownum:=@rownum+1 number, member_id FROM members, (SELECT @rownum:=0) r ORDER BY col1 DESC, col2 DESC;

/* And join against it to update your members.number column */
UPDATE 
  members
  JOIN ranks ON members.member_id = ranks.member_id
SET members.number = ranks.number;

DROP TABLE ranks;

Actually by my test just now I was able to do this without the temporary table. I didn't think MySQL would permit updating the same table from a subquery but apparently it is permitted with the correlated subquery.

实际上刚刚通过我的测试,我能够在没有临时表的情况下做到这一点。我不认为MySQL会允许从子查询更新同一个表,但显然它与相关子查询一起允许。

UPDATE 
  members 
  /* JOIN against a subquery which returns the rank values */
  JOIN ( 
   SELECT @rownum:=@rownum+1 number, member_id 
   FROM members, (SELECT @rownum:=0) r
   ORDER BY col1 DESC, col2 DESC
  ) ranks ON members.member_id = ranks.member_id 
/* And set the number column to the rank */
SET members.number = ranks.number;

SQLfiddle.com demo

#2

You can try this

你可以试试这个

update members A
INNER JOIN
(
    SELECT @rownum:=@rownum+1 number, member_id 
    FROM members, (SELECT @rownum:=0) r 
    ORDER BY col1 DESC, col2 DESC
) B ON A.member_id = B.member_id
SET A.number = B.number;

#1