在所有组合sql中查询6个不同的列，其中包含6个数字

Question

I have a database with 6 columns, n1 - n6. each has a number in it, so each row has 6 different numbers. When I query I am checking the 6 numbers I have to submit against each row in the database. I need the query to change the combination so in theory 36 queries are done.

So an example dataset might be

N1  N2  N3  N4  N5  N6
1   12  54  36  17  23

and the numbers I have to query against this would be

1 54 36 17 23 12

so they are the same but because they are in a different order it would not return a result. All 6 must match to return positive.

Example query:

$sql = "SELECT * FROM numbers WHERE n1 = :n1 AND n2 = :n2 AND n3 = :n3 AND n4 = :n4 AND n5 = :n5 AND n6 = :n6";
$stmt = $conn->prepare($sql);
$stmt->bindParam(":n1", $n1);
$stmt->bindParam(":n2", $n2);
$stmt->bindParam(":n3", $n3);
$stmt->bindParam(":n4", $n4);
$stmt->bindParam(":n5", $n5);
$stmt->bindParam(":n6", $n6);
$stmt->execute();

I could do this with 36 different queries, but to be able to do it in one would be much more usable.

Answer 1

Hmmm . . . Storing six numbers in a row sounds like a bad data format. If these were rows instead of columns, then it would be easier.

However, assuming no duplicates, you can do:

SELECT *
FROM numbers
WHERE n1 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n2 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n3 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n4 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n5 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n6 IN (:n1, :n2, :n3, :n4, :n5, :n6);