给查询增加一个序列

本文介绍了 SQL 中生成序号的多种方法,包括使用临时表和自连接等,并通过示例展示了如何根据不同条件生成序号,适用于各种复杂的业务场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

很多时候我们对查询的结果要增加一个序列,通常有用临时表和用自连接两种途径来实现。
 
用临时表:
select 序号= identity(int,1,1),* into #tmp from @t select * from #tmp ;
drop table #tmp
 
不用临时表:
select 序号=(select sum(1) from @t where and  id<=a.id),*    
from @t a order by  a.id 
 
稍稍复杂一点,假设表里面要根据不同的类型来加序列,可以这样:
select 序号=(select sum(1) from @t where type=a.type and  id<=a.id),*    
from @t a order by  a.id 
 
 
继续扩展一下。
select 序号=(select sum(1) from @t where type=a.type and id<=a.id),*
from @t a order by a.id
这个代码有一个情况:当最小id的两者id相同时(这里id假设不是主键,可以看作是成绩之类的),两个的序号都会变成2,或者说是并列第二。
我们通常喜欢将这种情况归为并列第一。这样我们可以将这个代码稍作修改。

注意把比较的<=改成<之后,两个同样最小的id因为没有比它再小的id了所以sum(1)会变成null,所以这里要加个小小的处理。
再对上面的情况加一点点扩展。
如果我的比较方法是先比较id,如果id相同再比较另外一个字段来排序,那应该怎么修改上面的代码呢?
答案:
select 序号=(select isnull(sum(1)+1,1) from @t where type=a.type and (id<a.id or field<a.field) ),*
from @t a order by a.id
<think>我们正在讨论Oracle数据库序列(Sequence)的操作。用户希望将序列的当前值设置到一个指定的数值。根据引用内容,我们可以通过以下步骤实现: 1. 首先,我们需要知道序列的当前值。可以通过查询序列的`nextval`来获取当前值(注意:查询`nextval`会推进序列)。但这里我们不需要推进,所以我们可以先查询`last_number`(来自`user_sequences`视图)来了解当前值,不过更直接的方法是采用引用中的方法。 2. 引用中提供的方法是:通过临时改变序列的`increment by`值,然后执行一次`nextval`,最后将`increment by`改回1。这样可以将序列的当前值调整到目标值。 具体步骤: - 假设当前序列的值为`current_val`,目标值为`target_val`。 - 计算需要增加的步长:`step = target_val - current_val - 1`(注意:因为执行`nextval`后,序列值会增加`increment by`,所以我们需要调整步长使得下一次`nextval`得到`target_val`)。 - 但是引用中的做法是:先设置`increment by`为`target_val - current_val`(注意:这里没有减1,因为执行一次`nextval`就会增加`increment by`,所以设置步长为`target_val - current_val`,然后执行一次`nextval`就会得到`target_val`)。然而,在引用[2]中,当前值是21,目标值是100,他们设置步长为79(100-21=79),然后执行`nextval`得到100(21+79=100)。然后设置步长为1,再执行`nextval`得到101。 但是,这里有一个细节:当我们执行`alter sequence ... increment by N`后,第一次执行`nextval`会在当前值的基础上增加N。因此,如果我们希望序列的下一个值是`target_val`,那么我们需要设置`increment by`为`(target_val - current_val)`。注意,这里`current_val`是序列的当前值(即最后一次使用的值,下一次使用序列时将会是`current_val + increment`)。所以,步骤为: 1. 确定序列的当前值(`current_val`)。我们可以通过查询`user_sequences`视图中的`last_number`来获取,但注意`last_number`是序列在内存中缓存的最后一个值(可能不是实际使用的最后一个值,因为可能有缓存)。更可靠的方法是先查询一次`nextval`,然后记下这个值,然后根据这个值来计算。但这样会消耗一个序列值。因此,我们采用以下步骤: a. 查询序列的当前值(通过`nextval`)并记录为`current_val`(注意:这会消耗一个序列值,所以如果序列已经被使用,我们需要谨慎,因为这样会跳过一些值)。或者,我们可以通过`user_sequences`视图来获取当前值(`last_number`),但要注意缓存的影响。 b. 或者,我们可以不查询当前值,而是直接计算需要跳过的步数:`increment = target_val - current_val - 1`(这种计算方式不对,因为执行一次`nextval`会增加`increment by`的值)。实际上,如果我们希望序列的下一个值(即下一次调用`nextval`)是`target_val`,那么我们需要设置`increment by`为`(target_val - current_val)`,然后执行一次`nextval`,这样就会得到`target_val`。但是,我们不知道`current_val`是多少,所以我们需要先查询一次`nextval`来得到当前值(但这样就会改变当前值)。因此,我们有两种方法: 方法一(推荐,但会消耗一个序列值): - 步骤1:`select your_sequence.nextval from dual;` 得到值`current_val`。 - 步骤2:计算需要设置的步长:`increment = target_val - current_val - 1`? 不对,因为执行`nextval`后,序列值变为`current_val + increment`(即`target_val`)。所以应该是`increment = target_val - current_val`。但是,当我们设置步长为`increment`后,执行`nextval`会得到`current_val + increment = target_val`。然后,我们需要将步长改回1(因为通常我们每次增加1)。但是,这里有一个问题:步骤1中我们已经执行了一次`nextval`,所以序列的当前值已经变成了`current_val+1`(如果原来的步长是1)?不对,实际上序列的当前值(即最后一次返回的值)是`current_val`,然后我们设置步长为`increment`,再执行`nextval`,序列值变为`current_val + increment`。所以,我们设置`increment = target_val - current_val`,然后执行`nextval`得到`target_val`。 方法二(不消耗序列值,但需要查询数据字典): - 查询`user_sequences`视图获取`last_number`,但要注意,`last_number`是序列在内存中缓存的最后一个值,可能不是实际使用的最后一个值(因为序列有缓存机制,可能会预分配一组值)。所以,这种方法不一定准确。 因此,引用中采用的方法一(通过执行`nextval`来获取当前值)是可靠的,尽管它会消耗一个序列值。 具体操作步骤(假设序列名为`SEQ_NAME`,目标值为`TARGET_VAL`): 1. 查询当前序列的下一个值(这会推进序列): ```sql SELECT SEQ_NAME.NEXTVAL FROM DUAL; ``` 假设返回值为`CURRENT_VAL`。 2. 计算需要设置的步长:`STEP = TARGET_VAL - CURRENT_VAL`。注意:这里`STEP`可以是负数(如果目标值小于当前值,但序列通常递增,所以这种情况较少)。 3. 修改序列的步长为`STEP`: ```sql ALTER SEQUENCE SEQ_NAME INCREMENT BY &STEP; ``` 4. 再次查询序列的下一个值(此时会得到`CURRENT_VAL + STEP = TARGET_VAL`): ```sql SELECT SEQ_NAME.NEXTVAL FROM DUAL; ``` 5. 将序列的步长改回原来的值(通常是1): ```sql ALTER SEQUENCE SEQ_NAME INCREMENT BY 1; ``` 注意:步骤3和步骤4之间如果有其他会话使用同一个序列,会导致问题。因此,建议在事务中执行或者确保没有其他会话在使用该序列。 另外,如果目标值小于当前值,那么我们需要将步长设置为负数(递减),但序列默认是递增的,允许设置为负数(即递减序列)。但通常我们不会将序列值调小,因为序列的主要目的是生成唯一的递增数字。 还有一种情况:如果序列的当前值已经大于目标值,那么我们需要将序列值调小。但Oracle序列不允许直接设置当前值,且不能回退(除非重建序列)。所以,如果目标值小于当前值,我们可能需要重建序列(使用`START WITH`)或者接受跳过一些值。 因此,上述方法适用于将序列的当前值调整到大于当前值的指定值。 根据引用[2]的例子,他们正是这样操作的。 所以,我们可以总结为: ```sql -- 步骤1:获取当前值(并推进序列SELECT SEQ_NAME.NEXTVAL FROM DUAL; -- 假设得到CURRENT_VAL -- 步骤2:修改步长(注意:这里STEP = TARGET_VAL - CURRENT_VAL) ALTER SEQUENCE SEQ_NAME INCREMENT BY &STEP; -- 替换为实际计算出的STEP -- 步骤3:获取下一个值(此时会得到TARGET_VAL) SELECT SEQ_NAME.NEXTVAL FROM DUAL; -- 步骤4:将步长改回1 ALTER SEQUENCE SEQ_NAME INCREMENT BY 1; ``` 注意:在步骤2和步骤4之间,序列的步长被临时修改,这可能会影响其他会话(如果并发使用该序列)。因此,建议在系统使用低峰期或者锁定序列(通过独占方式)来操作。 另外,为了避免在步骤1中消耗一个序列值,我们可以尝试使用`user_sequences`视图来获取当前值(即`last_number`),但是因为序列有缓存,所以`last_number`可能不是当前会话应该得到的下一个值(它可能是缓存的上限)。因此,如果序列使用了缓存,那么`last_number`是序列缓存的最后一个值,而当前会话可能还没有使用到缓存的值。所以,直接查询`last_number`并不可靠。 因此,我们采用上述方法(消耗一个序列值)是更可靠的做法。 如果不想消耗序列值,且序列没有缓存(`NOCACHE`),那么我们可以使用`user_sequences.last_number`作为当前值。但是,如果序列使用了缓存,那么我们需要先执行`ALTER SEQUENCE SEQ_NAME NOCACHE;`来禁用缓存(操作完成后再启用缓存)。但这样操作比较复杂,而且可能会影响性能。 所以,在大多数情况下,我们采用消耗一个序列值的方法。 总结:通过临时修改序列的步长,然后执行一次`nextval`,我们可以将序列的当前值设置到指定的目标值(大于当前值的情况下)。操作完成后,记得将步长修改回来。 示例:将序列`SEQ_ACT_DAY_SUM`的当前值调整到20000。 ```sql -- 1. 获取当前值 SELECT SEQ_ACT_DAY_SUM.NEXTVAL FROM DUAL; -- 假设返回10000 -- 2. 修改步长为10000(因为20000-10000=10000) ALTER SEQUENCE SEQ_ACT_DAY_SUM INCREMENT BY 10000; -- 3. 获取下一个值(应该得到20000) SELECT SEQ_ACT_DAY_SUM.NEXTVAL FROM DUAL; -- 4. 修改步长回1 ALTER SEQUENCE SEQ_ACT_DAY_SUM INCREMENT BY 1; ``` 这样,序列的下一个值将是20001(因为第3步执行后,当前值是20000,然后步长改回1,下一次获取就是20001)。 注意:第1步中,我们获取了10000,这个值被消耗了(即序列值10000被使用了)。如果我们不想使用这个值,可以在操作结束后通过其他方式补偿(比如将序列值再调回来,但这样更复杂)。所以,这种方法会跳过10000到19999之间的值,因此请确保这些值没有被使用且可以跳过。 因此,这种方法适用于需要将序列值调高到一个较大的值(比如在数据迁移中,需要将序列值设置为大于已有数据的最大值)的情况。 如果目标值小于当前值,我们可能需要重建序列: ```sql -- 删除序列(如果可删除) DROP SEQUENCE SEQ_NAME; -- 重新创建序列,设置起始值为目标值(注意:起始值就是第一个nextval返回的值) CREATE SEQUENCE SEQ_NAME START WITH TARGET_VAL; ``` 但是,删除序列会使得依赖该序列的对象(比如表的默认值)失效,所以需要谨慎。 因此,我们主要讨论将序列值调高的情况。 最后,根据用户的问题,我们给出以下回答:</think>### Oracle序列设置当前值到指定数值 在Oracle数据库中,序列(Sequence)的当前值无法直接修改,但可以通过临时调整`INCREMENT BY`属性间接实现。以下是具体操作步骤(假设目标值为`N`): #### 操作步骤 1. **查询当前序列值** 先获取序列当前值(执行会消耗一个序列值): ```sql SELECT your_sequence.NEXTVAL FROM dual; -- 假设返回值为CURRENT_VAL ``` 2. **计算步长差值** 目标步长 = `目标值N - CURRENT_VAL - 1` *(减1是因为下一步执行NEXTVAL会自增)* 3. **临时修改序列步长** ```sql ALTER SEQUENCE your_sequence INCREMENT BY &step_diff; ``` 4. **推进序列到目标值** ```sql SELECT your_sequence.NEXTVAL FROM dual; -- 此时返回值应为N-1 ``` 5. **恢复默认步长** ```sql ALTER SEQUENCE your_sequence INCREMENT BY 1; ``` #### 完整示例 将序列`my_seq`当前值设为100: ```sql -- 1. 查询当前值(假设返回20) SELECT my_seq.NEXTVAL FROM dual; -- 2. 计算步长:100-20-1=79 ALTER SEQUENCE my_seq INCREMENT BY 79; -- 3. 推进序列(返回99:20+79=99) SELECT my_seq.NEXTVAL FROM dual; -- 4. 恢复步长 ALTER SEQUENCE my_seq INCREMENT BY 1; -- 5. 验证(应返回100) SELECT my_seq.NEXTVAL FROM dual; ``` #### 注意事项 1. **值域限制** - 目标值必须 > 当前序列值 - 若需减小序列值,必须重建序列: ```sql DROP SEQUENCE my_seq; CREATE SEQUENCE my_seq START WITH 50; -- 从50开始 ``` 2. **并发风险** 操作期间序列可能被其他会话调用,建议在维护窗口执行[^1][^2]。 3. **缓存影响** 序列若设置缓存(如`CACHE 20`),需先执行`ALTER SEQUENCE my_seq NOCACHE`避免值跳跃。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值