sql查询

rkue9o1l 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(400)

我有以下三张表：
顾客
id（主键）
名称
产品
id（主键）
名称
价格
交易
id（主键）
客户id（fk ref customer.id）
产品id（fk ref product.id）
创建日期
我正在运行以下查询，它为我提供每个客户使用的产品数量：

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的产品的列表。我很难弄清楚这一点，因为我使用事务表作为客户和产品之间的连接表，并且我查找每个客户的每个产品的计数的方式是通过记录计数（正如您在上面的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分配给我的结果集中的计数，但最后我将得到两个列表，一个包含产品名称、每个产品的计数和客户使用过的每个产品的价格，另一个列表包含所有产品名称，计数为0，以及客户未使用的每种产品的价格。
这可能是一个解决方案，但似乎远不实际，因为我要处理两个列表，并且必须以编程方式赋值，而不是处理一个包含我需要的所有数据的列表，这将影响排序等。
我想知道这是否可能，或者是否有其他方法来看待这个问题。
我希望这足够清楚。如果你能深入了解此事，我将不胜感激！
毛里西奥b。

sql mysql

来源：https://stackoverflow.com/questions/50689055/sql-query-with-count-and-joining-table

3条答案

按热度按时间

mklgxw1f1#

这应该适用于大多数dbms。它将所有客户产品的事务表分组，并获取它们的计数。然后将其与产品连接起来。因为它是一个左连接，所有产品都将包括在内。对于客户尚未使用的产品，将使用联接列 NULL . 伯爵也是。与 coalesce() 函数返回的第一个参数不为null，则这些参数的计数为0，其他参数的实际计数为0。如果您的实际dbms不支持 coalesce() 您可能需要将其替换为相应的dbms特定函数。

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;

赞(0）回复(0）举报 2021-06-20

6rvt4ljy2#

您必须执行两个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

赞(0）回复(0）举报 2021-06-20

erhoui1w3#

不要在句子中使用逗号 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在事务表中。

赞(0）回复(0）举报 2021-06-20

我来回答

sql查询

3条答案

相关问题

热门标签

最新问答