时间戳

首页 > 解决方案 > 列出使用过所有付款方式的任何客户的姓名

mysql - 列出使用过所有付款方式的任何客户的姓名

问题描述

顾客

付款方式

付款选项，c，e，m。我如何找到使用所有付款方式的人并显示他们的姓名

我试过了

 SELECT cr.id, cr.firstName, cr.lastName FROM customer cr
 INNER JOIN purchase pm
 ON (cr.id = paymentmethod.customer)
 WHERE pm.paymethod="c" and pm.paymethod="e" and pm.paymethod="m"
 ORDER BY cr.name DESC

标签： mysqlsql

解决方案

您可以使用聚合和计数：

SELECT cr.id, cr.firstName, cr.lastName
FROM customer cr INNER JOIN
     purchase pm
     ON cr.id = pm.customer
WHERE pm.paymethod IN ('c', 'e', 'm')
GROUP BY cr.id, cr.firstName, cr.lastName
HAVING COUNT(DISTINCT pm.paymethod) = 3    -- number of methods in list
ORDER BY cr.name DESC;

还有其他方法。如果您正在寻找三种特定的付款方式，您也可以使用EXISTS：

select c.*
from customer c
where exists (select 1
              from purchase pm
              where cr.id = pm.customer and pm.paymethod = 'c'
             ) and
      exists (select 1
              from purchase pm
              where cr.id = pm.customer and pm.paymethod = 'e'
             ) and
      exists (select 1
              from purchase pm
              where cr.id = pm.customer and pm.paymethod = 'm'
             );

因为这避免了group by外部查询中的 ，所以它可能比第一个查询快得多（假设您在(customer, paymentmethod).

推荐阅读