我有下表fields
:
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_name | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+
| 220481 | 9926 | NULL | 0 | NULL | NULL | 4 | 28 | Test | NULL | NULL |
| 281863 | 9926 | NULL | 0 | NULL | NULL | 10 | 29 | insert after yes no question | NULL | NULL |
| 220496 | 9926 | NULL | 0 | 11 | 1 | 5 | 30 | test | NULL | NULL |
| 249234 | 9926 | NULL | 0 | 12 | 1 | 5 | 32 | | NULL | NULL |
| 279877 | 9926 | NULL | 0 | NULL | NULL | 4 | 33 | Test Text Questions | NULL | NULL |
| 281860 | 9926 | NULL | 0 | NULL | NULL | 10 | 34 | Something | NULL | NULL |
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 35 | sssss | NULL | NULL |
| 281960 | 9926 | NULL | 0 | 38 | 1 | 5 | 36 | yuyuyu | NULL | NULL |
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 40 | ttttt | NULL | NULL |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+------------------------------+-------------+------------------+
如您所见,在这种情况下有两个field_seq
具有相同的值。36
假设我在之后插入一个新行,field_id=281960
而field_seq
for 这样的新行将作为36
.
我需要构建一个查询甚至一个存储过程,我可以在其中找出是否存在field_seq
等于或大于的行,36
如果是,则将 的值更新为field_seq
当前值 plus 1
。
前任:
INSERT INTO `fields` VALUES(9999, 9926, NULL, 0, 41, 1, 5, 36, 'lllll', NULL, NULL);
看到下面可能的情况(每个例子后面都有例子):
案例 1:表中已经存在 field_seq=36 的行
- 保持插入数据原样,它将成为当前的
field_seq=36
新行 field_seq=current+1
更新将变为的表行的值37
- 如果
37
已经有,则重复上一步,直到不再重复field_seq
前:
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_nanme | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 32 | sssss | NULL | NULL |
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 36 | ttttt | NULL | NULL |
| 281960 | 9926 | NULL | 0 | 38 | 1 | 5 | 37 | yuyuyu | NULL | NULL |
| 281978 | 9926 | NULL | 0 | 38 | 1 | 5 | 38 | vvvvv | NULL | NULL |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+-------------+-------------+------------------+
后:
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_nanme | field_class | field_class_data |
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 32 | sssss | NULL | NULL |
| 9999 | 9926 | NULL | 0 | 41 | 1 | 5 | 36 | lllll | NULL | NULL | => new row inserted here
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 37 | ttttt | NULL | NULL | => this was 36 now is updated to 37
| 281960 | 9926 | NULL | 0 | 38 | 1 | 5 | 38 | yuyuyu | NULL | NULL | => this was 37 now is updated to 38
| 281978 | 9926 | NULL | 0 | 38 | 1 | 5 | 39 | vvvvv | NULL | NULL | => this was 38 now is updated to 39
| 220524 | 9926 | NULL | 0 | NULL | NULL | 5 | 40 | Patient Information | NULL | NULL | => we don't care about this cause there is room for one more, if one insert makes the rows above become 40 then this needs to be updated to 41
+----------+---------+-----------------+-------------+---------+--------------+---------------+-----------+---------------------+-------------+------------------+
情况 2:表中已经存在 field_seq=36 的行,但 nextfield_seq
大于37
- 保持插入数据原样,它将成为当前的
field_seq=36
新行 field_seq=current+1
更新将变为的表行的值37
- 在这种情况下,我们不需要继续更新,因为有足够的空间在它们变得相同之前插入更多的行
field_seq
前:
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_name | field_class |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 32 | sssss | NULL |
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 36 | ttttt | NULL |
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 40 | ooooo | NULL |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
后:
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| field_id | form_id | form_section_id | is_required | grid_id | is_base_grid | field_type_id | field_seq | field_name | field_class |
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
| 281914 | 9926 | NULL | 0 | 23 | 1 | 5 | 32 | sssss | NULL |
| 281972 | 9926 | NULL | 0 | 41 | 1 | 5 | 36 | lllll | NULL | => new row inserted here
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 37 | ttttt | NULL | => previous row with field_seq=36 was updated to 37
| 281972 | 9926 | NULL | 0 | 40 | 1 | 5 | 40 | ooooo | NULL | => nothing happen to this one since there is room for more
+----------+---------+-----------------+-------------+---------+---------------+---------------+-----------+------------+-------------+
我正在使用 Microsoft SQL Server 2016 (SP1)。我怎样才能做到这一点?
你可以试试这个:
代码将检查是否存在冲突,
field_seq.
如果存在,它将扫描表以查找下一个间隙,更新field_seq
该范围内的所有值,并留下一个间隙以插入新记录。如果没有发现冲突,则跳过更新。不过,我不能对此做出任何性能保证。我敢肯定有更优化的方法来做到这一点。这是dbfiddle - 您可以看到更新发生之前和之后创建插入的间隙。
请记住,您应该添加一个错误处理程序并使用 TRANSACTION 来确保所有受影响的记录都得到更新。
之后插入一条新记录
field_id=29355
在末尾插入新记录。
dbfiddle在这里
您始终可以在插入中使用子查询,例如....
可能有用