现在想设计一个表,希望表里面根据user_group字段来分组,user_nums实现自增长。
这个是在数据库里面实现还在要在sql里面实现。表结构如下user_group user_nums
user1 1
user1 2
user3 2
user3 3
user1 3
user2 1
user2 2
user3 1
user3 4
这个是在数据库里面实现还在要在sql里面实现。表结构如下user_group user_nums
user1 1
user1 2
user3 2
user3 3
user1 3
user2 1
user2 2
user3 1
user3 4
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');SELECT * FROM animals ORDER BY grp,id;Which returns: +--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+