添加一个字段并插入一个增量值 [英] Add a field and insert an increment value
问题描述
我有这张桌子:
id ref data
1 111 data1
2 111 data2
3 111 data3
4 111 data4
5 222 data1
6 222 data2
7 222 data3
8 333 data1
9 333 data2
,我想插入一个新字段,例如 order ,其中,对于每个引用,我将设置一个新月值.所以输出应该是:
and I'd like to insert a new field, called for example order, where for each ref, I'll set a crescent value. So the output should be :
id ref data order
1 111 data1 1
2 111 data2 2
3 111 data3 3
4 111 data4 4
5 222 data1 1
6 222 data2 2
7 222 data3 3
8 333 data1 1
9 333 data2 2
我可以通过一个简单的查询来做到这一点吗?
can I do this with a simple query?
编辑
上面的例子只是一个例子.这是我在数据库上的真实表:
The example above is just an example. This is my real table on the database :
CREATE TABLE `items` (
`id` int(11) unsigned NOT NULL auto_increment,
`trackid` varchar(255) NOT NULL,
`side` varchar(255) NOT NULL,
`side_pos` char(2) NOT NULL default '#',
`pos` int(11) NOT NULL,
`hh` char(2) NOT NULL,
`mm` char(2) NOT NULL,
`ss` char(2) NOT NULL,
`atl` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20229 DEFAULT CHARSET=utf8
推荐答案
MySQL不支持分析(IE:ROW_NUMBER),这是您要使用的输出结果.使用:
MySQL doesn't support analytics (IE: ROW_NUMBER), which are what you'd use for the output you're after. Use:
SELECT x.id, x.ref, x.data, x.rank AS `order`
FROM (SELECT t.id, t.ref, t.data
CASE
WHEN @ref = t.ref THEN @rownum := @rownum + 1
ELSE @rownum := 1
END AS rank,
@ref := t.ref
FROM YOUR_TABLE t
JOIN (SELECT @rownum := NULL, @ref := -1) r
ORDER BY t.id, t.ref, t.data) x
通常,这将需要单独的语句来声明@rownum和@ref变量,但是如果像在示例中所看到的那样定义,MySQL将使您摆脱它.
Usually, this would require separate statements to declare the @rownum and @ref variables but MySQL lets you get away with it if they are defined like you see in the example.
如果ref
值不匹配,则CASE语句会将@rownum值重置为1.否则,@rownum递增1. @rownum值与之关联的列由ORDER BY
子句确定.
The CASE statement resets the @rownum value to one if the ref
values don't match. Otherwise, @rownum increments by one. The column the incrementing @rownum value is associated with is determined by the ORDER BY
clause.
这篇关于添加一个字段并插入一个增量值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!