MySQL Query With Combinatorial Where Clause-CodePudding

Let's say I have a table with Columns A, B, C, D, E and F. How would I query for entries where (A, B, C, D, E, F) = (1, 2, 3, 4, 5, 6) but only a subset of columns need to match? For example at least 3 out of the 6 columns have to match.

The only solution I can think of is to go through all combinations where (A, B, C) = (1, 2 ,3) or (A, B, D) = (1, 2, 4) or...

But in this example that would already be 20 where clauses, if my math is correct. Is there a better solution, that also works with more columns? Or is my only option to programmatically create a huge, non human-readable query string with hundreds of where clauses?

CodePudding user response：

In MySql boolean expressions are evaluated as 1 for true or 0 for false, so you can add them in the WHERE clause:

WHERE (A = 1)   (B = 2)   (C = 3)   (D = 4)   (E = 5)   (F = 6) >= 3

Just in case any of the 6 columns is nullable, use the NULL-safe equal to operator <=> instead of =:

CodePudding user response：

You can use a score system and then get the rows sorted by score. For example:

select *
from (
  select t.*, 
    case when a = 1 then 1 else 0 end   
    case when b = 2 then 1 else 0 end   
    case when c = 3 then 1 else 0 end   
    case when d = 4 then 1 else 0 end   
    case when e = 5 then 1 else 0 end   
    case when f = 6 then 1 else 0 end as score
  from t
) x
where score >= 3
order by score desc

Of course, this query won't be efficient in terms of execution time, but should work well for small subsets of data.