用SQL代码批量/快速寻找满足条件的记录

RICHEER COCA 2021-04-27 03:20:34

快速寻找满足条件的记录



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

go 

create table #test1( Rid INT IDENTITY(1,1),SRN INT,[unotext] nvarchar(20))

insert #test1

select '2021044' ,' 05 07 14 17 25 28' union all

select '2021043' ,' 02 03 17 18 23 24' union all

select '2021042' ,' 05 09 10 22 25 27' union all

select '2021041' ,' 02 09 18 23 26 32' union all

select '2021040' ,' 02 06 07 24 28 29' union all

select '2021039' ,' 02 08 21 24 29 31' union all

select '2021038' ,' 05 07 09 16 18 27' union all

select '2021037' ,' 13 17 19 24 26 30' union all

select '2021036' ,' 01 02 06 11 21 26' union all

select '2021035' ,' 12 14 18 20 26 28' union all

select '2021034' ,' 04 10 12 18 23 25' union all

select '2021033' ,' 04 11 12 16 23 25' union all

select '2021032' ,' 04 11 12 16 23 25' union all

select '2021031' ,' 09 16 24 25 27 28'

step1：
求：任意2个数的余数等于0的次数不小于3次的记录
举例：
05 07 14 17 25 28从小到大分别是n1,n2,n3,n4,n5,n6，
其中任意2个数大数除以小数，其余数为0的记录是
n6 % n3=0
n6 % n2=0
n5 % n1=0
n3 % n2=0
任意2个数的余数等于0的次数为4，符合要求的记录输出到表[#test2]。

step2：
分别取n1,n2,n3,n4,n5,n6的个位数，如果是连续的自然数就符合条件
如09 16 24 25 27 28的个位分别是9/6/4/5/7/8，排列起来就是连续的自然数456789
，符合要求的记录输出到表[#test2]。

step3：
将数字从个位起每三个数分为一组，
如
05 07 11 17 26 27，分为50`711`172`627，求172+50-（627+711）的绝对值。

step4:
奇偶分组并求和，设定
偶数组之和为Q1
奇数组之和为Q2
求出奇数组之和与偶数组之和的差的绝对值

step5:
求出(字段名分别是RT2/RT3/RT5/RT7/RT9)
| Q1-Q2| % 2=RT2
| Q1-Q2| % 5=RT5
| Q1-Q2| % 3=RT3
| Q1-Q2| % 7=RT7
| Q1-Q2| % 9=RT9

step6:
以下是查询要求
| Q1-Q2| % 2= RT2=1
| Q1-Q2| % 5=RT5=0
| Q1-Q2| % 3=RT3=1
| Q1-Q2| % 7=RT7=4
| Q1-Q2| % 9=RT9=3
符合要求的记录输出到表[#test2]。

step1/step2 和 step6 重复的记录只记一次。

...全文

395 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

RICHEER COCA 2021-04-28

打赏
举报

引用 13 楼文盲老顾的回复:

在t1的cross 里加 y.val<>1，之前那个yv<>1已经在统计后了，那个是输出expr用的，不影响之前的计算，所以无效

感谢指点迷津

LZ修改了一下代码，但 table #test1记录超过120W时，效果不好，请帮忙优化，多谢


---  step3  to  step6 流程图
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go 
create table #test1 (Rid [int] IDENTITY(1,1) NOT NULL,SRN INT,no1 int,no2 int,no3 int,no4 int,no5 int,no6 int,notext varchar(20))
insert #test1
select '2021044','05','07','14','17','26','28','05 07 14 17 26 28' union all
select '2021043','02','03','17','18','23','24','02 03 17 18 23 24' union all
select '2021042','05','09','10','22','25','27','05 09 10 22 25 27' union all
select '2021041','02','09','18','23','26','32','02 09 18 23 26 32' union all
select '2021040','02','06','07','24','28','29','02 06 07 24 28 29' union all
select '2021039','02','08','21','24','29','31','02 08 21 24 29 31' union all
select '2021038','05','07','09','16','18','27','05 07 09 16 18 27' union all
select '2021037','13','17','19','24','26','30','13 17 19 24 26 30' union all
select '2021036','01','02','06','11','21','26','01 02 06 11 21 26' union all
select '2021035','12','14','18','20','26','28','12 14 18 20 26 28' union all
select '2021034','04','10','12','18','23','25','04 10 12 18 23 25' union all
select '2021032','04','11','12','16','23','25','04 11 12 16 23 25' union all
select '2021031','09','16','24','25','27','28','09 16 24 25 27 28' 

;with t1 as (
select *, Cast(SUBSTRING(NoText,1,2)+SUBSTRING(NoText,4,1)as int)  as un1,
            Cast(SUBSTRING(NoText,5,1)+SUBSTRING(NoText,7,2)as int)  as un2,
            Cast(SUBSTRING(NoText,10,2)+SUBSTRING(NoText,13,1)as int)  as un3,
              Cast(SUBSTRING(NoText,14,1)+SUBSTRING(NoText,16,2)as int)  as un4
			   from #test1 
			   ),
 t as (
	select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn 
	from t1
	unpivot(val for col in (un1,un2,un3,un4)) p
)
select *,ABS(q1-q2) as RST 
from (
	select Rid
		,SUM(case when val%2=0 then val else 0 end) as q1 
		,SUM(case when val%2=1 then val else 0 end) as q2
	from t
	group by Rid
) a
left join #test1 b on a.Rid=b.Rid

文盲老顾 2021-04-28

打赏
举报

在t1的cross 里加 y.val<>1，之前那个yv<>1已经在统计后了，那个是输出expr用的，不影响之前的计算，所以无效

文盲老顾 2021-04-28

打赏
举报

Step1

;with t as (
	select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn 
	from #test1
	unpivot(val for col in (no1,no2,no3,no4,no5,no6)) p
),t1 as (
	select distinct Rid,xv,yv 
	from t a
	cross apply (
		select x.val as xv,y.val as yv,x.val%y.val as mv
		from t x
		left join t y on x.Rid=y.Rid and x.sn<y.sn
		where x.Rid=a.Rid
		and x.val%y.val=0
	) b
)
select * 
from (
	select Rid,COUNT(0) as 次数 
	from t1
	group by Rid having(COUNT(0)>2)
) a
cross apply (
	select (
		select CONVERT(varchar,xv)+'/'+CONVERT(varchar,yv)+';' 
		from t1
		where Rid=a.Rid
		and yv<>1
		for xml path('')
	) as expr
) b
left join #test1 c on a.Rid=c.Rid

cross 里追加一个 yv<>1即排除某被除数等于1 的情况

RICHEER COCA 2021-04-28

打赏
举报

引用 15 楼文盲老顾的回复:

数据量大就用临时表，不要用cte搞大批量数据

继续请教

引用 12 楼文盲老顾的回复:

Step1


cross apply (
	select (
		select CONVERT(varchar,xv)+'/'+CONVERT(varchar,yv)+';' 
		from t1
		where Rid=a.Rid
		and yv<>1   ---  cross 里追加一个 yv<>1即排除某被除数等于1 的情况
		for xml path('')
	) as expr
) b
left join #test1 c on a.Rid=c.Rid


,t1 as (
	select distinct Rid,xv,yv 
	from t a
	cross apply (
		select x.val as xv,y.val as yv,x.val%y.val as mv
		from t x
		left join t y on x.Rid=y.Rid and x.sn<y.sn
		where x.Rid=a.Rid
		--and  y.val<>1 --  在t1的cross 里加 y.val<>1，次数的计算结果无RID=9的这一行记录，有错误,实际有2次
		and x.val%y.val=0
	) b

在t1的cross 里加 y.val<>1，RID是9的记录时，RID=9的这一行记录次数的计算结果无，有错误的，实际应该有2次如果要计算【次数】需要在计算的时候排除某被除数等于1 的情况，那如何修改代码，期盼中

文盲老顾 2021-04-28

打赏
举报

数据量大就用临时表，不要用cte搞大批量数据

RICHEER COCA 2021-04-27

打赏
举报

引用 7 楼 morliz子轩的回复:

[quote=引用 5 楼唐诗三百首的回复:]建议LZ自己先尝试写一部分, 然后把不会的核心问题, 抽象总结一下再拿来提问比较合适喔.

人家这是找码农来打打下手，主要是辛苦我们顾哥了。[/quote]感谢大神

正在努力学习

RICHEER COCA 2021-04-27

打赏
举报

引用 5 楼唐诗三百首的回复:

建议LZ自己先尝试写一部分, 然后把不会的核心问题, 抽象总结一下再拿来提问比较合适喔.

感谢版主，主要是学习老师的思路，叹为观止。如果我把自己写的代码放在上面，就怕影响到大神。再者对于不会的核心问题如果描述不准确就会引起误会，更是浪费大神宝贵的时间，再次感谢各位

RICHEER COCA 2021-04-27

打赏
举报

引用 6 楼文盲老顾的回复:

Step4

楼主从前一贴就注意到了，大神基本都是列转行之后处理的，绝招

从上一帖就开始领会，还有正则表达式，都是精髓所在，特别是用CROSS APPLY解开了困扰我的一些问题，#8 是对#4的优化，让我茅塞顿开。非常好

小白在努力消化学习中，严重感谢版主和顾大师的帮助。

引用 3 楼文盲老顾的回复:

Step1

如果no1=1时，no(k)/no1=no(k),LZ尝试设置排除 no1=1时结果，但试了几次，失败告终，再次请教大神指点。

RINK_1 2021-04-27

打赏
举报


#STEP2

WITH CTE
AS
(SELECT * FROM #test1
UNPIVOT (QTY FOR NUM IN ([no1],[no2],[no3],[no4],[no5],[no6])) B)

SELECT RID,MAX(notext) AS notext
FROM
(SELECT *,QTY%10 AS QTY_FINAL FROM CTE) AS A
GROUP BY RID 
HAVING MAX(QTY_FINAL)-MIN(QTY_FINAL)=5 AND COUNT(DISTINCT QTY_FINAL)=6

morliz子轩 2021-04-27

打赏
举报

引用 5 楼唐诗三百首的回复:

建议LZ自己先尝试写一部分, 然后把不会的核心问题, 抽象总结一下再拿来提问比较合适喔.

人家这是找码农来打打下手，主要是辛苦我们顾哥了。

文盲老顾 2021-04-27

打赏
举报

Step4

;with t as (
	select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn 
	from #test1
	unpivot(val for col in (no1,no2,no3,no4,no5,no6)) p
)
select *,ABS(q1-q2) as 差 
from (
	select Rid
		,SUM(case when val%2=0 then val else 0 end) as q1 
		,SUM(case when val%2=1 then val else 0 end) as q2
	from t
	group by Rid
) a
left join #test1 b on a.Rid=b.Rid

楼主应该注意到了，我基本都是列转行之后处理的

唐诗三百首 2021-04-27

打赏
举报

建议LZ自己先尝试写一部分, 然后把不会的核心问题, 抽象总结一下再拿来提问比较合适喔.

文盲老顾 2021-04-27

打赏
举报

Step2

;with t as (
	select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn 
	from #test1
	unpivot(val for col in (no1,no2,no3,no4,no5,no6)) p
),t1 as (
	select Rid,col,val%10 as val 
		,ROW_NUMBER() over(partition by rid order by val%10) as vid
	from t
)
select * from (
	select Rid 
	from t1
	group by Rid,val-vid having(COUNT(0)=6)
) a
left join #test1 b on a.Rid=b.Rid

然后，等别人回帖我才能再回帖了

文盲老顾 2021-04-27

打赏
举报

Step1

;with t as (
	select *,ROW_NUMBER() over(PARTITION by rid order by val desc) as sn 
	from #test1
	unpivot(val for col in (no1,no2,no3,no4,no5,no6)) p
),t1 as (
	select distinct Rid,xv,yv 
	from t a
	cross apply (
		select x.val as xv,y.val as yv,x.val%y.val as mv
		from t x
		left join t y on x.Rid=y.Rid and x.sn<y.sn
		where x.Rid=a.Rid
		and x.val%y.val=0
	) b
)
select * 
from (
	select Rid,COUNT(0) as 次数 
	from t1
	group by Rid having(COUNT(0)>2)
) a
cross apply (
	select (
		select CONVERT(varchar,xv)+'/'+CONVERT(varchar,yv)+';' 
		from t1
		where Rid=a.Rid
		for xml path('')
	) as expr
) b
left join #test1 c on a.Rid=c.Rid

文盲老顾 2021-04-27

打赏
举报

看到你这些数据，就感觉心累，完全不明白你的最终需求时什么，这么一步一步问。。。

RICHEER COCA 2021-04-27

打赏
举报


if object_id('tempdb.dbo.#test1') is not null drop table #test1
go 
create table #test1 (Rid [int] IDENTITY(1,1) NOT NULL,no1 int,no2 int,no3 int,no4 int,no5 int,no5 int,notext varchar(20))
insert #test1
select '2021044','05','07','14','17','26','28','05 07 14 17 26 28' union all
select '2021043','02','03','17','18','23','24','02 03 17 18 23 24' union all
select '2021042','05','09','10','22','25','27','05 09 10 22 25 27' union all
select '2021041','02','09','18','23','26','32','02 09 18 23 26 32' union all
select '2021040','02','06','07','24','28','29','02 06 07 24 28 29' union all
select '2021039','02','08','21','24','29','31','02 08 21 24 29 31' union all
select '2021038','05','07','09','16','18','27','05 07 09 16 18 27' union all
select '2021037','13','17','19','24','26','30','13 17 19 24 26 30' union all
select '2021036','01','02','06','11','21','26','01 02 06 11 21 26' union all
select '2021035','12','14','18','20','26','28','12 14 18 20 26 28' union all
select '2021034','04','10','12','18','23','25','04 10 12 18 23 25' union all
select '2021032','04','11','12','16','23','25','04 11 12 16 23 25' union all
select '2021031','09','16','24','25','27','28','09 16 24 25 27 28'

已知数据完善一下