只需要找出5个号码相同的记录。

RICHEER COCA 2017-09-05 10:58:44 
 --测试用

if object_id('tempdb.dbo.#tb1') is not null drop table #tb1 

 if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 

go

CREATE TABLE #tb1(



[NoText] [nvarchar](20) NULL)

 

insert #tb1

select '01 21 23 25 31 33' union all  ---第1行记录，6个号码01 21 23 25 31 33

select '02 21 23 25 31 33'    ---第2行记录，也是6个号码





CREATE TABLE #tb2(

[NoText] [nvarchar](20) NULL)

 

insert #tb2

select '01 03 21 23 25 31' union all

select '02 06 21 23 25 31' union all

select '03 08 21 23 25 31' union all

select '04 13 21 23 25 31' union all

select '05 15 21 23 25 31' union all

select '06 21 23 24 25 31' union all

select '07 21 23 25 29 31' union all

select '08 21 23 25 30 31' union all

select '09 21 23 25 31 32' union all

select '10 21 23 25 31 33' union all

select '01 03 21 23 25 33' union all

select '01 06 21 23 25 33' union all

select '01 08 21 23 25 33' union all

select '01 13 21 23 25 33' union all

select '01 15 21 23 25 33' union all

select '01 21 23 24 25 33' union all

select '02 21 23 25 29 33' union all

select '03 21 23 25 30 33' union all

select '04 21 23 25 31 33' union all

select '05 21 23 25 32 33' union all

select '01 03 23 25 31 33' union all

select '01 06 23 25 31 33' union all

select '01 08 23 25 31 33' union all

select '01 13 23 25 31 33' union all

select '01 15 23 25 31 33' union all

select '11 21 23 25 31 33' union all

select '01 23 24 25 31 33' union all

select '02 23 25 29 31 33' union all

select '03 23 25 30 31 33' union all

select '04 23 25 31 32 33' union all

select '01 03 21 25 31 33' union all

select '01 06 21 25 31 33' union all

select '01 08 21 25 31 33' union all

select '01 13 21 25 31 33' union all

select '01 15 21 25 31 33' union all

select '01 21 23 25 31 33' union all

select '02 21 24 25 31 33' union all

select '03 21 25 29 31 33' union all

select '04 21 25 30 31 33' union all

select '05 21 25 31 32 33' union all

select '01 03 21 23 31 33' union all

select '01 06 21 23 31 33' union all

select '01 08 21 23 31 33' union all

select '01 13 21 23 31 33' union all

select '01 15 21 23 31 33' union all

select '01 21 23 24 31 33' union all

select '20 21 23 25 31 33' union all

select '01 21 23 29 31 33' union all

select '02 21 23 30 31 33' union all

select '03 21 23 31 32 33' union all

select '04 21 23 25 31 33' union all

select '03 21 23 25 31 33' union all

select '06 21 23 25 31 33' union all

select '08 21 23 25 31 33' union all

select '13 21 23 25 31 33' union all

select '15 21 23 25 31 33' union all

select '21 23 24 25 31 33' union all

select '11 23 25 29 31 33' union all

select '12 23 25 30 31 33' union all

select '13 23 25 31 32 33'



---需要的结果   基础数据 #tb1 与 数据表#tb2 对比分析，找出5个号码 是相同的记录

SELECT NoText FROM  #tb2

where notext like '%01%'

and  notext like '%21%' 

and  notext like '%23%' 

and  notext like '%25%' 

and  notext like '%31%' 



union all

SELECT NoText FROM   tb17500cn 

where notext like '%01%'

and  notext like '%21%' 

and  notext like '%23%' 

and  notext like '%25%' 

and  notext like '%33%' 

union all

SELECT NoText FROM   tb17500cn 

where notext like '%01%'

and  notext like '%31%' 

and  notext like '%23%' 

and  notext like '%25%' 

and  notext like '%33%' 

union all

SELECT NoText FROM   tb17500cn 

where notext like '%01%'

and  notext like '%21%' 

and  notext like '%25%' 

and  notext like '%31%' 

and  notext like '%33%' 

union all

SELECT NoText FROM   tb17500cn 

where notext like '%01%'

and  notext like '%21%' 

and  notext like '%23%' 

and  notext like '%31%' 

and  notext like '%33%' 

union all

SELECT NoText FROM   tb17500cn 

where notext like '%31%'

and  notext like '%21%' 

and  notext like '%23%' 

and  notext like '%25%' 

and  notext like '%33%'



--问题：以上代码是 #tb1 第1行记录的6个号码01 21 23 25 31 33与 #tb2 对比分析，找出了5个号码 是相同的记录，

--但 #tb1 还有第2、3、4、5......N行记录，如何用代码一次性完成？
...全文
589 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
听雨停了 2017-09-06
  • 打赏
  • 举报
 回复
