返回至少满足三个条件中的两个的值的Where子句

I am trying to write a where clause that will find people who have meet at least two of three criteria. This is an example

我正在写一个where子句，它将查找那些至少满足三个标准中的两个的人。这是一个例子

   SELECT *
   FROM Personal
   WHERE
   [State] = 'Tx' or [City] = 'Austin' or [Gender] = 'Male'

So It should return anyone who Lives in Texas and Austin or Lives in Texas and is Male and so on, but not someone who just lives in Texas, they have to meet at least two of the criteria

所以它应该让任何住在德克萨斯和奥斯汀的人或者住在德克萨斯州的男性，但是不是只住在德克萨斯州的人，他们必须满足至少两个标准

My real query can have more criteria and also include a greater than two or exactly two and so on.

我的真实查询可以有更多的标准，也包括一个大于两个或恰好两个等等。

Thanks in advance

谢谢提前

2 个解决方案

#1

You might add matches in a series of case ... then 1 else 0 end statements and compare final result to number of required matches:

您可以在一系列案例中添加匹配…然后1 else 0结束语句并将最终结果与所需匹配数进行比较:

   SELECT *
   FROM Personal
   WHERE
     case when [State] = 'Tx' then 1 else 0 end
     + case when [City] = 'Austin' then 1 else 0 end
     + case when [Gender] = 'Male' then 1 else 0 end
     >= 2

Alternatively, you might break it into a list of union all:

或者，你也可以把它分解成一个联合所有的列表:

SELECT *
FROM   personal
       INNER JOIN (SELECT id
                   FROM   (SELECT id
                           FROM   personal
                           WHERE  state = 'Tx'
                           UNION ALL
                           SELECT id
                           FROM   personal
                           WHERE  city = 'Austin'
                           UNION ALL
                           SELECT id
                           FROM   personal
                           WHERE  gender = 'Male') a
                   GROUP  BY id
                   HAVING COUNT (*) >= 2) a
         ON personal.id = a.id

#2

 SELECT *
   FROM Personal
   WHERE
   ([State] = 'Tx' AND [City] = 'Austin') or ([State] = 'Tx' AND [Gender] = 'Male')

#1