用SQL代碼批量/快速尋找滿足條件的記錄-有解無憂

快速尋找滿足條件的記錄



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  重復的記錄只記一次。

uj5u.com熱心網友回復：



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'

已知資料完善一下

uj5u.com熱心網友回復：

看到你這些資料，就感覺心累，完全不明白你的最終需求時什么，這么一步一步問。。。

uj5u.com熱心網友回復：

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

uj5u.com熱心網友回復：

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

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

uj5u.com熱心網友回復：

建議LZ自己先嘗試寫一部分, 然后把不會的核心問題, 抽象總結一下再拿來提問比較合適喔.

uj5u.com熱心網友回復：

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

樓主應該注意到了，我基本都是列轉行之后處理的

uj5u.com熱心網友回復：

參考 5 樓唐詩三百首的回復:

建議LZ自己先嘗試寫一部分, 然后把不會的核心問題, 抽象總結一下再拿來提問比較合適喔.

人家這是找碼農來打打下手，主要是辛苦我們顧哥了。

uj5u.com熱心網友回復：



#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

uj5u.com熱心網友回復：

參考 6 樓文盲老顧的回復:

Step4

樓主從前一貼就注意到了，大神基本都是列轉行之后處理的，絕招

從上一帖就開始領會，還有正則運算式，都是精髓所在，特別是用CROSS APPLY解開了困擾我的一些問題，#8 是對#4的優化，讓我茅塞頓開。非常好

小白在努力消化學習中，嚴重感謝版主和顧大師的幫助。

參考 3 樓文盲老顧的回復:

Step1

如果no1=1時，no(k)/no1=no(k),LZ嘗試設定排除 no1=1時結果，但試了幾次，失敗告終，再次請教大神指點。

uj5u.com熱心網友回復：

參考 5 樓唐詩三百首的回復:

建議LZ自己先嘗試寫一部分, 然后把不會的核心問題, 抽象總結一下再拿來提問比較合適喔.

感謝版主，主要是學習老師的思路，嘆為觀止。如果我把自己寫的代碼放在上面，就怕影響到大神。再者對于不會的核心問題如果描述不準確就會引起誤會，更是浪費大神寶貴的時間，再次感謝各位

uj5u.com熱心網友回復：

參考 7 樓 morliz子軒的回復:

Quote: 參考 5 樓唐詩三百首的回復:
建議LZ自己先嘗試寫一部分, 然后把不會的核心問題, 抽象總結一下再拿來提問比較合適喔.

人家這是找碼農來打打下手，主要是辛苦我們顧哥了。

感謝大神

正在努力學習

uj5u.com熱心網友回復：

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 的情況

uj5u.com熱心網友回復：

在t1的cross 里加 y.val<>1，之前那個yv<>1已經在統計后了，那個是輸出expr用的，不影響之前的計算，所以無效

uj5u.com熱心網友回復：

參考 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

uj5u.com熱心網友回復：

資料量大就用臨時表，不要用cte搞大批量資料

uj5u.com熱心網友回復：

參考 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 的情況，那如何修改代碼，期盼中

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/281883.html

標籤：疑難問題

上一篇：請問這個程式哪里錯了，我運行不出來。

下一篇：如何選取SQL server與python爬蟲之間的管理教學書籍？