我有以下三个表:
客户
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。
发布于 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表中。
发布于 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;
发布于 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, prodname
https://stackoverflow.com/questions/50689055
复制相似问题