php - MySQL 加入特定的 WHERE 子句

Question

我有一个简单的数据库

通过这个查询，我得到了所有用户和他们购买的产品数量:

SELECT
  c.id,
  cd.name,
  cd.surname,
  COUNT(p.name) as bought
FROM shopping s
 JOIN client c ON s.client_id = c.id
 JOIN client_details cd ON c.id = cd.client_id
 JOIN product p ON s.product_id = p.id
GROUP BY c.id;

很好，但我必须选择所有用户和购买的特定产品的数量。通过此查询，我仅在用户至少购买了一种产品时才得到它:

SELECT
  c.id,
  cd.name,
  cd.surname,
  COUNT(p.name) as bought
FROM shopping s
 JOIN client c ON s.client_id = c.id
 JOIN client_details cd ON c.id = cd.client_id
 JOIN product p ON s.product_id = p.id
WHERE p.name = 'sugar'
GROUP BY c.id;

我想获取所有用户的列表，如果特定用户没有购买特定产品，则输出应该为 0。

Answer 1

使用条件聚合:

SELECT c.id, cd.name, cd.surname,
       MAX(p.name = 'sugar') as SugarFlag
FROM shopping s JOIN
     client c
     ON s.client_id = c.id JOIN
     client_details cd
     ON c.id = cd.client_id JOIN
     product p
     ON s.product_id = p.id
GROUP BY c.id;