首页 > 代码库 > 现实项目中用户随意添加序号，如何用SQL解决序号连续性问题

现实项目中用户随意添加序号，如何用SQL解决序号连续性问题

2024-09-27 17:49:39   210人阅读

  前段时间，一直忙于学习golang语言，没有时间整理项目中用到的方法，今天趁着有空写下笔记。

  项目中，遇到一个比较"刁钻"的需求：用户用Excel导入到系统里，每一行前面都有一个序号，序号分成两部分，如下所示：

 技术分享

　　左边部分是大序号，右边是小序号，类似于书籍目录那样，序号是由用户自己编写，而且用户可以随意在Excel序号插入任何新序号，用户不保证新增或者编辑的序号是否正确，我们要做的是检查这些序号。

以下是我的检查思路：

1.序号是否连续

　　我们要事先给用户做一个限制，在大序号后面添加小序号的最大容量是100(其他容量也可以，只不过业务上100够用了)，即2到2-99都可以，用户可以随意追加小序号，例如有序号2，2-1，2-2，3，用户可以在2-2后面插入2-3。

2.序号有无重复

　　这个比较简单，SQL里用group  by xx having count(xx) > 1就可以解决，这个放到最后去检查。

步骤：

0x00

　　导入数据

create table #SerialNum(
    Seq varchar(20)
)
insert into #SerialNum (Seq)
select ‘1‘
union all select ‘1-1‘
union all select ‘1-2‘
union all select ‘1-2‘
union all select ‘1-5‘
union all select ‘1-6‘
union all select ‘2‘
union all select ‘2-2‘
union all select ‘4‘
union all select ‘6-8‘
union all select ‘6-10‘

0x01

　　写一个函数，把序号转换为decimal(18,2)类型的小数，例如把2-1转换成2.01，因为小序号的容量是100，所以2-1=>2+1/100=2.01。

/*
    select dbo.StringToDecimal(‘2-1‘)    --输出2.01
    select dbo.StringToDecimal(‘32-68‘)    --输出32.68
*/
CREATE function [dbo].[StringToDecimal](
    @Str as varchar(20)
)
returns decimal(18,2)
as
begin
    declare @IntNum decimal(18,2)    --整数部分
    declare @DeNum decimal(18,2)    --小数点后面
    declare @CharIndex int

    set @CharIndex = charindex(‘-‘,@Str)
    IF @CharIndex = 0    --没有‘-‘直接转decimal
        return cast(@Str as decimal(18,2))

    set @IntNum = cast(left(@Str,@CharIndex - 1) as decimal(18,2))    --获取整数部分
    set @DeNum = cast(RIGHT(@Str,len(@Str) - @CharIndex) as decimal(18,2))/100    --获取小数部分

    return cast(@IntNum + @DeNum as decimal(18,2))
end

 

　　再把#SerialNum的数据转换一次，放入另一个临时表#temp里。

create table #temp(
    Seq decimal(18,2)
)
insert into #temp (Seq)
select dbo.StringToDecimal(Seq) from #SerialNum

0x02

　　首先找出小序号是否连续，比如1-1，1-2，1-5，1-6，2，这里就出现断号，我们要做的是把1-5找出来(但是2前面不算断号)，提示该序号前面有断号，先上代码。

