添加字段并插入增量值

Question

我有一张桌子：

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的新字段，其中对于每个ref，我将设置一个新月值。因此，产出应该是：

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

我可以用一个简单的查询来完成这个任务吗？

编辑

上面的例子只是一个例子。这是我在数据库上的真正的表格：

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

Answer 1

MySQL不支持分析(IE: ROW_NUMBER)，这是您所追求的输出所需要的。使用：

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变量，但是，如果像在示例中所看到的那样定义了@rownum和@ref变量，那么MySQL允许您使用它们。

如果ref值不匹配，CASE语句将@rownum值重置为一个。否则，@rownum将增加一个。与递增@rownum值相关联的列由ORDER BY子句确定。

Answer 2

我相信，如果您创建一个主键(ref，order)，其中order是一个auto_increment，那么您可以让数据库自动为您创建这个键。

http://mysqldump.azundris.com/archives/5-AUTO_INCREMENT-Trivia.html