mssql sqlserver 使用sql脚本检查测验数据表中一列数据是还是不是一而再的主意分享

发布时间:2019-11-14  栏目:MySQL  评论:0 Comments

初藳地址:http://www.maomao365.com/?p=7335

转自:http://www.maomao365.com/?p=7205

 

摘要:

摘要:

下文分享两条sql求和本子,再次求和的方法共享

 

 /*
     例:
       下文已知两条sql求和脚本,现需对两张不同表的求和记录再次求和   
     */  
     ---对两条求和sql脚本求和的方法
      select sum(q) from 
      (
       select sum(qty)  as q from tableNameA where ...
       union all 
        select sum(qty)  as q from tableNameB where ...
       ) as table

     ---对多条求和sql脚本,再次求和的方法分享
     select sum(q) from 
      (
       select sum(qty)  as q from tableNameA where ...
       union all 
        select sum(qty)  as q from tableNameB where ...
         union all 
        select sum(qty)  as q from tableNameC where ...
        union all 
        select sum(qty)  as q from tableNameD where ...
       ...  
    ) as table

 
 数据表中,有一列是机动流水号,由于种种操作极其原因(恐怕插入战败),此列数据会变的不总是,下文将呈报使用sql脚本的措施获取数据表中的数额是还是不是一而再的艺术分享,如下所示:

 

 

   实验意况:sqlserver 二〇〇八 凯雷德2  


 

例:获取表test中keyId是或不是为总是的数额

 

达成思路:

 

 1.采用row_number 对表test重新生成流水号

 

 2.用到with as (cte)表明式将再度生成流水号的表归入一时表

 

 3.对一时表自个儿举办左连接(前风流浪漫行和后风流倜傥行互补),然后相比较行中两张表的keyId 是或不是离开1,

 

   要是离开1,则象征接二连三数据行,反之为非延续行数据

create table test 

(keyId int,info varchar(60))

go

insert into test(keyId,info)values

(1,'maomao365.com'),(2,N'连续数判断'),

(4,N'blog教程'),(5,'maomao'),

(8,'test'),(9,'info')

;

with  tmp as 

 (select  ROW_NUMBER() over (order by keyId asc ) as keyIdNew,

* from test  ) 

select a.keyIdNew,

a.keyId,a.info ,

 case  when ISNULL(a.keyId,0)-1 = b.keyId 

              or b.keyId is null  then '连续'

       else '不连续'

 end as '连续标志'

 from tmp a 

left join tmp b on a.keyIdNew = b.keyIdNew+1

;

go

truncate table test 

drop     table test 

 图片 1

 

留下评论

网站地图xml地图