I want to check if there is a 5-digit sequence in the 6-8 digit sequence.

This is my first time asking a question.

Currently, Python+SQL replaces the formula written in Excel and displays it in PHP.
I'm working on a project.(I'm aiming to get out of Excel)

I got caught in the middle of it.
I don't know how to determine if a 6- to 8-digit sequence contains a 5-digit sequence.
(1 to 5 digits)

For example, the following sequence is defined:

1 | 2 | 3 | 4 | 5 | 6

In this sequence

5 | 7 | 11 | 9 | 4

True if all are included.
We are calculating that

for Excel descriptions

IF(
    AND(
        OR (5=1,7=1,11=1,9=1,4=1),
        OR (5=2,7=2,11=2,9=2,4=2),
        OR (5=3,7=3,11=3,9=3,4=3),
        OR (5=4,7=4,11=4,9=4,4=4),
        OR (5=5,7=5,11=5,9=5,4=5
    )
,0,1)

Yes, trying to write this in Python+SQL

query="select id from mst_nin_%where"%(suffix)
    if(suffix=='s8'):
        for vin range (1, 6):
            if(v!=1):
                query+="AND"
            varname = "num" + str(v);
            query+="'%s'in(num1, num2, num3, num4, num5, num6, num7, num8)"%(eval(varname))

    elif(suffix=='s7') :
        for vin range (1, 6):
            if(v!=1):
                query+="AND"
            varname = "num" + str(v)
            query+="'%s' in (num1, num2, num3, num4, num5, num6, num7)"% (eval(varname))
    elif(suffix=='s6'):
        for vin range (1, 6):
            if(v!=1):
                query+="AND"
            varname = "num" + str(v)
            query+="'%s'in(num1, num2, num3, num4, num5, num6)"%(eval(varname))
...

I prepared the Python code and generated SQL.It is as follows.

select id from mst_nin_s6where
 '5' in (num1, num2, num3, num4, num5, num6)AND
 '7' in (num1, num2, num3, num4, num5, num6)AND
 '11' in (num1, num2, num3, num4, num5, num6)AND
 '9' in (num1, num2, num3, num4, num5, num6)AND
 '4' in (num1, num2, num3, num4, num5, num6)

We then ran SQL to see if it existed in PHP. SQL is as follows:

select
group_concat(distinct(mb.num1)SEPARATOR',') as num1,
group_concat(distinct(mb.num2)SEPARATOR',') as num2,
group_concat(distinct(mb.num3)SEPARATOR',') as num3,
group_concat(distinct(mb.num4)SEPARATOR',') as num4,
group_concat(distinct(mb.num5)SEPARATOR',') as num5,
group_concat(distinct(mb.num6)SEPARATOR',') as num6
from input_nin_s6 asib join mst_nin_s6mbonib.nin_s6_id REGEXP CONCAT('(^|,)', mb.id,'(,|$)')
group by ib.id

The following results will be returned when executed.

At this time, the irrelevant 10 came back.

I don't know why 10 comes back.If you understand, could you please let me know?

I look forward to your kind cooperation.

python mysql sql

2022-09-29 21:28

2 Answers

select id from mst_nin_s6where
'5' in (num1, num2, num3, num4, num5, num6)

How to use the in clause in
Column name in (value 1, value 2...)
I think is normal, but

as indicated Value in (column name 1, column name 2...)
I don't know how to use it like this.
Why don't you check if the return result of query on python instead of php is normal?

Also, in the first Excel example, if the cell says 5 | 5 | 5 | 5 | 5 | 5 | 5 etc, it becomes true.
I think the original expectations are wrong.

2022-09-29 21:28

In the Excel formula, the duplicate and missing numbers are also determined to be true.
※ Did you omit the missing item?

Assume the in syntax in the question is acceptable because no SQL product or policy is specified...

For this SQL, it is true if there are five numbers in five of the minimum six columns.

because one of the remaining columns is missing. If the data satisfies the above, one column will be retrieved with any value.
That's why I think the number 10 is included.

(Regardless of whether the original is correct or not) To make the decision the same as Excel,
where num1 in ('5', '7', '11', '9', '4')
and num2 in ('5', '7', '11', '9', '4')
and num3 in ('5', '7', '11', '9', '4')
and num4 in ('5', '7', '11', '9', '4')
and num5 in ('5', '7', '11', '9', '4')
and so on.

If you want to exclude duplicate numbers, you need to compare the columns.
num1!=num2 and num1!=num3 and num2!=num3 and...

Either way, the contents of the description and the written judgment are different.
You should check the original specifications before creating the code

2022-09-29 21:28

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656