首页 > 代码库 > 现实项目中用户随意添加序号，如何用SQL解决序号连续性问题
现实项目中用户随意添加序号，如何用SQL解决序号连续性问题
前段时间，一直忙于学习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
总结：这是本人第一次写博客笔记，语言组织上可能有一点缺陷，望前辈们见谅。
PS：此文章是原创，转载需声明出处。
现实项目中用户随意添加序号，如何用SQL解决序号连续性问题
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。