MySQL匹配5个字段中的2个 [英] MySQL matching 2 out of 5 fields

问题描述

在MySQL中，我试图选择与所提供数据的至少2个字段匹配的任何行

In MySQL I'm trying to select any row that matches at least 2 fields of the provided data

例如.我已经获得了firstName，lastName，dob，网站，电子邮件，并且我想要与firstName和lastName或firstName和email或网站和电子邮件等匹配的任何行.

Eg. I have been given firstName, lastName, dob, website, email and I want any rows that match firstName and lastName, or firstName and email, or website and email etc.

我知道我可以按照以下方式写一段很长的陈述 (this = this AND this = this)或(this = this等 但是此查询可能会变得非常大，尤其是如果我们决定要在5个以上的字段中进行匹配的话.

I know I could write a very long winded statement along the lines of (this=this AND this=this) OR (this=this etc but this query could potentially get really large, especially if we decide we want to match on more than 5 fields.

我们还需要对匹配的行进行排名，因此，如果某些行匹配3，而不是仅匹配最小的2个字段，则它们应在返回的结果中显示较高的位置.

We will also need to rank the matching rows, so if some rows match 3 instead of only the minimum 2 fields then they should show up higher in the returned results.

我以后可以使用PHP处理此问题，还是执行多个SQL查询，我只是想知道是否有人知道一种更简单的方法来匹配此数据?

I could process this afterwards with PHP, or do multiple SQL queries, I'm just wondering if anyone knows an easier/cleaner way to match this data?

我非常感谢您的帮助！ 乔

I appreciate any help! Jo

推荐答案

您可以算出匹配的表达式. MySQL返回1表示真​​"，0表示假".

You could count up the matching expressions. MySQL returns 1 for true and 0 for false.

WHERE (FirstName = ?) + (LastName = ?) + (... = ?) > 2

您也可以使用此命令进行订购.您将需要按降序排列，以确保较高的匹配项首先出现.

You can also order using this as well. You will want to sort descending to ensure that the higher matches appear first.

ORDER BY ((FirstName = ?) + (LastName = ?) + (... = ?)) DESC

这篇关于MySQL匹配5个字段中的2个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！