带有左联接的sql聚合函数 [英] sql aggregate function with left join

问题描述

我的sql查询是:

My sql query is :

select s.no,s.name,sum(e.qty),sum(x.qty) from Stock s left join entry e on s.no=e.no left join exit x on s.no=x.no group by s.no,s.name

我有三张桌子，进，出出口.库存中没有列是主键，输入和退出中没有列是库存表列号的外键.
我得到错误的e.qty，x.qty列的总和值(例如，显示600而不是200)

I have three tables Stock,entry,exit. no column in stock is a primary key and no column in entry and exit is foreign key of stock table''s column no.
I getting wrong sum values of e.qty,x.qty column(e.g displaying 600 instead of 200)

推荐答案

这可能是由于您使用的联接条件所致.
s.no=e.no这可能适用于多于一行的行，从而导致重复并因此导致错误的结果.

示例:

This might be because of the join condition you used.
s.no=e.no This might be true for more than one row leading to duplication and hence wrong result.

Example:

Table A
No Name Qty
1  ABC  10
2  XYZ  20
3  QWE  30

Table B
No Name Qty
2  XYZ  40
5  DFG  30
2  XYZ  40

Table A Left Join Table B:
A.No A.Name A.Qty B.Qty
1    ABC     10    NULL
2    XYZ     20    40
2    XYZ     20    40
3    QWE     30    NULL

如果看到的话，上面的XYZ会根据联接被复制.这会导致错误的总和数.

请参考: W3School左联接 [

If you see, above XYZ is getting duplicated based on the join. This leads to wrong sum number.

Refer: W3School LEFT Join[^]

这篇关于带有左联接的sql聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！