我要求在表中包含数据,其中id(自动增量(应针对特定类型的数据进行划分。
类型,例如 - 类型 1、类型 2、类型 3 和 类型4
Type | Offset
Type1 | 1 -100
Type2 | 101-200
Type3 | 201-300
Type4 | 302-400
如果将数据插入到下面定义的表中,则应根据类型设置并自动递增 id。
例如
Capability_id | Capability_name | type
---------------------------------------------
1 | Type1_cap1 | Type1
2 | Type1_cap2 | Type1
101 | Type2_cap1 | Type2
3 | Type1_cap3 | Type1
有没有办法在mariadb/mysql中实现这一点?
如果type
已预定义为固定范围的 id,我认为您可以执行以下操作:
SELECT capability_id,CASE
WHEN capability_id BETWEEN 1 AND 100 THEN CONCAT("Type1_cap",capability_id)
WHEN capability_id BETWEEN 101 AND 200 THEN CONCAT("Type2_cap",capability_id-100)
WHEN capability_id BETWEEN 201 AND 300 THEN CONCAT("Type3_cap",capability_id-200)
WHEN capability_id BETWEEN 301 AND 400 THEN CONCAT("Type4_cap",capability_id-300)
END as `Capability_name`
FROM mytable;
在这里小提琴:https://www.db-fiddle.com/f/bG3mVwoNX66NBEgqLxX7Ya/0