我有一张桌子：
custID orderID orderComponent
=====================================
1 123 pizza
1 123 wings
1 234 breadsticks
1 239 salad
2 456 pizza
2 890 salad
我有一个价值清单 - 比萨饼，翅膀，面包棒和沙拉。如果客户至少有一条包含每条记录的记录，我需要一种方法来获得真/假值。这是可能的mysql查询，或者我只需要做一个 select distinct(orderComponent) 为每个用户和使用PHP检查结果？
如果您只是想查看客户是否订购了所有商品，那么您可以使用：
select t1.custid,
case when t2.total is not null
then 'true'
else 'false'
end OrderedAll
from yourtable t1
left join
(
select custid, count(distinct orderComponent) Total
from yourtable
where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
group by custid
having count(distinct orderComponent) = 4
) t2
on t1.custid = t2.custid
看到 SQL小提琴演示
如果你想扩展它，看看是否 custid 已经订购了单个订单中的所有商品，那么您可以使用：
select t1.custid,
t1.orderid,
case when t2.total is not null
then 'true'
else 'false'
end OrderedAll
from yourtable t1
left join
(
select custid, orderid, count(distinct orderComponent) Total
from yourtable
where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
group by custid, orderID
having count(distinct orderComponent) = 4
) t2
on t1.custid = t2.custid
and t1.orderId = t2.orderid
看到 SQL小提琴演示
如果您只想要custid和true / false值，那么您可以添加 distinct 到查询。
select distinct t1.custid,
case when t2.total is not null
then 'true'
else 'false'
end OrderedAll
from yourtable t1
left join
(
select custid, count(distinct orderComponent) Total
from yourtable
where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
group by custid
having count(distinct orderComponent) = 4
) t2
on t1.custid = t2.custid
看到 SQL小提琴演示
或者通过custid和orderid：
select distinct
t1.custid,
t1.orderid,
case when t2.total is not null
then 'true'
else 'false'
end OrderedAll
from yourtable t1
left join
(
select custid, orderid, count(distinct orderComponent) Total
from yourtable
where orderComponent in ('pizza', 'wings', 'breadsticks', 'salad')
group by custid, orderID
having count(distinct orderComponent) = 4
) t2
on t1.custid = t2.custid
and t1.orderId = t2.orderid
看到 SQL小提琴演示
这是一种方法。此方法不需要内联视图（派生表），如果要包含多个条件的标志，则此方法可能有效：
这回来了 custID 所有四个项目都有一行：
SELECT t.custID
, MAX(IF(t.orderComponent='breadsticks',1,0))
+ MAX(IF(t.orderComponent='pizza',1,0))
+ MAX(IF(t.orderComponent='salad',1,0))
+ MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
FROM mytable t
GROUP BY t.custID
HAVING has_all_four = 4
（这检查了具有所有四个项目的客户“订单”，而不仅仅是“custID”。）
SELECT t.custID
, t.orderID
, MAX(IF(t.orderComponent='breadsticks',1,0))
+ MAX(IF(t.orderComponent='pizza',1,0))
+ MAX(IF(t.orderComponent='salad',1,0))
+ MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
-- , MAX(IF(t.orderComponent='breadsticks',1,0)) AS has_breadsticks
-- , MAX(IF(t.orderComponent='pizza',1,0)) AS has_pizza
-- , MAX(IF(t.orderComponent='salad',1,0)) AS has_salad
-- , MAX(IF(t.orderComponent='wings',1,0)) AS has_wings
FROM mytable t
GROUP BY t.custID, t.orderID
HAVING has_all_four = 4
这将得到包含所有四个项目的“订单”。如果只想返回custID的值，则使用上面的查询作为内联视图（将其包装在另一个查询中）
SELECT s.custID
FROM (
SELECT t.custID
, t.orderID
, MAX(IF(t.orderComponent='breadsticks',1,0))
+ MAX(IF(t.orderComponent='pizza',1,0))
+ MAX(IF(t.orderComponent='salad',1,0))
+ MAX(IF(t.orderComponent='wings',1,0)) AS has_all_four
FROM mytable t
GROUP BY t.custID, t.orderID
HAVING has_all_four = 4
) s
GROUP BY s.custID
select case when
count(distinct orderComponent) = 4
then 'true'
else 'false'
end as bool
from tbl
where custID=1
@EmmyS：你可以两种方式做到。 如果要使用MySql检查使用：
SELECT @rowcount:=COUNT(*) FROM orderComponent Where (Your Conditions);
IF (@rowcount > 0) THEN
'True'
ELSE
'False'
END IF