如何使用SQL查找列中不同值的数目

Question

假设我有3个表：

customer_table(customer_id，customer_name(

product_table(product_id，product_name(

purchase_table(product_id，customer_id(

我想找到customer_id和该客户购买的不同类型产品的数量。

这是我目前所掌握的，但不正确

select c.customer_id
from customer_table c;
union
select count(distinct product_id)
from purchase_table
where customer_id = c.customer id;

Answer 1

您可以使用相关的子查询：

select c.customer_id,
(select count(distinct product_id)
from purchase_table
where customer_id = c.customer id) as count_product
from customer_table c;