[求SQL语句]搜索顺序数字列中缺少的数字解决方案

[求SQL语句]搜索顺序数字列中缺少的数字
问题比较简单,但是不发帖问而去搜的话又找不到适合我的情况.
一个属性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


方法一：创建存储过程实现


--如何用"最小缺失数"实现确实日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'


GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP @ENDTIME DATE
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 日期'
DECLARE @MINMISS DATE
SET @MINMISS=(
SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),
'2012-03-01') AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B
WHERE B.日期=DATEADD(DD,1,A.日期))
AND EXISTS (
SELECT 1 FROM TBL WHERE 日期='2012-03-01'))
PRINT @MINMISS
WHILE @MINMISS<=@ENDTIME
BEGIN
INSERT TBL(日期) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT DATEADD(DD,1,MIN(A.日期))
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B
WHERE B.日期=DATEADD(DD,1,A.日期))
)
END
EXEC(@SQL)

EXEC P_SP '2012-03-20'

/*
日期    备注
2012-03-01    NULL
2012-03-02    B
2012-03-03    NULL
2012-03-04    NULL
2012-03-05    C
2012-03-06    D
2012-03-07    E
2012-03-08    NULL
2012-03-09    F
2012-03-10    NULL
2012-03-11    G
2012-03-12    H
2012-03-13    I

2012-03-14    NULL
2012-03-15    J

2012-03-16    NULL

2012-03-17    NULL

2012-03-18    NULL

2012-03-19    K

2012-03-20    L


*/



--------------------------------------------------

--------------------------------------------------

--方法二，利用递归实现：

-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2012-03-01' UNION ALL
SELECT '2012-03-31'


--利用递归实现输出三月份的所有日期：
go
declare @date date
select @date=MAX(日期) from tbl
;with t
as(
select * from tbl
union all
select dateadd(dd,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(DD,1,a.日期)
)
and a.日期<@date
)
select *from t order by 日期

/*
日期
2012-03-01
2012-03-02
2012-03-03
2012-03-04
2012-03-05
2012-03-06
2012-03-07
2012-03-08
2012-03-09
2012-03-10
2012-03-11
2012-03-12
2012-03-13
2012-03-14
2012-03-15
2012-03-16
2012-03-17
2012-03-18
2012-03-19
2012-03-20
2012-03-21
2012-03-22
2012-03-23
2012-03-24
2012-03-25
2012-03-26
2012-03-27
2012-03-28
2012-03-29
2012-03-30
2012-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 ....


*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
[A] int,
[C2] varchar(10),
[C3] datetime
)
insert tbl
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'


select
isnull(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]) from

tbl) as decimal(18,2)))+'%' as [percent]
--求百分比
 from master..spt_values  b
left join
(select convert(varchar(7),C3,120) as c1,sum([A]) as c2

from tbl
group by convert(varchar(7),C3,120)
) c on b.number=month(c.c1+'-01') where b.type='p' and

b.number between 1 and 12

/*
month    total    percent
2010-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%

*/

[求SQL语句]搜索顺序数字列中缺少的数字解决方案

相关推荐