22,301
社区成员




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'
--- 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
;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 的情况
,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 的情况,那如何修改代码,期盼中
#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
;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
楼主应该注意到了,我基本都是列转行之后处理的;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
然后,等别人回帖我才能再回帖了;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
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'
已知数据完善一下