select min(Seq) as Seq    --找出脱离连续序号的一组序号的最小值，就知道哪里断开了
    from
        (select [gid] = Seq - cast(cast(pid -1 as decimal(18,2))/100 as decimal(18,2))    --计算差值，如果序号连续，那么gid一定等于序号的整数部分
                ,Seq
                from 
                    (select row_number() over(partition by cast(Seq as int) order by Seq) as pid    --在每个大序号内的排序
                                                                                ,Seq from #temp) a ) b 
                                                                                    group by cast(Seq as int),gid 
                                                                                    having cast(Seq as int) <> gid    --找出gid不等于序号整数的分组

 

　　先分解嵌套的a表，SQL中cast(Seq as int)的意图就是取序号的整数部分，数据如下：

pid Seq
1 1
2 1.01
3 1.02
4 1.02
5 1.05
6 1.06
1 2
2 2.02
1 4
1 6.08
2 6.1

　　再看嵌套的b表，b表的gid就是Seq - (pid)/100，数据如下：

gid Seq
1 1
1 1.01
1 1.02
0.99 1.02
1.01 1.05
1.01 1.06
2 2
2.01 2.02
4 4
6.08 6.08
6.09 6.1

 

　　看出来了吗？你会发现当gid等于Seq整数部分的时候就是连续的，不等于就代表出现断号了，那么接下来就直接通过group by cast(Seq as int),gid having cast(Seq as int) <> gid把断号块找出来，拿出断号块的第一个序号，就能准确提醒用户这里出现断号。
0x03

　　接下来检查出整数序号连续性

select
    min(Seq) as Seq
    from
    (select
        row_number() over (order by _Seq) as pid,Seq
        from
            (select cast(Seq as int) as _Seq, min(Seq) as Seq from #temp group by cast(Seq as int)) a) b group by (cast(Seq as int) - pid) having (cast(Seq as int) - pid) <> 0    --序号减去行号不等于零就是不连续

 

0x04

　　找出重复的序号

select Seq from #temp group by Seq having count(Seq) > 1

0x05

　　最后把前三步筛选出来的数据用CTE公用表达式整合在一起，放入临时表#rst

;with t1 as (    --t1筛选出小数序号连续性
    select min(Seq) as Seq    --找出脱离连续序号的一组序号的最小值，就知道哪里断开了
        from
            (select [gid] = Seq - cast(cast(pid -1 as decimal(18,2))/100 as decimal(18,2))    --计算差值，如果序号连续，那么gid一定等于序号的整数部分
                    ,Seq
                    from 
                        (select row_number() over(partition by cast(Seq as int) order by Seq) as pid    --在每个大序号内的排序
                                                                                    ,Seq from #temp) a ) b 
                                                                                        group by cast(Seq as int),gid 
                                                                                        having cast(Seq as int) <> gid    --找出gid不等于序号整数的分组
),t2 as (        --t2筛选出整数序号连续性
    select
        min(Seq) as Seq
        from
        (select
            row_number() over (order by _Seq) as pid,Seq
            from
                (select cast(Seq as int) as _Seq, min(Seq) as Seq from #temp group by cast(Seq as int)) a) b group by (cast(Seq as int) - pid) having (cast(Seq as int) - pid) <> 0    --序号减去行号不等于零就是不连续
),t3 as (        --t3检查重复性
    select Seq from #temp group by Seq having count(Seq) > 1
    union all
    select Seq from t1
    union all
    select Seq from t2
)
select distinct Seq into #rst from t3 order by Seq

 

0x06

　　但是要把小数还原成X-XX的格式，所以要写一个转换函数。

/*
    select dbo.DecimalToString(2) --输出2
    select dbo.DecimalToString(2.02)  --输出2-2
    select dbo.DecimalToString(2.2)  --输出2-20
*/
CREATE function [dbo].[DecimalToString](
    @Num as decimal(18,2)
)
returns varchar(20)
as
begin
    declare @Index int
    declare @Str varchar(20)

    set @Str = cast(@Num as varchar(20))
    set @Index = charindex(‘.‘,@Str)

    if @Num%1 <> 0
    begin
        set @Str = left(@Str,@Index-1) + ‘-‘ + cast(cast((@Num - cast(@Num as int))*100 as int) as varchar(20))
    end
    else
    begin
        set @Str = cast(cast(@Num as int) as varchar(20))
    end

    return @Str
end

 

0x07

　　给用户提示

select N‘序号 ‘ + dbo.DecimalToString(Seq) + N‘ 重复或者前面有断号‘ as BreakNum from #rst
BreakNum
序号 1-2 重复或者前面有断号
序号 1-5 重复或者前面有断号
序号 2-2 重复或者前面有断号
序号 4 重复或者前面有断号
序号 6-8 重复或者前面有断号
序号 6-10 重复或者前面有断号

所有源代码(可以直接复制按F5运行)：

技术分享 
create table #SerialNum(
    Seq varchar(20)
)
insert into #SerialNum (Seq)
select ‘1‘
union all select ‘1-1‘
union all select ‘1-2‘
union all select ‘1-2‘
union all select ‘1-5‘
union all select ‘1-6‘
union all select ‘2‘
union all select ‘2-2‘
union all select ‘4‘
union all select ‘6-8‘
union all select ‘6-10‘

create table #temp(
    Seq decimal(18,2)
)
insert into #temp (Seq)
select dbo.StringToDecimal(Seq) from #SerialNum

;with t1 as (    --t1筛选出小数序号连续性
    select min(Seq) as Seq    --找出脱离连续序号的一组序号的最小值，就知道哪里断开了
        from
            (select [gid] = Seq - cast(cast(pid -1 as decimal(18,2))/100 as decimal(18,2))    --计算差值，如果序号连续，那么gid一定等于序号的整数部分
                    ,Seq
                    from 
                        (select row_number() over(partition by cast(Seq as int) order by Seq) as pid    --在每个大序号内的排序
                                                                                    ,Seq from #temp) a ) b 
                                                                                        group by cast(Seq as int),gid 
                                                                                        having cast(Seq as int) <> gid    --找出gid不等于序号整数的分组
),t2 as (        --t2筛选出整数序号连续性
    select
        min(Seq) as Seq
        from
        (select
            row_number() over (order by _Seq) as pid,Seq
            from
                (select cast(Seq as int) as _Seq, min(Seq) as Seq from #temp group by cast(Seq as int)) a) b group by (cast(Seq as int) - pid) having (cast(Seq as int) - pid) <> 0    --序号减去行号不等于零就是不连续
),t3 as (        --t3检查重复性
    select Seq from #temp group by Seq having count(Seq) > 1
    union all
    select Seq from t1
    union all
    select Seq from t2
)
select distinct Seq into #rst from t3 order by Seq


select N‘序号 ‘ + dbo.DecimalToString(Seq) + N‘ 重复或者前面有断号‘ as BreakNum from #rst

drop table #SerialNum
drop table #temp
drop table #rst
View Code

 

 

总结：这是本人第一次写博客笔记，语言组织上可能有一点缺陷，望前辈们见谅。

 

PS：此文章是原创，转载需声明出处。

现实项目中用户随意添加序号，如何用SQL解决序号连续性问题


联系
我们