If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1
Go
The create table # test1 (rids INT IDENTITY (1, 1), SRN INT, [unotext] nvarchar (20))
Insert # test1
Select '2021044', '05 07 14 and 25 to 28' union all
Select '2021043', '02 03 17 18 23 24' union all
Select '2021042', '05 09 10 22 to 25 and 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 and 31' union all
Select '2021038', '05 07 09 16 18 27' union all
Select '2021037', '13 17 24 19 and 26 30' union all
Select '2021036', '01, 02 6 November 21 26' union all
Select '2021035', '12 and 18 to 20 26 28' union all
Select '2021034', '04 October 12 18 23 25' union all
Select '2021033', '04 11 12 16 23 to 25' union all
Select '2021032', '04 11 12 16 23 to 25' union all
Select '2021031', '09 16 24, 25 and 27, 28'
Step1:
Ask: the number of the remainder of any 2 digits equals 0 is not less than three times the record
Example:
05 07 14 of 17 25 to 28 from small to large, respectively is n1 n2 and n3 and n4 interchange, n5 and n6,
The arbitrary number 2 larger divided by the Numbers, the rest of the number of 0 record is
N6 % n3=0
N2 n6 %=0
N5 % n1=0
N2 n3 %=0
The remainder of any 2 digits equals the number of 0 to 4, conform to the requirements of the record output to the table [# test2],
Step2:
Respectively take n1, n2 and n3 and n4 interchange, n5 and n6 single digits, if is a continuous natural number is eligible
Such as 09 16 24, 25 and 27, 28 bits are respectively 9/6/4/5/7/8, align is consecutive natural number 456789
, in line with the requirements of the output record to the table [# test2],
Step3:
Figures from the number of bits up every three can be divided into a group,
Such as
05 7 November 17 of 26 and 27, divided into 50 ` ` ` 172 627 711, o + 50-172 (627 + 711), of the absolute value of
Step4:
Parity grouping and summation, set
Even groups combined for Q1
The sum of the odd group for Q2
O surprising array and even set of the absolute value of the difference between the sum of the sum of
Step5:
Calculate (field names are respectively RT2 RT3/RT5/RT7/RT9)
| Q1 and Q2 | % 2=RT2
| Q1 and Q2 | % 5=RT5
| Q1 and Q2 | % 3=RT3
| Q1 and Q2 | % 7=RT7
| Q1 and Q2 |=% 9 RT9
Step6:
The following is a query request
| Q1 and Q2 | % 2=RT2=1
| Q1 and Q2 | % 5=RT5=0
| Q1 and Q2 | % 3=RT3=1
| Q1 and Q2 | % 7=RT7=4
| Q1 and Q2 | % 9=RT9=3
Conform to the requirements of the record output to the table [# test2],
Step1/step2 and step6 duplicate records only remember once,
CodePudding user response:
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1
Go
The create table # test1 (rids/int IDENTITY (1, 1) NOT NULL, no1 int, int, no2 no3 int, no.4 int, no5 int, no5 int, notext varchar (20))
Insert # test1
Select '2021044', '05 and' 07 ', '14' and '17', '26', 'and', '05 07 14 of 17 26 28' union all
Select '2021043', '02', '3', '17', '18', '23', '24', '02 03 17 18 23 24' union all
Select '2021042', '05,' 09 ', '10', '22', '25', '27', '05 09 10 22 to 25 and 27' union all
Select '2021041', '02', '9', '18', '23', '26', '32', '02 09 18 23 26 32' union all
Select '2021040', '02', '06,' 07 ', '24', 'and', '29', '02 06 07 24 to 28, 29' union all
Select '2021039', '02', '08', '21', '24', '29', 'and', '02 08 21 24 and 31' union all
Select '2021038', '05 and' 07 ', '9', '16', '18', '27', '05 07 09 16 18 27' union all
Select '2021037', '13', '17', 'the', '24', '26', '30', '13 17 24 19 and 26 30' union all
Select '2021036', '01', '02', '6', '11', '21', '26', '01, 02 06 26 November 21' union all
Select '2021035', '12', '14', '18', '20', '26', 'and', '12 and 18 to 20 26 28' union all
Select '2021034', '04,' 10 ', '12', '18', '23', '25', '04 October 12 18 23 25' union all
Select '2021032', '04,' 11 ', '12', '16', '23', '25', '04 11 12 16 23 to 25' union all
Select '2021031', '9', '16', '24', '25', '27', 'and', '09 16 24, 25 and 27, 28'
Known data to perfect it
CodePudding user response:
see you these data, we'll feel tired heart, don't know what your final demand, so step by step, ask,,,CodePudding user response:
Step1; As with t (
Select *, ROW_NUMBER () over (PARTITION by rids the order by val desc) as sn
The from # test1
Unpivot (val for col (in no1, no2, no3, no.4, no5, no6)) p
), t1 as (
Select distinct rids, xv, yv
The from t a
Cross the apply (
The select x.v al as xv, y.v al as yv, x.v al % y.v al as mv
The from t x
Left the join t y on x.R id=y.R id and x.s nWhere x.R id=a.R id
And x.v y.v al al %=0
B)
)
Select *
The from (
The select rids, COUNT (0) as the number of
The from t1
Group by rids having (COUNT (0) & gt; 2)
) a
Cross the apply (
Select (
Select the CONVERT (varchar, xv) + '/' + CONVERT (varchar, yv) + '; '
The from t1
Where rids=a.R id
For XML path (' ')
) as expr
B)
Left the join # test1 c on a.R id=c.R id
CodePudding user response:
Step2; As with t (
Select *, ROW_NUMBER () over (PARTITION by rids the order by val desc) as sn
The from # test1
Unpivot (val for col (in no1, no2, no3, no.4, no5, no6)) p
), t1 as (
Select rids, col, 10 as val val %
ROW_NUMBER () over (partition by rids the order by val % 10) as vid
The from t
)
Select * from (
The select rids
The from t1
Group by rids, val - vid having (COUNT (0)=6)
) a
Left the join # test1 b on a.R id=b.R id
Then, wait reply I can reply again
CodePudding user response:
Advised to make his first try writing part, then put not the core of the problem, summarize and abstract questions more appropriate.nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull