从sql中的多个表中获取数据

Question

表 1：发票（inv_id、inv_value、cust_id）
表 2：客户（cust_id、sales_rep）
表 3：成员（Member_id、member_cateogry、member_type、cust_id）

注1:每个客户支付多张发票。(一对多关系)。< br >注2:每个客户为一个或多个会员付费(因此一个客户可能与多个会员相关)。< br >注3:每个成员都有一个类别，可以是1“代表个人”或2“代表团体”。< br >注4:每个成员都有一个类型，可以是1“代表新”或2“代表更新”。

我想为sales_rep=1、member_category=10、 members_type=123的客户获取

我试过了：

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}";

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

我该如何解决这个问题？

Answer 1

您似乎是从< code >发票中提取< code>inv_value，它与< code >客户有多对一关系，但< code >客户表和< code >成员表有一对多关系。

说你有以下数据

发票表

invoices.cust_id    invoices.inv_value
custid1                 100

客户表

customer.cust_id
custid1

成员表

members.cust_id     members.category
custid1                 1
custid1                 2

在连接所有三个表时

customer.cust_id    members.cust_id         invoices.inv_value  members.category
custid1                 custid1                 100                 1
custid1                 custid1                 100                 2

如果您注意到<；code>；custid1<；/code>；存在于2<；code；membercategory<；/code<；中，<；代码>；发票值>；也会重复。

要解决这个问题，首先可以获取不同的记录，然后将这些不同的记录相加将有助于解决问题，如下所示

解决方案

SELECT Sum(invoices.inv_value) AS total
FROM(
    SELECT DISTINCT members.custid, invoices.inv_value inv_value
    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});

Answer 2

这是你的疑问:

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;

客户id也可能在< code >客户中重复。

假设重复项仅存在于成员中，请将查询更改为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}
             );