原始数据库

-------------------------------
id | codeId | sequence | name |
-------------------------------
1  | code 1 |      1     | moka |
2  | code 2 |      2     | moka |
3  | code 3 |      3     | moka |
4  | code 4 |      4     | moka |
5  | code 5 |      5     | moka |
6  | code 6 |      6     | moka |
7  | code 7 |      7     | moka |
8  | code 8 |      8     | moka |
9  | code 9 |      9     | moka |
10 | code 10|      10    | moka |

case-1####当selectedcodeid数组移到索引1（向上）。

targetSequence = 1;
selectedCodeId = ["code 1","code 4","code 7"]

I want Output like below (Database Compared with Original database)
sequence value should change in this order.
-------------------------------
id | codeId | sequence | name |
-------------------------------
1  | code 1 |      1     | moka |
2  | code 2 |      4     | moka |
3  | code 3 |      5     | moka |
4  | code 4 |      2     | moka |
5  | code 5 |      6     | moka |
6  | code 6 |      7     | moka |
7  | code 7 |      3     | moka |
8  | code 8 |      8     | moka |
9  | code 9 |      9     | moka |
10 | code 10|      10    | moka |

case-2####当selectedcodeid数组移到索引8（下面）。

targetSequence = 8;
selectedCodeId = ["code 1","code 4","code 7"]

I want Output like below (Database Compared with Original database)
sequence value should change in this order.
-------------------------------
id | codeId | sequence | name |
-------------------------------
1  | code 1 |      8     | moka |
2  | code 2 |      1     | moka |
3  | code 3 |      2     | moka |
4  | code 4 |      9     | moka |
5  | code 5 |      3     | moka |
6  | code 6 |      4     | moka |
7  | code 7 |      10    | moka |
8  | code 8 |      5     | moka |
9  | code 9 |      6     | moka |
10 | code 10|      7     | moka |

case-3#####当selectedcodeid数组移到索引2（介于两者之间）时。

targetSequence = 2;
selectedCodeId = ["code 1","code 4"]

I want Output like below (Database Compared with Original database)
sequence value should be like this.
-------------------------------
id | codeId | sequence | name |
-------------------------------
1  | code 1 |      2     | moka |
2  | code 2 |      1     | moka |
3  | code 3 |      4     | moka |
4  | code 4 |      3     | moka |
5  | code 5 |      5     | moka |
6  | code 6 |      6     | moka |
7  | code 7 |      7     | moka |
8  | code 8 |      8     | moka |
9  | code 9 |      9     | moka |
10 | code 10|      10    | moka |

我们尝试使用下面的函数来维护该类型序列。

const test = async (knexTemp, array, index, tableName) => {

    let sequenceArray = await knexTemp(tableName).select('id','rowId','sequence').whereIn('codeId',  ["code 1","code 4","code 7"]).orderBy('sequence', 'acs');;
    let type = 'increment';
    let firstSequence = sequenceArray.data[0].sequence;
    if (index > firstSequence) {
        type = 'decrement';
    }
    let sequenceCount = 1;
    let arrayLength = array.length;
    if (type == 'decrement') {
        for (let i in sequenceArray) {
            sequenceArray[i].firstValue = sequenceArray[i].sequence;
            sequenceArray[i].firstOperator = '>=';
            if (i < arrayLength - 1) {
                sequenceArray[i].secondValue = sequenceArray[parseInt(i) + 1].sequence;
                sequenceArray[i].secondOperator = '<';
            } else {
                sequenceArray[i].secondValue = index + arrayLength;
                sequenceArray[i].secondOperator = '<';
            }
            sequenceArray[i].value = sequenceCount;
            sequenceCount++;
        }
    } else {
        for (let i in sequenceArray) {
            if (i == 0) {
                sequenceArray[i].firstValue = index;
                sequenceArray[i].firstOperator = '>=';
            } else {
                sequenceArray[i].firstValue = sequenceArray[parseInt(i) - 1].sequence;
                sequenceArray[i].firstOperator = '>';
            }
            sequenceArray[i].secondValue = sequenceArray[i].sequence;
            sequenceArray[i].secondOperator = '<';
            sequenceArray[i].value = arrayLength;
            arrayLength--;
        }
    }
    for (let i in sequenceArray) {
        if (type == 'decrement') {
            const data = await knexTemp(tableName)
                .where('id', query.firstOperator, query.firstValue)
                .where('id', query.secondOperator, query.secondValue)
                .decrement('sequence', query.value);
            obj.data = data;
        } else {
            await knexTemp(tableName)
                .where('id', query.firstOperator, query.firstValue)
                .where('id', query.secondOperator, query.secondValue)
                .increment('sequence', query.value);
            obj.data = data;
        }
        await knexTemp(tableName).where({id : query.codeId}).update({sequence : index });
        index++;
    }
}