引用 13 楼 u011709039 的回复:
代码应该这样修改，原来的取字节数有误，所以只有15行结果。 

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  distinct NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
[quote=引用 12 楼 qq_37170555 的回复:] 或者你用游标1条1条的去把#tb1的数据和#tb2数据连一起。这样一次只出来120万数据，应该也不需要多久可以跑出来的
谢谢12 楼 qq_37170555 的回复，楼主对于游标的使用很是菜鸟，请多指导，谢谢啊。[/quote] 

DECLARE @notext VARCHAR(50)
DECLARE my_cur CURSOR FOR 
SELECT top 100 notext FROM #tb1
OPEN my_cur
FETCH NEXT FROM my_cur INTO @notext
IF NOT  OBJECT_ID('#tab3') IS NOT NULL
BEGIN 
	DROP TABLE #tab3
	CREATE TABLE #tab3
	(
		notext VARCHAR(20)
	)
END
WHILE @@FETCH_STATUS=0
BEGIN
	with cte1 as
	(select a.NoText,' '+a.NoText+' ' as NoText_2,' '+b.NoText+' ' as NoText_1 
	 from #tb2 a
	INNER JOIN (SELECT @notext AS notext) b on 1=1
	)
	INSERT INTO #tab3
	select distinct  NoText	
	from
	(select *,
			(case when charindex(substring(NoText_1,1,4),NoText_2)>0 then 1 else 0 end+
			 case when charindex(substring(NoText_1,4,4),NoText_2)>0 then 1 else 0 end+
			 case when charindex(substring(NoText_1,7,4),NoText_2)>0 then 1 else 0 end+
			 case when charindex(substring(NoText_1,10,4),NoText_2)>0 then 1 else 0 end+
			 case when charindex(substring(NoText_1,13,4),NoText_2)>0 then 1 else 0 end+
			 case when charindex(substring(NoText_1,16,4),NoText_2)>0 then 1 else 0 end) as match
	from cte1) as a
	where match>=5
	FETCH NEXT FROM my_cur INTO @notext	
END
CLOSE my_cur
DEALLOCATE my_cur
SELECT DISTINCT notext FROM #tab3
类似这样，上面写了top 100，你可以先跑100条看看，看是否跑的动，如果跑的动就把top 100去掉
繁花尽流年 2017-09-06
  • 打赏
  • 举报
 回复
tb2的数字组合LZ能做到重大到小或者从小到大按顺序排列的话，只需要整理tb1的排列种类就行了啊。 6个数字抽5个数字为有效排列的话1组也就出6组排列，1000条记录转换成6000条直接去like吧。
RICHEER COCA 2017-09-06
  • 打赏
  • 举报
 回复
代码应该这样修改，原来的取字节数有误，所以只有15行结果。 

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  distinct NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
引用 12 楼 qq_37170555 的回复:
或者你用游标1条1条的去把#tb1的数据和#tb2数据连一起。这样一次只出来120万数据，应该也不需要多久可以跑出来的
谢谢12 楼 qq_37170555 的回复，楼主对于游标的使用很是菜鸟，请多指导，谢谢啊。
听雨停了 2017-09-06
  • 打赏
  • 举报
 回复
引用 11 楼 u011709039 的回复:
[quote=引用 10 楼 RINK_1 的回复:] 

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
很感谢10 楼的大神 RINK_1，同时也感谢楼上3#、5#给予的无私帮助。 现在的问题是：10 楼 RINK_1的执行效率比较高，但tb1有2900条记录，tb2有120W条记录，执行了9个半小时，没有跑完，未有结果出现，请问有办法优化代码提高执行效率吗？[/quote] NoText -------------------- 01 03 23 25 31 33 01 06 23 25 31 33 01 08 23 25 31 33 01 13 23 25 31 33 01 15 23 25 31 33 01 21 23 25 31 33 03 21 23 25 31 33 04 21 23 25 31 33 06 21 23 25 31 33 08 21 23 25 31 33 10 21 23 25 31 33 11 21 23 25 31 33 13 21 23 25 31 33 15 21 23 25 31 33 20 21 23 25 31 33 (15 行受影响) 以上是我跑10#出来的结果，我看了下10#的代码，他的逻辑只能跑出#Tb2顺序和#tb1顺序一样的那样的结果，但是如果#tb2的顺序是乱的话，是跑不出来的，例如下面的 --01 21 23 24 25 33 --01 21 23 24 31 33 --01 21 23 25 31 33 --01 21 23 29 31 33 楼上的代码只能跑出来的结果是 --01 21 23 25 31 33 而其他的是出不来的，但是其他的也是符合条件的。 以下是我简单的修改了10#的代码得来的，不过你那么大的数据量估计一时半会也是很难跑出结果来的 

with cte1
as
(select a.NoText,' '+a.NoText+' ' as NoText_2,' '+b.NoText+' ' as NoText_1 from #tb2 a
join #tb1 b on 1=1)
select distinct  NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,4),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,4),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,4),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,4),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,4),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,4),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
你想想1200 inner join 120万的数据是多少啊，十几个亿啊。然后你下面在用这些字符串函数，再加上distinct 。跑九个半小时不出来也是正常的。我的建议你把#tb1的数据分开n多次和#tb2连一起，或者你用游标1条1条的去把#tb1的数据和#tb2数据连一起。这样一次只出来120万数据，应该也不需要多久可以跑出来的
RICHEER COCA 2017-09-06
  • 打赏
  • 举报
 回复
