使用计数和连接表的SQL查询
我有以下三个表：
客户
id (主键)
名字
产品
id(主键)
名字
价格
事务
id(主键)
customer_id(FK参考Customer.id)
product_id(FK参考Product.id)
date_created
我正在运行以下查询，它给出了每个客户使用的产品数量：
SELECT p.id, p.name, Count(p.id), p.price
FROM transaction t, product p, customer c
WHERE p.id = t.product_id
AND c.id = t.customer_id
AND c.id = 1
group by p.price, p.name, p.id
ORDER BY p.name ASC;这是可行的，除了它只给出了客户使用过的产品和他们各自的数量。我想要的是所有产品的列表，对于那些客户没有使用过的产品，计数为0。我在尝试解决这个问题时遇到了麻烦，因为我使用Transaction表作为客户和产品之间的连接表，并且我通过记录计数来查找每个客户的每个产品的数量(正如您在上面的SQL代码中看到的那样)。
我可以这样做：
SELECT *
FROM product
WHERE product.name NOT IN (SELECT p.name
FROM transaction t, product p, customer c
WHERE p.id = t.product_id
AND c.id = t.customer_id
AND c.id = 1);然后以某种方式将0赋给我结果集中的计数，但随后我将得到两个列表，一个包含产品名称、每个产品的计数以及customer 使用的每个产品的价格，另一个列表将包含所有产品名称、计数0以及customer 没有使用的每个产品的价格。
这可能是一个解决方案，但它看起来很不实用，因为id正在处理两个列表，并且必须以编程方式赋值，而不是处理一个包含我需要的所有数据的列表，这将影响排序等。
我想知道这是否可能，或者是否有另一种方式来看待这个问题。
我希望这是足够清楚的。如果你对此有任何见解，我将不胜感激！
毛利西奥B。
回答 3
Stack Overflow用户
发布于 2018-06-05 05:55:00
永远不要在FROM子句中使用逗号。始终使用正确的、明确的、标准的JOIN语法。
你的问题特别是你需要一个left join
select p.id, p.name, Count(t.id), p.price
from product p left join
transaction t
on p.id = t.product_id and
t.customer_id = 1
group by p.price, p.name, p.id
order by p.name asc;注意:您不需要customer表。id在transaction表中。
Stack Overflow用户
发布于 2018-06-05 05:50:51
这应该可以在大多数DBMS中工作。它对所有customers产品的transaction表进行分组并获取它们的计数。然后将其左连接到产品上。由于它是左连接，因此将包括所有产品。对于那些客户没有使用过的产品，连接的列将是NULL。伯爵也一样。在coalesce()函数返回第一个参数not null的情况下，这些参数的计数为0，其他参数的实际计数为0。如果您的实际数据库管理系统不支持coalesce()，那么您可能需要用相应的数据库管理系统特定的函数来替换它。
SELECT p.id,
p.name,
coalesce(t.count, 0) count,
p.price
FROM product p
LEFT JOIN (SELECT t.product_id,
count(*) count
FROM transaction t
WHERE t.customer_id = 1
GROUP BY t.product_id) t
ON t.product_id = p.id
ORDER BY p.name ASC;Stack Overflow用户
发布于 2018-06-05 06:59:51
您必须执行两个select查询并连接它们：
select prodcust.custname, prodcust.prodname, prodcust.prodId,
isnull(tranCount,0) as count, prodcust.price
from
(
select p.name as prodname, p.id as prodID, c.name as custname, c.id as
custid, p.price
from product p, customer c
)
as prodcust
left join
(
select p.price, p.id, c.id as custid, count(t.id) as tranCount
from Transaction t
inner join customer c
on c.id = t.customer_id
inner join product p
on t.product_id = p.id
group by p.price, p.id, c.id
) as trans
on prodcust.custid = trans.custid
and prodcust.prodID = trans.id
order by custname, prodnamehttps://stackoverflow.com/questions/50689055
复制相似问题