mysql中grouping语句对两个不同表求和的差分

vmdwslir  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(378)

这个问题在这里已经有答案了

mysql中sum问题的连接表(2个答案)
两年前关门了。
我正在创建一个门户,其中包含具有销售/付款选项的客户,所有销售都在一个表中,所有付款都在另一个表中。对于客户状态表,我需要得到每个客户的所有付款和每个客户的所有销售加上他们之间的差额的总和。
示例表:
clients ```

id client_name

1 School "Mike Jordan"
2 Car wash "Blah blah"

表 `purchasing` ```
===================================
id  client_id  amount
===================================
1   1          1000
2   1          500
3   2          800
4   2          800

payments ```

id client_id amount
1 1 300
2 2 200
3 2 200

我尝试过内部连接和分组,但这不是得到正确的总和,总是更大的一个,就像它是计数的东西,我不想要(没有总数,只是试图得到总和:

select clients.client_name,
sum(purchasing.amount) as SOLD,
sum(payments.amount) as PAID
from clients
inner join payments on payments.client_id=clients.id
inner join purchasing on purchasing.client_id=clients.id
group by clients.id

在那里我得到了不同的结果(都更大)。

client_name SOLD PAID

School "Mike Jordan" 1500 600
Car wash "Blah blah" 3200 800

如果我尝试按payments.id和/或purchasing.id添加分组,则会得到两个以上的结果。。。我知道有一个分组问题,但我真的不知道它在哪里。
我想添加一个新的列,这只是销售和支付的区别,但服务器生成错误的消息,销售和支付不在字段列表中。
你知道如何用一个查询来完成吗?
fafcakar

fafcakar1#

在执行联接之前,需要对表进行汇总。

SELECT clients.`client_name`
    ,purchasing_sum.SOLD
    ,payments_sum.PAID
FROM clients
INNER JOIN (
    SELECT client_id
        ,sum(amount) as PAID
    FROM payments
    GROUP BY client_id
    ) payments_sum ON payments_sum.client_id = clients.id
INNER JOIN (
    SELECT client_id
        ,sum(amount) as SOLD
    FROM purchasing
    GROUP BY client_id
    ) purchasing_sum ON purchasing_sum.client_id = clients.id
GROUP BY clients.id
gab6jxml

gab6jxml2#

你可以试试这个。使用 UNION ALL 合并 purchasing 以及 payments 那就坐table吧 joinclients .
下一步怎么办 SUM 以及 group by ```
create table clients(
id int,
client_name varchar(50)
);

insert into clients values (1,'School "Mike Jordan"');
insert into clients values (2,'Car wash "Blah blah"');

create table purchasing(
id int,
client_id int,
amount int
);

insert into purchasing values (1,1,1000);
insert into purchasing values (2,1,500);
insert into purchasing values (3,2,800);
insert into purchasing values (4,2,800);

create table payments(
id int,
client_id int,
amount int
);
insert into payments values (1,1,300);
insert into payments values (2,2,200);
insert into payments values (3,2,200);

查询1:

select c.client_name,
sum(SOLD) as SOLD,
sum(PAID) as PAID
from (
SELECT client_id,amount as SOLD,0 as PAID
FROM purchasing
UNION ALL
SELECT client_id,0,amount
FROM payments
)t1 INNER JOIN clients c on t1.client_id = c.id
group by c.client_name

结果:
client_nameSOLDPAID
Car wash "Blah blah"1600400
School "Mike Jordan"1500300

相关问题