引用 10 楼 RINK_1 的回复:

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
很感谢10 楼的大神 RINK_1，同时也感谢楼上3#、5#给予的无私帮助。 现在的问题是：10 楼 RINK_1的执行效率比较高，但tb1有2900条记录，tb2有120W条记录，执行了9个半小时，没有跑完，未有结果出现，请问有办法优化代码提高执行效率吗？
听雨停了 2017-09-06
  • 打赏
  • 举报
 回复
引用 18 楼 u011709039 的回复:
可以的话就结贴了，大兄弟
RICHEER COCA 2017-09-06
  • 打赏
  • 举报
 回复
听雨停了 2017-09-06
  • 打赏
  • 举报
 回复
引用 16 楼 u011709039 的回复:
[quote=引用 15 楼 qq_37170555 的回复:] 类似这样，上面写了top 100，你可以先跑100条看看，看是否跑的动，如果跑的动就把top 100去掉
谢谢15 楼 qq_37170555 的回复，学习中，做了一个对比，继续请教： 15 楼 qq_37170555 的执行后 TOP1 耗时 13秒 结果 (163 行受影响) TOP10 耗时 123秒 结果 (1630 行受影响) TOP100 耗时 20‘’11秒 结果 (16161 行受影响) 

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  distinct NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
这是10 楼 RINK_1 的代码经过优化后 TOP1 耗时 10秒 结果 (163 行受影响) TOP10 耗时 98秒 结果 (1630 行受影响) TOP100 耗时 16‘’01秒 结果 (16300 行受影响) 问题：面对22亿行的庞大数据量，一定学习游标的代码，游标的优势在哪里？两者代码对比后，效率没有体现出来啊。 补充： 【楼主的环境】固盘，WIN7，Microsoft SQL Server 2014 Microsoft SQL Server Management Studio 12.0.2269.0 Microsoft Analysis Services 客户端工具 12.0.2000.8 Microsoft 数据访问组件 (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 8.0.7601.17514 Microsoft .NET Framework 4.0.30319.1026 操作系统 6.1.7601[/quote] 这里之所以使用游标，是因为你的#tb2的数据量太大了，用游标一行一行去读取#tb1的数据，然后再和#tb2连起来，这样的话就不会因为#tb1的数据太多，导致表相连的时候查询不来数据。一般情况下最好不要用游标，能不用的时候尽量不要用，因为在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量…… 对于SQL的优化我也还在学习中，你可以请教一下论然的大神，他们很厉害。我能帮你的就只是写个sql代码出来结果。对于大数据优化我还在摸索中，爱莫能助了兄弟
RICHEER COCA 2017-09-06
  • 打赏
  • 举报
 回复
引用 15 楼 qq_37170555 的回复:
类似这样，上面写了top 100，你可以先跑100条看看，看是否跑的动，如果跑的动就把top 100去掉
谢谢15 楼 qq_37170555 的回复，学习中，做了一个对比，继续请教： 15 楼 qq_37170555 的执行后 TOP1 耗时 13秒 结果 (163 行受影响) TOP10 耗时 123秒 结果 (1630 行受影响) TOP100 耗时 20‘’11秒 结果 (16161 行受影响) 

with cte1
as
(select  distinct  a.NoText,a.NoText as NoText_2,b.NoText  as NoText_1 from #tb2 a
join #tb1 b on 1=1)

selet  distinct NoText
from
(select *,
        (case when charindex(substring(NoText_1,1,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,4,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,7,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,10,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,13,2),NoText_2)>0 then 1 else 0 end+
         case when charindex(substring(NoText_1,16,2),NoText_2)>0 then 1 else 0 end) as match
from cte1) as a
where match>=5
这是10 楼 RINK_1 的代码经过优化后 TOP1 耗时 10秒 结果 (163 行受影响) TOP10 耗时 98秒 结果 (1630 行受影响) TOP100 耗时 16‘’01秒 结果 (16300 行受影响) 问题：面对22亿行的庞大数据量，一定学习游标的代码，游标的优势在哪里？两者代码对比后，效率没有体现出来啊。 补充： 【楼主的环境】固盘，WIN7，Microsoft SQL Server 2014 Microsoft SQL Server Management Studio 12.0.2269.0 Microsoft Analysis Services 客户端工具 12.0.2000.8 Microsoft 数据访问组件 (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 8.0.7601.17514 Microsoft .NET Framework 4.0.30319.1026 操作系统 6.1.7601
听雨停了 2017-09-05
  • 打赏
  • 举报
 回复 

btxt
--------------------
01 03 21 23 25 31
01 03 21 23 25 33
01 03 21 23 31 33
01 03 21 25 31 33
01 03 23 25 31 33
01 06 21 23 25 33
01 06 21 23 31 33
01 06 21 25 31 33
01 06 23 25 31 33
01 08 21 23 25 33
01 08 21 23 31 33
01 08 21 25 31 33
01 08 23 25 31 33
01 13 21 23 25 33
01 13 21 23 31 33
01 13 21 25 31 33
01 13 23 25 31 33
01 15 21 23 25 33
01 15 21 23 31 33
01 15 21 25 31 33
01 15 23 25 31 33
01 21 23 24 25 33
01 21 23 24 31 33
01 21 23 25 31 33
01 21 23 29 31 33
01 23 24 25 31 33
02 06 21 23 25 31
02 21 23 25 29 33
02 21 23 30 31 33
02 21 24 25 31 33
02 23 25 29 31 33
03 21 23 25 31 33
04 21 23 25 31 33
06 21 23 25 31 33
08 21 23 25 31 33
10 21 23 25 31 33
11 21 23 25 31 33
13 21 23 25 31 33
15 21 23 25 31 33
20 21 23 25 31 33
21 23 24 25 31 33

(41 行受影响)
--如果不要左右对照的话那么出来的结果就是这个了
听雨停了 2017-09-05
  • 打赏
  • 举报
 回复 

atxt                 btxt
-------------------- --------------------
01 21 23 25 31 33    01 03 21 23 25 31
01 21 23 25 31 33    01 03 21 23 25 33
01 21 23 25 31 33    01 03 21 23 31 33
01 21 23 25 31 33    01 03 21 25 31 33
01 21 23 25 31 33    01 03 23 25 31 33
01 21 23 25 31 33    01 06 21 23 25 33
01 21 23 25 31 33    01 06 21 23 31 33
01 21 23 25 31 33    01 06 21 25 31 33
01 21 23 25 31 33    01 06 23 25 31 33
01 21 23 25 31 33    01 08 21 23 25 33
01 21 23 25 31 33    01 08 21 23 31 33
01 21 23 25 31 33    01 08 21 25 31 33
01 21 23 25 31 33    01 08 23 25 31 33
01 21 23 25 31 33    01 13 21 23 25 33
01 21 23 25 31 33    01 13 21 23 31 33
01 21 23 25 31 33    01 13 21 25 31 33
01 21 23 25 31 33    01 13 23 25 31 33
01 21 23 25 31 33    01 15 21 23 25 33
01 21 23 25 31 33    01 15 21 23 31 33
01 21 23 25 31 33    01 15 21 25 31 33
01 21 23 25 31 33    01 15 23 25 31 33
01 21 23 25 31 33    01 21 23 24 25 33
01 21 23 25 31 33    01 21 23 24 31 33
01 21 23 25 31 33    01 21 23 29 31 33
01 21 23 25 31 33    01 23 24 25 31 33
01 21 23 25 31 33    03 21 23 25 31 33
01 21 23 25 31 33    04 21 23 25 31 33
01 21 23 25 31 33    06 21 23 25 31 33
01 21 23 25 31 33    08 21 23 25 31 33
01 21 23 25 31 33    10 21 23 25 31 33
01 21 23 25 31 33    11 21 23 25 31 33
01 21 23 25 31 33    13 21 23 25 31 33
01 21 23 25 31 33    15 21 23 25 31 33
01 21 23 25 31 33    20 21 23 25 31 33
01 21 23 25 31 33    21 23 24 25 31 33
02 21 23 25 31 33    01 21 23 25 31 33
02 21 23 25 31 33    02 06 21 23 25 31
02 21 23 25 31 33    02 21 23 25 29 33
02 21 23 25 31 33    02 21 23 30 31 33
02 21 23 25 31 33    02 21 24 25 31 33
02 21 23 25 31 33    02 23 25 29 31 33
02 21 23 25 31 33    03 21 23 25 31 33
02 21 23 25 31 33    04 21 23 25 31 33
02 21 23 25 31 33    06 21 23 25 31 33
02 21 23 25 31 33    08 21 23 25 31 33
02 21 23 25 31 33    10 21 23 25 31 33
02 21 23 25 31 33    11 21 23 25 31 33
02 21 23 25 31 33    13 21 23 25 31 33
02 21 23 25 31 33    15 21 23 25 31 33
02 21 23 25 31 33    20 21 23 25 31 33
02 21 23 25 31 33    21 23 24 25 31 33

(51 行受影响)
听雨停了 2017-09-05
  • 打赏
  • 举报
 回复 

 --测试用
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1 
 if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 
go
CREATE TABLE #tb1(
 
[NoText] [nvarchar](20) NULL)
  
insert #tb1
select '01 21 23 25 31 33' union all  ---第1行记录，6个号码01 21 23 25 31 33
select '02 21 23 25 31 33'    ---第2行记录，也是6个号码
 
 
CREATE TABLE #tb2(
[NoText] [nvarchar](20) NULL)
  
insert #tb2
select '01 03 21 23 25 31' union all
select '02 06 21 23 25 31' union all
select '03 08 21 23 25 31' union all
select '04 13 21 23 25 31' union all
select '05 15 21 23 25 31' union all
select '06 21 23 24 25 31' union all
select '07 21 23 25 29 31' union all
select '08 21 23 25 30 31' union all
select '09 21 23 25 31 32' union all
select '10 21 23 25 31 33' union all
select '01 03 21 23 25 33' union all
select '01 06 21 23 25 33' union all
select '01 08 21 23 25 33' union all
select '01 13 21 23 25 33' union all
select '01 15 21 23 25 33' union all
select '01 21 23 24 25 33' union all
select '02 21 23 25 29 33' union all
select '03 21 23 25 30 33' union all
select '04 21 23 25 31 33' union all
select '05 21 23 25 32 33' union all
select '01 03 23 25 31 33' union all
select '01 06 23 25 31 33' union all
select '01 08 23 25 31 33' union all
select '01 13 23 25 31 33' union all
select '01 15 23 25 31 33' union all
select '11 21 23 25 31 33' union all
select '01 23 24 25 31 33' union all
select '02 23 25 29 31 33' union all
select '03 23 25 30 31 33' union all
select '04 23 25 31 32 33' union all
select '01 03 21 25 31 33' union all
select '01 06 21 25 31 33' union all
select '01 08 21 25 31 33' union all
select '01 13 21 25 31 33' union all
select '01 15 21 25 31 33' union all
select '01 21 23 25 31 33' union all
select '02 21 24 25 31 33' union all
select '03 21 25 29 31 33' union all
select '04 21 25 30 31 33' union all
select '05 21 25 31 32 33' union all
select '01 03 21 23 31 33' union all
select '01 06 21 23 31 33' union all
select '01 08 21 23 31 33' union all
select '01 13 21 23 31 33' union all
select '01 15 21 23 31 33' union all
select '01 21 23 24 31 33' union all
select '20 21 23 25 31 33' union all
select '01 21 23 29 31 33' union all
select '02 21 23 30 31 33' union all
select '03 21 23 31 32 33' union all
select '04 21 23 25 31 33' union all
select '03 21 23 25 31 33' union all
select '06 21 23 25 31 33' union all
select '08 21 23 25 31 33' union all
select '13 21 23 25 31 33' union all
select '15 21 23 25 31 33' union all
select '21 23 24 25 31 33' union all
select '11 23 25 29 31 33' union all
select '12 23 25 30 31 33' union all
select '13 23 25 31 32 33'
--测试数据结束

WITH cte AS (
SELECT REPLACE([NOTEXT],' ','') AS [notext] , row_number() OVER ( ORDER BY [NOTEXT] ) as num FROM #tb1
)
,cte3 AS (
SELECT substring(notext,(number*2)+1,2) AS Txtnum,num
FROM MASTER..spt_values,cte
WHERE number<6 AND TYPE='p'
)
, cte2 AS (
SELECT REPLACE([NOTEXT],' ','') AS [notext] , row_number() OVER ( ORDER BY [NOTEXT] ) as num FROM #tb2
)
,cte4 AS (
SELECT substring(notext,(number*2)+1,2) AS Txtnum,num
FROM MASTER..spt_values,cte2
WHERE number<6 AND TYPE='p'
)
,cte5 AS (
SELECT a.txtnum AS atxtnum,a.num AS anum,b.txtnum AS btxtnum,b.num AS bnum,COUNT(b.num) OVER(PARTITION BY b.num,a.num) AS cnt FROM cte3 a
inner JOIN cte4 b ON a.txtnum=b.txtnum --AND b.num>=a.num
)
--但愿你可以理解以上的逻辑，不理解也没事，反正这样能出来，而且绝对没问题
SELECT distinct b.[notext] AS atxt, c.[NOTEXT] AS btxt
FROM cte5 a
left OUTER JOIN cte b ON a.anum=b.num
left OUTER JOIN cte2 c ON a.bnum=c.num
where a.cnt=5
ORDER BY b.[notext]
原本出来数据应该是53条的，但是由于你的#tb2有一条数据是重复的，所以出来的数据为51条
二月十六 2017-09-05
  • 打赏
  • 举报
 回复
需要去重的话把select 后面加一个distinct
二月十六 2017-09-05
  • 打赏
  • 举报
 回复
数据太多，没细看结果（可以减少到3个数字试试），试试这样：
CREATE FUNCTION dbo.f_splitstr(@SourceSql   NVARCHAR(MAX),@StrSeprate   VARCHAR(100))   

  RETURNS   @temp   TABLE(F1   VARCHAR(100))   

  AS     

  BEGIN   

  DECLARE   @ch   AS   VARCHAR(100)   

  SET   @SourceSql=@SourceSql+@StrSeprate     

  WHILE(@SourceSql<>'')   

                  BEGIN   

                  SET   @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)   

  INSERT   @temp   VALUES(@ch)   

  SET   @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')   

                  END   

  RETURN   

  END

