mysql - sql从多个表中获取数据

Question

表 1:发票(inv_id、inv_value、cust_id)
表 2:客户(cust_id、sales_rep)
表 3:成员(Member_id、member_cateogry、member_type、cust_id)

注意 1:每个客户支付多张发票。 (一对多关系)。
注2:每个客户为一个或多个成员(member)付费(因此一个以上的成员(member)可能与一个客户相关)。
注3:每个成员都有一个类别，可以是1“代表个人”或2“代表团体”。
注4:每个Member都有一个type，可以是1“代表new”或者2“代表renew”。

我想为 sales_rep = 1 且他们的 member_category = 10 和他们的 的客户获取 Invoice_value 字段的总计成员(member)类型 = 123
例如:如果这些客户的 Sales_rep 为 1，并且他们支付的成员(member)是新成员(member)和个人成员(member)，那么客户支付的发票总额是多少。

我试过:

SELECT Sum(invoices.inv_value) AS total
FROM   invoices,
       customers,
       members
WHERE  invoices.cust_id = customers.cust_id
AND    members.custid = customers.cust_id
AND    members.category = {$category}
AND    members_type = {$type}
AND    customers.sales_rep = {$id}";

和

SELECT     Sum(invoices.inv_value) AS total
FROM       members
INNER JOIN customers
ON         members.custid = customers.cust_id
INNER JOIN invoices
ON         customers.cust_id = invoices.cust_id
WHERE      customers.sales_rep = {$id}
AND        members.category = {$category}
AND        members.type = {$type}";

但两者都返回发票值的两倍。
例如:发票表中 120 美元的发票使用这些 sql 查询返回 240 美元。

我该如何解决这个问题？

Answer 1

这是您的查询:

SELECT sum(i.inv_value) as total
FROM members m INNER JOIN
     customers c
     ON m.custid = c.cust_id INNER JOIN
     invoices i
     ON c.cust_id = i.cust_id
WHERE c.sales_rep = {$id} AND 
      m.category = {$category} AND
      m.type = {$type}";

(不要使用使用逗号的隐式 JOIN 语法。它陈旧且功能较弱。)

问题可能是两个成员可以拥有相同的客户 ID。您可以通过运行来检查:

select m.cust_id, count(*)
from members m
group by m.cust_id
having count(*) > 1;

customers 中的客户 ID 也有可能重复。

假设重复项仅在成员中，将查询更改为exists:

SELECT sum(i.inv_value) as total
FROM customers c
     ON  INNER JOIN
     invoices i
     ON c.cust_id = i.cust_id
WHERE c.sales_rep = {$id} AND 
      EXISTS (SELECT 1
              FROM members m
              WHERE m.custid = c.cust_id AND
                    m.category = {$category} AND
                    m.type = {$type}
             );