如何更改select语句中的列值
I have a select in mysql table that results this:
我在mysql表中有一个select,结果如下:
topic user weight
"hello" 324 3
"hello" 21 1
"second thread" 34 1
"second thread" 21 1
"second thread" 32 1
"second thread" 3 3
"hello" 23 1
I would like to turn the first column "topic" into numbers, to simplify things. Is it possible to turn just one column into numbers like this:
我想把第一列“主题”变成数字,以简化事情。是否可以将一列转换为如下数字:
topic user weight
1 324 3
1 21 1
2 34 1
2 21 1
2 32 1
2 3 3
1 23 1
Many thanks for your help!
非常感谢您的帮助!
Best regards, Simone
最好的问候,西蒙娜
3 个解决方案
#1
1
Here's a method which gets a DISTINCT list of topics and assigns row numbers to join against. This makes it possible to do in one query, plus a variable:
这是一个获取DISTINCT主题列表并指定要加入的行号的方法。这使得可以在一个查询中加上一个变量:
Edited: Refactored to initialize the rownum variable in the FROM clause, which is a little tidier looking and won't produce n*2 values for the row numbers.
编辑:重构初始化FROM子句中的rownum变量,这看起来有点整洁,并且不会为行号生成n * 2值。
SELECT id, yourtable.*
FROM
yourtable
JOIN (
/* Suquery gets row rank per distinct topic */
SELECT topic, @rownum:=@rownum+1 AS id
FROM (
/* Variable initialized in FROM clause */
SELECT @rownum:=0
) sr JOIN (
SELECT DISTINCT topic FROM yourtable
) t
) topicids ON yourtable.topic = topicids.topic
http://sqlfiddle.com/#!2/58d53/14
ID TOPIC USER WEIGHT
1 "hello" 324 3
1 "hello" 21 1
2 "second" 34 1
2 "second" 21 1
2 "second" 32 1
2 "second" 3 3
1 "hello" 23 1