Mysql选择匹配的4、5或6列。

Question

我正在对有6个不同列的DB进行自定义搜索。为了进行搜索，我有6个不同的输入字段，它们只接受数字。

我已经让6列搜索正常工作了，代码：

SELECT * FROM data 
WHERE 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
AND 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a')
ORDER BY id ASC;

这给出了正确的结果，尽管输入的顺序。

但我需要选择给我的结果，无论它找到与4或5的数字输入。我尝试了OR，但是我不知道如何定义匹配的列的数量。

这个是可能的吗？

谢谢大家的关注！

编辑：

我被dbaseman提供的代码卡住了。

因为我得到了一个真实的结果，但我真的不知道它是通过匹配4、5或6列来生成的。

我想在后面插入一个箱子，但不起作用。

有没有办法知道哪个列的组合给了我结果？

.rough=‘rough 4’>结果- 4，如果= 5，则结果-5.等等.然后能够将行放入数组中。

谢谢!

Answer 1

与其使用和/或，不如总结点击率：

SELECT *,
    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_1,
    CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_2,
    CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_3,
    CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_4,
    CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_5,
    CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_6,

    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    AS no_of_matches
FROM data 
WHERE 
    (
    CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    + CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END
    ) in (4,5,6)
ORDER BY id ASC;

另一种方法是，使用嵌套查询更漂亮一些：

SELECT a.*, a.match_1+a.match_2+a.match_3+a.match_4+a.match_5+a.match_6 AS no_of_matches
FROM (
    SELECT *,
        CASE WHEN 1_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_1,
        CASE WHEN 2_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_2,
        CASE WHEN 3_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_3,
        CASE WHEN 4_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_4,
        CASE WHEN 5_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_5,
        CASE WHEN 6_column IN ('$a1a','$a2a','$a3a','$a4a','$a5a','$a6a') THEN 1 ELSE 0 END AS match_6
        FROM data 
    ) a
WHERE a.match_1+a.match_2+a.match_3+a.match_4+a.match_5+a.match_6 in (4,5,6)
ORDER BY id ASC;