问题比较简单,但是不发帖问而去搜的话又找不到适合我的情况.
一个属性property_ID列,不允许重复,而且是我手动的赋值的,我现在使用的逻辑是SELECT MAX(property_ID) FROM tab.然后把返回值+1再插入到新的数据上.
问题是,这一列是可以被修改的.也就是说原本所有物品的property_ID列都是递增的,如
property_ID
0001
0002
0003
0004
0005
但可能被修改过后就会导致数字的不连续.如
property_ID
0001
0003
0098
0005
但由于每插入一个新行我都是在最大的基础上加1,那些被删除的数字就永远得不到利用.
现在我想修改我的SELECT语句,有两个思路,一个是每次返回的都是最小的未被使用的值,如针对上例,返回的就应该是0002.另一个思路是每次都返回一个随机的新值(不一定要递增,但必须要符合数字规则,如在上例中就必须是四位的数字),这样的好处是我都不用再进行业务处理,直接把返回值插入新行就可以了.
两种方法我都不会实现,求帮助!
------解决方案--------------------
- SQL code
方法一:创建存储过程实现--如何用"最小缺失数"实现确实日期的自动补全-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE,备注 VARCHAR(100))GOINSERT TBLSELECT '2012-03-02','B' UNION ALLSELECT '2012-03-05','C' UNION ALLSELECT '2012-03-06','D' UNION ALLSELECT '2012-03-07','E' UNION ALLSELECT '2012-03-09','F' UNION ALLSELECT '2012-03-11','G' UNION ALLSELECT '2012-03-12','H' UNION ALLSELECT '2012-03-13','I' UNION ALLSELECT '2012-03-15','J' UNION ALLSELECT '2012-03-19','K' UNION ALLSELECT '2012-03-20','L'GOIF OBJECT_ID('P_SP')IS NOT NULLDROP PROC P_SPGOCREATE PROC P_SP @ENDTIME DATEASDECLARE @SQL VARCHAR(100)SET @SQL='SELECT * FROM TBL ORDER BY 日期'DECLARE @MINMISS DATESET @MINMISS=(SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),'2012-03-01') AS MISSINGFROM TBL AWHERE NOT EXISTS(SELECT * FROM TBL BWHERE B.日期=DATEADD(DD,1,A.日期))AND EXISTS (SELECT 1 FROM TBL WHERE 日期='2012-03-01'))PRINT @MINMISSWHILE @MINMISS<[email protected]BEGININSERT TBL(日期) VALUES(@MINMISS)SELECT @MINMISS=(SELECT DATEADD(DD,1,MIN(A.日期))FROM TBL AWHERE NOT EXISTS(SELECT * FROM TBL BWHERE B.日期=DATEADD(DD,1,A.日期)))ENDEXEC(@SQL)EXEC P_SP '2012-03-20'/*日期 备注2012-03-01 NULL2012-03-02 B2012-03-03 NULL2012-03-04 NULL2012-03-05 C2012-03-06 D2012-03-07 E2012-03-08 NULL2012-03-09 F2012-03-10 NULL2012-03-11 G2012-03-12 H2012-03-13 I2012-03-14 NULL2012-03-15 J2012-03-16 NULL2012-03-17 NULL2012-03-18 NULL2012-03-19 K2012-03-20 L*/------------------------------------------------------------------------------------------------------方法二,利用递归实现:-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE)GOINSERT TBLSELECT '2012-03-01' UNION ALLSELECT '2012-03-31'--利用递归实现输出三月份的所有日期:godeclare @date dateselect @date=MAX(日期) from tbl;with tas(select * from tblunion allselect dateadd(dd,1,a.日期) from t awhere not exists(select * from tbl bwhere b.日期=DATEADD(DD,1,a.日期))and a.日期<@date)select *from t order by 日期/*日期2012-03-012012-03-022012-03-032012-03-042012-03-052012-03-062012-03-072012-03-082012-03-092012-03-102012-03-112012-03-122012-03-132012-03-142012-03-152012-03-162012-03-172012-03-182012-03-192012-03-202012-03-212012-03-222012-03-232012-03-242012-03-252012-03-262012-03-272012-03-282012-03-292012-03-302012-03-31*/------------------------------------------------------------------------------------------------------方法三:利用系统表构造实现/* create table #tB( [A] int, [C2] varchar(10), [C3] datetime ) insert #tB select 1,'dfgsdfgsdf','2010-02-01' union all select 2,'dfgsdfgsdf','2010-02-02' union all select 3,'dfgsdfgsdf','2010-02-03' union all select 4,'dfgsdfgsdf','2010-02-04' union all select 4,'dfgsdfgsdf','2010-09-04' union all select 5,'dfgsdfgsdf','2010-09-08' union all select 5,'dfgsdfgsdf','2010-03-08' union all select 6,'dfgsdfgsdf','2010-03-11' union all select 4,'dfgsdfgsdf','2010-05-04' union all select 5,'dfgsdfgsdf','2010-02-08' union all select 6,'dfgsdfgsdf','2010-05-11' union all select 7,'dfgsdfgsdf','2010-05-14' union all select 8,'dfgsdfgsdf','2010-05-16' union all select 7,'dfgsdfgsdf','2010-03-14' union all select 8,'dfgsdfgsdf','2010-03-16' union all select 6,'dfgsdfgsdf','2010-09-11' union all select 7,'dfgsdfgsdf','2010-09-14' union all select 8,'dfgsdfgsdf','2010-09-16' union all select 9,'dfgsdfgsdf','2010-11-17'想得到如下结果SQL code month total percent 2010-01 0 .... 2010-02 14 .... 2010-03 26 .... 2010-04 0 .... 2010-05 25 .... 2010-06 0 .... 2010-07 0 .... 2010-08 0 .... 2010-09 25 .... 2010-10 0 .... 2010-11 9 .... 2010-12 0 ....*/goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl([A] int,[C2] varchar(10),[C3] datetime)insert tblselect 1,'dfgsdfgsdf','2010-02-01' union allselect 2,'dfgsdfgsdf','2010-02-02' union allselect 3,'dfgsdfgsdf','2010-02-03' union allselect 4,'dfgsdfgsdf','2010-02-04' union allselect 4,'dfgsdfgsdf','2010-09-04' union allselect 5,'dfgsdfgsdf','2010-09-08' union allselect 5,'dfgsdfgsdf','2010-03-08' union allselect 6,'dfgsdfgsdf','2010-03-11' union allselect 4,'dfgsdfgsdf','2010-05-04' union allselect 5,'dfgsdfgsdf','2010-02-08' union allselect 6,'dfgsdfgsdf','2010-05-11' union allselect 7,'dfgsdfgsdf','2010-05-14' union allselect 8,'dfgsdfgsdf','2010-05-16' union allselect 7,'dfgsdfgsdf','2010-03-14' union allselect 8,'dfgsdfgsdf','2010-03-16' union allselect 6,'dfgsdfgsdf','2010-09-11' union allselect 7,'dfgsdfgsdf','2010-09-14' union allselect 8,'dfgsdfgsdf','2010-09-16' union allselect 9,'dfgsdfgsdf','2010-11-17'selectisnull(c1,'2010-'+right('00'+ltrim(number),2)) as[month],--实现按月份递增isnull(c2,0) as total,ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) fromtbl) as decimal(18,2)))+'%' as [percent]--求百分比 from master..spt_values bleft join(select convert(varchar(7),C3,120) as c1,sum([A]) as c2from tblgroup by convert(varchar(7),C3,120)) c on b.number=month(c.c1+'-01') where b.type='p' andb.number between 1 and 12/*month total percent2010-01 0 0.00%2010-02 15 14.29%2010-03 26 24.76%2010-04 0 0.00%2010-05 25 23.81%2010-06 0 0.00%2010-07 0 0.00%2010-08 0 0.00%2010-09 30 28.57%2010-10 0 0.00%2010-11 9 8.57%2010-12 0 0.00%*/