从单列中选择多个值

问题描述：

我想从数据库表的单个列中选择多个值，这些值等于多个值.我希望所有这些值都匹配，否则它将不返回任何行.我不想使用"IN"，因为它等于"OR".

I would like to select multiple values from a single column in a database table that equal to a number of values. I want all these values to match otherwise it should return no rows. I do not want to use "IN" as that is equal to "OR".

以下是它应该做什么的基本模型，但是它必须是动态的，因为我希望将其与PDO语句一起使用.如果数据库仅包含id的1和2，它将失败，即不返回任何行.

The following is a basic mockup of what it should do but it needs to be dynamic as I wish to use it with a PDO statement. If the database only contains id's 1 and 2 it should fail ie return no rows.

SELECT
id
FROM
reports
WHERE
id=1 AND id=2 AND id=3

我有以下当前代码，该代码错误地返回了零行:

I have the current code as follow which is incorrectly returning zero rows:

SELECT id,title
FROM reports
WHERE id IN (1,2)
GROUP BY title 
HAVING COUNT(DISTINCT id) = 2

我当前的表结构如下: http://www.sqlfiddle.com/#!2/ce4aa/1

My current table structure is as follows: http://www.sqlfiddle.com/#!2/ce4aa/1

答

您必须使用HAVING COUNT(id) = 3来确保所选行具有所有三个ID.像这样:

You have to use HAVING COUNT(id) = 3 to ensure that the selected rows have all the three id's. Something like:

SELECT *
FROM reports
WHERE id = 1 OR id = 2 OR id = 3 -- Or id IN(1, 2, 3)
GROUP BY SomeOtherField
HAVING COUNT(DISTINCT id) = 3;

或:

SELECT *
FROM reports
WHERE SomeOtherField IN (SELECT SomeOtherField 
                         FROM reports
                         WHERE id = 1 or id = 2 -- Or id IN(1, 2, 3)
                         GROUP BY SomeOtherField
                         HAVING COUNT(DISTINCT id) = 3
                        );

请注意:您必须GROUP BY SomeOtherField，其中SomeOtherField是id以外的其他字段，因为如果您将GROUP BY id与HAVING COUNT(id)结合使用，则不会得到任何记录，因为COUNT(id)将始终为1.

Note that: You have to GROUP BY SomeOtherField where SomeOtherField is other field than id because if you GROUP BY id with HAVING COUNT(id) you won't get any records, since COUNT(id) will be always = 1.

:修复了WHERE子句，由OR代替了AND.

fixed WHERE clause, OR's instead of AND's.

SQL小提琴演示

从单列中选择多个值

相关推荐