MySQL ONLY IN()等效子句 [英] MySQL ONLY IN() equivalent clause

问题描述

我在这里给出我的问题的非常抽象的版本，请耐心等待.我有一个查询，它将检查特定的主体是否具有某些相同类型的多个参数.例如，就巧克力而言，男孩有多种选择.但是，我想从桌子上选择男孩，他们吃的都是我提到的巧克力.不多不少，也不是喜欢"或"IN()".

I am giving a very abstract version of my question here, so please bear with me. I have a query that will check whether a particular body has certain multiple parameters of same type. Example, a boy has multiple selection as far as chocolates are concerned. But, I want to choose boys from the table who have exactly the chocolates I mention. Not more not less and not 'LIKE' or not 'IN()'.

SELECT boy_id from boys_chocolates WHERE chocolate_id ONLY IN('$string');

..当然"$ string"是一个PHP变量，其中包含逗号分隔的值，这些值仅是我想用来拉男孩的那些巧克力.

..where of course '$string' is a PHP variable containing comma separated values of only those chocolates I want to use to pull the boys.

我知道这是无效的MySQL语句，但是是否有与此有效的等效项?

I know this is invalid MySQL statement, but is there any valid equivalent to this?

这是一个更全面的查询，它在特殊情况下(但并非总是)获取记录.

This is more comprehensive query which gets records in special cases, but not always.

SELECT boys.* FROM schools_boys INNER JOIN boys ON boys.boy_id=schools_boys.boy_id
INNER JOIN chocolates_boys a ON a.boy_id=boys.boy_id INNER JOIN schools
ON schools.school_id=schools_boys.school_id WHERE a.chocolate_id IN(1000,1003)
AND 
            EXISTS
            (
                    SELECT 1
                    FROM chocolates_boys b
                    WHERE a.boy_id=b.boy_id
                    GROUP BY boy_id
                    HAVING COUNT(DISTINCT chocolate_id) = '2'
                    )

                GROUP BY schools_boys.boy_id HAVING COUNT(*) = '2'

Boys Table
+--------+-------------+
| id     | boy         |
+--------+-------------+
| 10007  | Boy1        |
| 10008  | Boy2        |
| 10009  | Boy3        |
+--------+-------------+

Chocolates Boys Table
+----+---------+--------------+
| id | chocolate_id | boy_id |
+----+--------------+---------+
| 1  | 1000         | 10007   |
| 2  | 1003         | 10007   |
| 3  | 1006         | 10007   |
| 4  | 1000         | 10009   |
| 5  | 1001         | 10009   |
| 6  | 1005         | 10009   |
+----+--------------+---------+

当我单独选择1000来拉两个男孩(或)1000和1003来拉出ID为10007的男孩时，什么都没有发生.

Nothing happens when I select 1000 alone to pull two boys (or) 1000 and 1003 to pull out the boy with ID 10007.

推荐答案

此问题称为Relational Division

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN ('$string')
GROUP  BY boy_id 
HAVING COUNT(DISTINCT chocolate_id) = ? -- <<== number of chocolates specified

示例:

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN (1,2,3,4)
GROUP  BY boy_id 
HAVING COUNT(DISTINCT chocolate_id) = 4

• 关系部门的SQL
• SQL of Relational Division

但是，如果chocolate_id对于每个boy_id是唯一的，则DISTINCT关键字是可选的.

however, if the chocolate_id is unique for every boy_id, DISTINCT keyword is optional.

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN (1,2,3,4)
GROUP  BY boy_id 
HAVING COUNT(*) = 4

---

更新1

...我想从表中选择那些我刚提到的巧克力的男孩.不多不少...

...I want to choose boys from the table who have exactly the chocolates I mention. Not more not less...

SELECT boy_id 
FROM   boys_chocolates a
WHERE  chocolate_id IN (1,2,3,4) AND
        EXISTS 
        (
            SELECT  1
            FROM    boys_chocolates b
            WHERE   a.boy_ID = b.boy_ID
            GROUP   BY boy_id
            HAVING  COUNT(DISTINCT chocolate_id) = 4
        )
GROUP  BY boy_id
HAVING COUNT(*) = 4

• SQLFiddle演示
• SQLFiddle Demo

这篇关于MySQL ONLY IN()等效子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！