GO



 --测试用

if object_id('tempdb.dbo.#tb1') is not null drop table #tb1 

 if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 

go

CREATE TABLE #tb1(

 

[NoText] [nvarchar](20) NULL)

  

insert #tb1

select '01 21 23 25 31 33' union all  ---第1行记录，6个号码01 21 23 25 31 33

select '02 21 23 25 31 33'    ---第2行记录，也是6个号码

 

 

CREATE TABLE #tb2(

[NoText] [nvarchar](20) NULL)

  

insert #tb2

select '01 03 21 23 25 31' union all

select '02 06 21 23 25 31' union all

select '03 08 21 23 25 31' union all

select '04 13 21 23 25 31' union all

select '05 15 21 23 25 31' union all

select '06 21 23 24 25 31' union all

select '07 21 23 25 29 31' union all

select '08 21 23 25 30 31' union all

select '09 21 23 25 31 32' union all

select '10 21 23 25 31 33' union all

select '01 03 21 23 25 33' union all

select '01 06 21 23 25 33' union all

select '01 08 21 23 25 33' union all

select '01 13 21 23 25 33' union all

select '01 15 21 23 25 33' union all

select '01 21 23 24 25 33' union all

select '02 21 23 25 29 33' union all

select '03 21 23 25 30 33' union all

