22,301
社区成员




USE TEST
GO
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1 (Rid [int] IDENTITY(1,1) NOT NULL,SRN INT,no1 int,no2 int,no3 int,no4 int,no5 int,notext varchar(20))
insert #tb1
select '258131','09','22','31','33','34','09 22 31 33 34' union all
select '258132','09','22','31','33','35','09 22 31 33 35' union all
select '258133','09','22','31','34','35','09 22 31 34 35' union all
select '258134','09','22','32','33','34','09 22 32 33 34' union all
select '258135','09','22','32','33','35','09 22 32 33 35' union all
select '258136','09','22','32','34','35','09 22 32 34 35' union all
select '258137','09','22','33','34','35','09 22 33 34 35' union all
select '258138','09','23','24','25','26','09 23 24 25 26' union all
select '258139','06','23','24','25','27','06 23 24 25 27' union all
select '258140','09','23','24','25','28','09 23 24 25 28'
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 --数据量超大就用了临时表#tb2,没有用cte建临时表.
go
select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn
into #tb2
from #tb1
unpivot(val for col in (no1,no2,no3,no4,no5)) p
if object_id('tempdb.dbo.#tb3') is not null drop table #tb3
go
select Rid,col,val%10 as val
,ROW_NUMBER() over(partition by rid order by val%10) as vid
into #tb3
from #tb2
select * from (
select Rid
from #tb3
group by Rid,val-vid having(COUNT(0) between 2 and 2 ) -- 后期需要所以不要用 having(COUNT(0)>n 的格式。
) a
left join #tb1 b on a.Rid=b.Rid
select * from (
SELECT X.Rid FROM (
select Rid,COUNT(0) counts
from #tb3
group by Rid,val-vid
--having(COUNT(0) =2 )
-- 后期需要所以不要用 having(COUNT(0)>n 的格式。
) X GROUP BY X.Rid HAVING MAX(X.counts)=2
) a
left join #tb1 b on a.Rid=b.Rid
select Rid,val%10 as val
,ROW_NUMBER() over(partition by rid order by val%10) as vid
into #tb3
from #tb2
group by Rid,val%10
-- 构建数据表
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1 (Rid [int] IDENTITY(1,1) NOT NULL,Notext varchar(20),SNO int)
insert #test1
SELECT '01 02 03 07 16 32','1' UNION ALL
SELECT '01 02 03 07 16 32','2' UNION ALL
SELECT '01 02 03 07 16 32','3' UNION ALL
SELECT '01 02 03 07 16 32','7' UNION ALL
SELECT '01 02 03 07 16 32','6' UNION ALL
SELECT '01 02 03 07 16 32','2' UNION ALL
SELECT '01 03 04 06 12 16','1' UNION ALL
SELECT '01 03 04 06 12 16','3' UNION ALL
SELECT '01 03 04 06 12 16','4' UNION ALL
SELECT '01 03 04 06 12 16','6' UNION ALL
SELECT '01 03 04 06 12 16','2' UNION ALL
SELECT '01 03 04 06 12 16','6'
-- 用SQL查询连续数字并且统计连续个数
SELECT b.Notext, MIN (b.SNO) Start_HM, MAX (b.SNO) End_HM, count(*) as ccount
FROM (
SELECT a.Notext,a.SNO, to_number (a.SNO - ROWNUM) cc --'to_number' 不是可以识别的 内置函数名称。
FROM (
SELECT *
FROM #test1
ORDER BY Notext, SNO
) a
) b
GROUP BY b.Notext, b.cc
having count(*) > 1
select notext from #tb1 ---简称 大集合A
except
select notext from #tb4 ---简称 大集合B
if object_id('tempdb.dbo.#tb3') is not null drop table #tb3
go
select Rid,min(col) col,val%10 as val
,ROW_NUMBER() over(partition by rid order by val%10) as vid
into #tb3
from #tb2 group by Rid,val%10