当达到阈值时，如何重置表的标识列？

本文介绍了当达到阈值时，如何重置表的标识列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遗漏了很多我的SQL来巩固我的问题。我有一个临时表声明如下：

I'm leaving out much of my sql to consolidate my question. I have a temp table declared as follows:

if OBJECT_ID('Tempdb..#RankCalendar','U') is not null drop table #RankCalendar CREATE TABLE #RankCalendar (ID INT identity(0,1) ,CalendarDate DATE ) INSERT INTO #RankCalendar SELECT cal.CalendarDate FROM tblCalendar cal

这实质上是将一个日期值（01/01/2002）插入到一个起始ID为0的表中。但是，我需要的是每个月后重置等级。假设1月是我的第一个月，那么我就有： | ID | CalendarDate | | 0 | 01/01/2002 | | 1 | 01/02/2002 | | 2 | 01/03/2002 | 这对1月很好，但是当输入2月1日时，ID是32而不是0。 br /> 关于如何处理这个的任何想法或更好的解决方案？ 我尝试过： 我尝试过应用Dense_Rank和Lead功能无济于事。我也试过使用msdn上列出的sum（）方法。

This essentially inserts a date value (01/01/2002) into a table with a starting ID of 0. What I need however, is for the rank to reset after every month. So assuming January is my first month, then I'd have: | ID | CalendarDate | | 0 | 01/01/2002 | | 1 | 01/02/2002 | | 2 | 01/03/2002 | This is great for January but when February 1st is entered the ID is 32 rather than 0. Any ideas or better yet solutions on how to handle this? What I have tried: I've tried applying Dense_Rank and Lead functions to no avail. I've also tried to use the sum over() approach listed on msdn.

推荐答案

你可以通过执行这个SQL语句来重置标识列： You can "reset" the identity column by executing this SQL statement: DBCC CHECKIDENT ('tableNameGoesHere')

如果要将列重新设置为已知值，可以使用：

If you want to reseed the column to a known value, you can use:

DBCC CHECKIDENT ('tableNameGoesHere', RESEED, value)

插入表中的下一条记录将获得您设置的值+1。

The next record inserted into the table will get whatever the value is you set +1.

类似这样的工作对你来说？ Would something like this work for you? if OBJECT_ID('Tempdb..#RankCalendar','U') is not null drop table #RankCalendar; CREATE TABLE #RankCalendar ( ID INT NOT NULL, CalendarDate DATE NOT NULL ); INSERT INTO #RankCalendar ( ID, CalendarDate ) SELECT DENSE_RANK() OVER (PARTITION BY EOMONTH(cal.CalendarDate) ORDER BY cal.CalendarDate) - 1, cal.CalendarDate FROM tblCalendar cal ;