select '04 21 23 25 31 33' union all

select '05 21 23 25 32 33' union all

select '01 03 23 25 31 33' union all

select '01 06 23 25 31 33' union all

select '01 08 23 25 31 33' union all

select '01 13 23 25 31 33' union all

select '01 15 23 25 31 33' union all

select '11 21 23 25 31 33' union all

select '01 23 24 25 31 33' union all

select '02 23 25 29 31 33' union all

select '03 23 25 30 31 33' union all

select '04 23 25 31 32 33' union all

select '01 03 21 25 31 33' union all

select '01 06 21 25 31 33' union all

select '01 08 21 25 31 33' union all

select '01 13 21 25 31 33' union all

select '01 15 21 25 31 33' union all

select '01 21 23 25 31 33' union all

select '02 21 24 25 31 33' union all

select '03 21 25 29 31 33' union all

select '04 21 25 30 31 33' union all

select '05 21 25 31 32 33' union all

select '01 03 21 23 31 33' union all

select '01 06 21 23 31 33' union all

select '01 08 21 23 31 33' union all

select '01 13 21 23 31 33' union all

select '01 15 21 23 31 33' union all

select '01 21 23 24 31 33' union all

select '20 21 23 25 31 33' union all

select '01 21 23 29 31 33' union all

select '02 21 23 30 31 33' union all

