无法从MySQL查询中获得期望的结果

Question

我想在PHP中使用输出在MySQL中编写查询，但它证明相当困难。

我有3列的表（是的，我知道这个数据库布局是不是最好的，但我不能改变这一点，客户有使用此数据库与此完全相同的布局等软件）

Amount - Product - Package 
2  - Apple - Fruitbasket1 
1  - Pear - Fruitbasket1 
5  - Grape - Fruitbasket1 
2  - Apple - Fruitbasket2 
1  - Pear - Fruitbasket2 
9  - Banana - Fruitbasket2 

到目前为止，我想出了这一点：

SELECT package 
FROM `data` 
WHERE package IN 
(
    SELECT package 
    FROM `data` 
    WHERE product = 'apple' AND amount = '2' 
) 
AND package IN 
(
    SELECT package 
    FROM `data` 
    WHERE product = 'pear' AND amount ='1' 
) 
AND package IN 
(
    SELECT package 
    FROM `data` 
    WHERE product = 'grape' AND amount = '5' 
) 
LIMIT 0,1 

的问题是，当客户将填写“苹果”和“2”的形式，它会返回一个包含2个苹果的所有篮子。我需要的是一份声明，它只接受确切的产品和金额，以返还那些成分所属的篮子，或者根本没有。

即使1个梨和2个苹果在Fruitbasket1中，但如果其他部分（即5个葡萄）未被客户指定，它也不应该给Fruitbasket1。

Answer 1

此查询将返回所有的包，唯一的产品是2个苹果

select package from `data` 
group by package 
having count(case when amount = 2 and product = 'Apple' then 1 end) = 1 
and count(*) = 1 

如果你想梨1个和2个苹果例如

select package from `data` 
group by package 
having count(
    case 
    when (amount = 2 and product = 'Apple') or (amount = 1 and product = 'Pear') 
    then 1 
    end) = 2 
and count(*) = 2 

，你也可以这样写

select package from `data` 
group by package 
having count(case when amount = 2 and product = 'Apple' then 1 end) = 1 
    and count(case when amount = 1 and product = 'Pear' then 1 end) = 1 
    and count(*) = 2 

Answer 2

我只有Oracle方便，所以你可能需要调整一些语法，但后续g您的原始风格我已添加“不在”部分，以确保篮子只包含您正在寻找的水果。这里有一些插入几个例子：

create table data (amount integer, product varchar(12), package varchar(16)); 

insert into data values (2, 'Apple', 'Fruitbasket1'); 
insert into data values (1, 'Pear', 'Fruitbasket1'); 
insert into data values (5, 'Grape', 'Fruitbasket1'); 
insert into data values (2, 'Apple', 'Fruitbasket2'); 
insert into data values (1, 'Pear', 'Fruitbasket2'); 
insert into data values (9, 'Banana', 'Fruitbasket2'); 
insert into data values (2, 'Apple', 'Fruitbasket3'); 
insert into data values (2, 'Apple', 'Fruitbasket4'); 
insert into data values (1, 'Pear', 'Fruitbasket4'); 


SELECT package 
FROM data 
WHERE package IN 
(
    SELECT package 
    from data 
    WHERE product = 'Apple' AND amount = '2' 
) 
and package not in 
(
    SELECT package 
    from data 
    WHERE product <> 'Apple' 
) 
group by package 
; 


SELECT package 
FROM data 
WHERE package IN 
(
    SELECT package 
    from data 
    WHERE product = 'Apple' AND amount = '2' 
) 
AND package IN 
(
    SELECT package 
    FROM data 
    WHERE product = 'Pear' AND amount ='1' 
) 
and package not in 
(
    SELECT package 
    from data 
    WHERE product <> 'Apple' and product <> 'Pear' 
) 
group by package 
;