mysql 查询是否有可能返回 true/false 而不是值？

Is it possible for a mysql query to return true/false instead of values?

提问by EmmyS

I have a table:

我有一张桌子：

custID    orderID    orderComponent
=====================================
1          123        pizza
1          123        wings
1          234        breadsticks
1          239        salad
2          456        pizza
2          890        salad

I have a list of values - pizza, wings, breadsticks, and salad. I need a way to just get a true/false value if a customer has at least one record containing each of these. Is that possible with a mysql query, or do I just have to do a select distinct(orderComponent)for each user and use php to check the results?

我有一份价值观清单——比萨饼、鸡翅、面包棒和沙拉。如果客户至少有一个包含其中每一个的记录，我需要一种方法来获取真/假值。这可以通过 mysql 查询实现，还是我只需要select distinct(orderComponent)为每个用户做一个并使用 php 来检查结果？

采纳答案by Taryn

If you are just looking to see if the customer has ordered all items, then you can use:

如果您只是想查看客户是否订购了所有商品，那么您可以使用：

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

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

If you want to expand this out, to see if the custidhas ordered all items in a single order, then you can use:

如果你想扩展它，看看是否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

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

If you only want the custid and the true/false value, then you can add distinctto the query.

如果您只想要 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

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

Or by custid and orderid:

或者通过 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

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

回答by triclosan

select case when 
count(distinct orderComponent) = 4 
then 'true' 
else 'false' 
end as bool
from tbl
where custID=1

回答by spencer7593

Here's one approach. This approach does not require an inline view (derived table), and can be effective if you want to include flags for multiple conditions:

这是一种方法。这种方法不需要内联视图（派生表），如果您想包含多个条件的标志，这种方法可能很有效：

EDIT:

编辑：

This returns custIDthat has a row for all four items:

这将返回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

---

---

ORIGINAL ANSWER:

原始答案：

(This checked for a customer "order" that had all four items, rather than just a "custID".)

（这检查了包含所有四个项目的客户“订单”，而不仅仅是“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

That will get the "orders" that have all four items. If you want to return just values for custID, then use the query above as an inline view (wrap it in another query)

这将获得包含所有四个项目的“订单”。如果您只想返回 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

回答by Learner

@EmmyS: you can do it both ways. If you want to check using MySql use:

@EmmyS：你可以两种方式都做。如果要使用 MySql 进行检查，请使用：

SELECT @rowcount:=COUNT(*) FROM orderComponent Where (Your Conditions);
IF (@rowcount > 0) THEN
    'True'
ELSE
    'False'
END IF