添加字段并插入增量值

发布于 2024-11-01 21:55:02 字数 1056 浏览 0 评论 0原文

我有这个表：

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，其中对于每个引用，我将设置一个新月值。所以输出应该是：

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

原文

I have this table :

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

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?

EDIT

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮光之海 2024-11-08 21:55:02

MySQL 不支持分析（即：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 变量，但如果它们的定义像示例中那样，那么 MySQL 可以让您摆脱它。

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

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

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.

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.

回复收藏 0 原文