select '03 21 23 31 32 33' union all

select '04 21 23 25 31 33' union all

select '03 21 23 25 31 33' union all

select '06 21 23 25 31 33' union all

select '08 21 23 25 31 33' union all

select '13 21 23 25 31 33' union all

select '15 21 23 25 31 33' union all

select '21 23 24 25 31 33' union all

select '11 23 25 29 31 33' union all

select '12 23 25 30 31 33' union all

select '13 23 25 31 32 33'

 

---需要的结果   基础数据 #tb1 与 数据表#tb2 对比分析，找出5个号码 是相同的记录

SELECT  #tb2.*

FROM    #tb1 ,

        #tb2

WHERE   ( SELECT    COUNT(1)

          FROM      dbo.f_splitstr(#tb1.NoText, ' ') AS t1

                    JOIN dbo.f_splitstr(#tb2.NoText, ' ') AS t2 ON t1.F1 = t2.f1

        ) > 4
RICHEER COCA 2017-09-05
  • 打赏
  • 举报
 回复
删除1#重复的记录，如果用代码一次性完成，应该得到这样的结果 
notext
01 03 21 23 25 31
01 03 21 23 25 33
01 03 21 23 31 33
01 03 21 25 31 33
01 03 23 25 31 33
01 06 21 23 25 33
01 06 21 23 31 33
01 06 21 25 31 33
01 06 23 25 31 33
01 08 21 23 25 33
01 08 21 23 31 33
01 08 21 25 31 33
01 08 23 25 31 33
01 13 21 23 25 33
01 13 21 23 31 33
01 13 21 25 31 33
01 13 23 25 31 33
01 15 21 23 25 33
01 15 21 23 31 33
01 15 21 25 31 33
01 15 23 25 31 33
01 21 23 24 25 33
01 21 23 24 31 33
01 21 23 25 31 33
01 21 23 29 31 33
01 23 24 25 31 33
02 06 21 23 25 31
02 21 23 25 29 33
02 21 23 30 31 33
02 21 24 25 31 33
02 23 25 29 31 33
03 21 23 25 31 33
04 21 23 25 31 33
06 21 23 25 31 33
08 21 23 25 31 33
10 21 23 25 31 33
11 21 23 25 31 33
13 21 23 25 31 33
15 21 23 25 31 33
20 21 23 25 31 33
21 23 24 25 31 33
RICHEER COCA 2017-09-05
  • 打赏
  • 举报
 回复
tb1里2行记录 与tb2 对比后 有5个号码相同的结果是 
NoText
01 03 21 23 25 31
01 21 23 25 31 33
01 03 21 23 25 33
01 06 21 23 25 33
01 08 21 23 25 33
01 13 21 23 25 33
01 15 21 23 25 33
01 21 23 24 25 33
01 21 23 25 31 33
01 03 23 25 31 33
01 06 23 25 31 33
01 08 23 25 31 33
01 13 23 25 31 33
01 15 23 25 31 33
01 23 24 25 31 33
01 21 23 25 31 33
01 03 21 25 31 33
01 06 21 25 31 33
01 08 21 25 31 33
01 13 21 25 31 33
01 15 21 25 31 33
01 21 23 25 31 33
01 21 23 25 31 33
01 03 21 23 31 33
01 06 21 23 31 33
01 08 21 23 31 33
01 13 21 23 31 33
01 15 21 23 31 33
01 21 23 24 31 33
01 21 23 29 31 33
10 21 23 25 31 33
04 21 23 25 31 33
11 21 23 25 31 33
01 21 23 25 31 33
20 21 23 25 31 33
04 21 23 25 31 33
03 21 23 25 31 33
06 21 23 25 31 33
08 21 23 25 31 33
13 21 23 25 31 33
15 21 23 25 31 33
21 23 24 25 31 33
02 06 21 23 25 31
02 21 23 25 29 33
02 23 25 29 31 33
02 21 24 25 31 33
02 21 23 30 31 33
10 21 23 25 31 33
04 21 23 25 31 33
11 21 23 25 31 33
01 21 23 25 31 33
20 21 23 25 31 33
04 21 23 25 31 33
03 21 23 25 31 33
06 21 23 25 31 33
08 21 23 25 31 33
13 21 23 25 31 33
15 21 23 25 31 33
21 23 24 25 31 33
RINK_1 2017-09-05
  • 打赏
  • 举报
 回复
with cte1 as (select a.NoText,' '+a.NoText+' ' as NoText_2,' '+b.NoText+' ' as NoText_1 from #tb2 a join #tb1 b on 1=1) select distinct NoText from (select *, (case when charindex(substring(NoText_1,1,4),NoText_2)>0 then 1 else 0 end+ case when charindex(substring(NoText_1,4,7),NoText_2)>0 then 1 else 0 end+ case when charindex(substring(NoText_1,7,10),NoText_2)>0 then 1 else 0 end+ case when charindex(substring(NoText_1,10,13),NoText_2)>0 then 1 else 0 end+ case when charindex(substring(NoText_1,13,16),NoText_2)>0 then 1 else 0 end+ case when charindex(substring(NoText_1,16,19),NoText_2)>0 then 1 else 0 end) as match from cte1) as a where match>=5
听雨停了 2017-09-05
  • 打赏
  • 举报
 回复
引用 8 楼 u011709039 的回复:
[quote=引用 6 楼 qq_37170555 的回复:] 

atxt                 btxt
-------------------- --------------------
01 21 23 25 31 33    01 03 21 23 25 31
。。。

(51 行受影响)
得不到这样的数据格式，因为使用了
SELECT REPLACE([NOTEXT],' ','') AS [notext]
[/quote] 

;
WITH cte AS (
SELECT notext,REPLACE([NOTEXT],' ','') AS [anotext] , row_number() OVER ( ORDER BY [NOTEXT] ) as num FROM #tb1
)
,cte3 AS (
SELECT substring(anotext,(number*2)+1,2) AS Txtnum,num
FROM MASTER..spt_values,cte
WHERE number<6 AND TYPE='p'
)
, cte2 AS (
SELECT notext,REPLACE([NOTEXT],' ','') AS [bnotext] , row_number() OVER ( ORDER BY [NOTEXT] ) as num FROM #tb2
)
,cte4 AS (
SELECT substring(bnotext,(number*2)+1,2) AS Txtnum,num
FROM MASTER..spt_values,cte2
WHERE number<6 AND TYPE='p'
)
,cte5 AS (
SELECT a.txtnum AS atxtnum,a.num AS anum,b.txtnum AS btxtnum,b.num AS bnum,COUNT(b.num) OVER(PARTITION BY b.num,a.num) AS cnt FROM cte3 a
inner JOIN cte4 b ON a.txtnum=b.txtnum --AND b.num>=a.num
)
--SELECT * FROM cte5 where cnt=5
--ORDER BY anum, bnum

--SELECT * FROM cte

SELECT distinct b.[NOTEXT] AS atxt, c.[NOTEXT] AS btxt
FROM cte5 a
left OUTER JOIN cte b ON a.anum=b.num
left OUTER JOIN cte2 c ON a.bnum=c.num
where a.cnt=5
ORDER BY b.[NOTEXT], c.[NOTEXT]
-------------------结果集----------------------------
atxt                 btxt
-------------------- --------------------
01 21 23 25 31 33    01 03 21 23 25 31
01 21 23 25 31 33    01 03 21 23 25 33
01 21 23 25 31 33    01 03 21 23 31 33
01 21 23 25 31 33    01 03 21 25 31 33
01 21 23 25 31 33    01 03 23 25 31 33
01 21 23 25 31 33    01 06 21 23 25 33
01 21 23 25 31 33    01 06 21 23 31 33
01 21 23 25 31 33    01 06 21 25 31 33
01 21 23 25 31 33    01 06 23 25 31 33
01 21 23 25 31 33    01 08 21 23 25 33
01 21 23 25 31 33    01 08 21 23 31 33
01 21 23 25 31 33    01 08 21 25 31 33
01 21 23 25 31 33    01 08 23 25 31 33
01 21 23 25 31 33    01 13 21 23 25 33
01 21 23 25 31 33    01 13 21 23 31 33
01 21 23 25 31 33    01 13 21 25 31 33
01 21 23 25 31 33    01 13 23 25 31 33
01 21 23 25 31 33    01 15 21 23 25 33
01 21 23 25 31 33    01 15 21 23 31 33
01 21 23 25 31 33    01 15 21 25 31 33
01 21 23 25 31 33    01 15 23 25 31 33
01 21 23 25 31 33    01 21 23 24 25 33
01 21 23 25 31 33    01 21 23 24 31 33
01 21 23 25 31 33    01 21 23 29 31 33
01 21 23 25 31 33    01 23 24 25 31 33
01 21 23 25 31 33    03 21 23 25 31 33
01 21 23 25 31 33    04 21 23 25 31 33
01 21 23 25 31 33    06 21 23 25 31 33
01 21 23 25 31 33    08 21 23 25 31 33
01 21 23 25 31 33    10 21 23 25 31 33
01 21 23 25 31 33    11 21 23 25 31 33
01 21 23 25 31 33    13 21 23 25 31 33
01 21 23 25 31 33    15 21 23 25 31 33
01 21 23 25 31 33    20 21 23 25 31 33
01 21 23 25 31 33    21 23 24 25 31 33
02 21 23 25 31 33    01 21 23 25 31 33
02 21 23 25 31 33    02 06 21 23 25 31
02 21 23 25 31 33    02 21 23 25 29 33
02 21 23 25 31 33    02 21 23 30 31 33
02 21 23 25 31 33    02 21 24 25 31 33
02 21 23 25 31 33    02 23 25 29 31 33
02 21 23 25 31 33    03 21 23 25 31 33
02 21 23 25 31 33    04 21 23 25 31 33
02 21 23 25 31 33    06 21 23 25 31 33
02 21 23 25 31 33    08 21 23 25 31 33
02 21 23 25 31 33    10 21 23 25 31 33
02 21 23 25 31 33    11 21 23 25 31 33
02 21 23 25 31 33    13 21 23 25 31 33
02 21 23 25 31 33    15 21 23 25 31 33
02 21 23 25 31 33    20 21 23 25 31 33
02 21 23 25 31 33    21 23 24 25 31 33

(51 行受影响)
在Cte，和Cte2中加上原本没有replace的字段不就行了啊，这么简单的问题
RICHEER COCA 2017-09-05
  • 打赏
  • 举报
 回复
引用 6 楼 qq_37170555 的回复:

atxt                 btxt
-------------------- --------------------
01 21 23 25 31 33    01 03 21 23 25 31
。。。

(51 行受影响)
得不到这样的数据格式，因为使用了
SELECT REPLACE([NOTEXT],' ','') AS [notext]
YOLOv5实战垃圾分类目标检测
利用超万张已标注目标检测数据集进行训练，对居民生活垃圾图片进行检测，找出图片中属于哪个类别的垃圾，并指示出在图片中的位置。 本课程的YOLOv5使用PyTorch版的ultralytics/yolov5，分别在Windows和Ubuntu系统上...
java 找出重复的数字
这是一个经典的题型，比如：有1亿个数字，找出其中重复的。 最直观的写法就是双重循环了，但是效率过低。再就是先排序再遍历，又总感觉不太直接。后来偶然查到BitSet有相应的api来处理这个问题，查了下源码，还挺...
算法系列-大数据面试题-两个大文件中找出共同记录
给定a、b两个文件，各存放50亿个url，每个url各占64字节，内存限制是4G，让你找出a、b文件共同的url?（假如每个url大小为10bytes） 2.思考过程 （1）首先我们最常想到的方法是读取文件a整体加载到内存中，建立...
1亿个数中找出最大的100个数（top K问题）
如何在1亿个数中找出最大的100个数（top K问题） ​ 最容易想到的方法是将数据全部排序，然后在排序后的集合中进行查找，最快的排序算法的时间复杂度一般为O（nlogn），如快速排序。但是在32位的机器上，每个float...
怎么在海量数据中找出重复次数最多的一个
　此题，在我之前的一篇文章算法里头有所提到，当时给出的方案是：IP的数目还是有限的，最多2^32个，所以可以考虑使用hash将ip直接存入内存，然后进行统计。 　再详细介绍下此方案：首先是这一天，并且是访问百度...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章