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

最新推荐文章于 2023-05-22 08:00:00 发布
最新推荐文章于 2023-05-22 08:00:00 发布
阅读量218 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： golang
原文链接：http://www.cnblogs.com/prettydone/p/6918153.html
本文介绍了一种检查Excel导入系统中用户自定义序号连续性和重复性的方法，包括将序号转换为小数进行处理，使用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)的意图就是取序号的整数部分，数据如下：

pidSeq
11
21.01
31.02
41.02
51.05
61.06
12
22.02
14
16.08
26.1

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

gidSeq
11
11.01
11.02
0.991.02
1.011.05
1.011.06
22
2.012.02
44
6.086.08
6.096.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：此文章是原创，转载需声明出处。

转载于:https://www.cnblogs.com/prettydone/p/6918153.html

sql----连续性问题终极万能模板（2021-07-08）
qq_42166308的博客
07-08 1045
CODE君这几天遇到个烦人的问题，连续性问题！！在这里看到别人的帖子备受触动，写一篇文章分享给大家，一定要收藏好，面试必备，个人能力的体现~~~~~~~~ 有一张“用户登陆记录表”，包含两个字段：用户id、日期。 【问题】查询2021年每个月，每个用户连续登陆的最多天数。 【解题步骤】 连续问题的万能模板 在《拼多多面试题：如何找出连续出现N次的内容？》里讲过遇到“连续问题”如何解决，并送出了一个万能模板，模板使用的是窗口函数解决连续问题。 1 select distinct 列1 2 from (
SQL-生成序号的四种方式1
08-08
SQL中的排名函数是处理和分析数据时非常重要的工具，尤其是在数据分页、统计分析和数据排序中。SQL Server 2005引入了四个排名函数，分别是`row_number`, `rank`, `dense_rank`, 和 `ntile`。这些函数帮助我们为查询...
sql序号连续
这里有道风景
04-08 1848
SELECT ISNULL(MIN(id),0)+1 AS id FROM incontact WHERE id NOT IN (SELECT id-1 FROM incontact) 可以保证序号连续！！！
巧用SQL实现连续编号
emailqjc的专栏
07-29 778
select id=a.id+b.id+c.id+d.idfrom(    select id=0 union all select 1    union all select id=2 union all select 3    union all select id=4 union all select 5    union all select id=6 union all select 7
SQL查询结果增加序列号
weixin_30539625的博客
05-04 397
SQL查询记录中增加序列号 根据学生成绩在查询结果中增加排名字段： 1.SELECT ROW_NUMBER() OVER (ORDER BY SCORE ASC) AS RANK,NAME,SCORE FROM GRADE ORDER BY SCORE; 此写法是直接在结果记录添加顺序排序序号 2.SELECT RANK() OVER (ORDER BY SCORE ASC) AS RAN...
SQL 生成连续的编号
jinahong101的博客
05-22 3607
给大家一分钟，请思考：在你熟悉的数据库里使用SQL快速生成001~999的编号有哪些方法
Sql语句实现连号查询
qq_59657197的博客
03-18 1029
其原理是生成一张虚拟表-t2，用原表的num 列和 t2 表的num 列进行对比“t1.num = t2.num+1 or t1.num = t2.num-1”，最后去重就可以了。前段时间碰到这样一个需求，需要从数据库中查询出连续的单号，断号则剔除。需要对‘num’字段进行连续性查询；
SQL为查询的结果加上序号(ROW_NUMBER) 合并多个查询结果
10-29
总结来说，`ROW_NUMBER()`函数是SQL中非常有用的一个工具，它可以帮助我们为查询结果中的每一行添加唯一的序号，进而实现更复杂的查询逻辑。结合`RANK()`和`DENSE_RANK()`函数的使用，可以满足更多样化的排序需求。...
SQL Server 不删除信息重新恢复自动编号列的序号的方法
09-14
在大多数情况下，保持标识列的连续性是最佳实践，除非有特殊需求。如果你只是想避免已删除的标识值被再次使用，通常不需要手动重置种子，因为SQL Server通常能够有效地管理标识值的分配。 此外，如果你在开发或测试...
sql语句序号连续排序
最新发布
02-25
### 实现行号连续排序 在 SQL 查询中，`ROW_NUMBER()` 是一种非常有效的开窗函数，能够为每一行分配唯一的顺序编号。此功能特别适用于需要按特定标准对数据集内的每条记录进行排名或编号的情况。 对于 `ROW_NUMBER...
如何用SQL 来核查序号是否连续
liu834189447的博客
11-13 2714
select id+1 from TABLE where id+1 not in(select id from TABLE) and id+1<>(select max(id)+1 from TABLE) 执行后的结果就是缺少的 编号
如何解决sql server 自动编号问题
qingyundys的专栏
08-09 2337
你创建一个表，把其中一个列（比如“ID”）的“标识列”属性设为“是”，然后设置它的种子（初始值）、递增量。比如你按默认设定种子为1、递增量为1，那么生成的ID序列就会是：1、2、3、4、…… 在记录不是特别多（>1000万条）时，这个值是唯一的，也就是不会出现两者相同的情况。 但这样会有一个问题，比如你顺序生成了10条记录： 1、2、3、4、5、6、7、8、9、10 然后你删除了3号记录，这样剩下
SQL生成序号的四种方式
热门推荐
发光的萤火虫
02-18 8万+
排名函数是SQL SERVER2005新增的函数。排名函数总共有四种，分别是：row_number、rank、 dense_rank 、ntile。 row_number：顺序生成序号。 rank：相同的序值序号相同，但序号会跳号。 dense_rank ：相同的序值序号相同，序号顺序递增。 ntile：装桶，把记录分成指的桶数，编序号。  下面分别介绍一下这
Microsoft SQL Server 如何生成连续的数字/字符/时间序列
weixin_44519342的博客
01-11 1078
Microsoft SQL Server 如何生成连续的数字/字符/时间序列
在别人的空间里看到的收藏在这里
cineqi9136的博客
11-27 258
我有一个表结构，fphm,kshm2014,000000012014,000000022014,000000032014,000000042014,000000052014,000000072014,000000082014,0...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值