想要MY SQL中列组的行号吗? [英] Want Row Number on Group of column in MY SQL?
本文介绍了想要MY SQL中列组的行号吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在MYSQL中有一张表 它的数据如
I have one table in MYSQL It has data like
Id Name
1 test
1 test
1 test123
2 test222
3 test333
我想要
Id Name RowNum
1 test 1
1 test 2
1 test123 1
2 test222 1
3 test333 1
我要在ID和名称组中分配行号的方法吗?
Means i want assign row number on group of Id and Name ?
脚本应具有什么用途?
推荐答案
此表定义将实现您想要的.
This table definition will achieve what you want.
CREATE TABLE `test` (
`Id` int(10) unsigned NOT NULL,
`Name` varchar(45) NOT NULL,
`RowNum` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`Id`,`Name`,`RowNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
用数据填充表
INSERT INTO test VALUES
(1,"test",null),
(1,"test",null),
(1,"test123",null),
(2,"test222",null),
(3,"test333",null);
从表格中选择数据
SELECT * FROM test;
结果
1, 'test', 1
1, 'test', 2
1, 'test123', 1
2, 'test222', 1
3, 'test333', 1
在查询中执行此操作是一种很粗糙的方法.
For doing it in a query here is a rather crude way of doing it.
select g.id,g.name,g.rownum
from (
select t.id,t.name,
@running:=if(@previous=concat(t.id,t.name),@running,0) + 1 as rownum,
@previous:=concat(t.id,t.name)
from test t
order by concat(t.id,t.name)
) g;
这篇关于想要MY SQL中列组的行号吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文