代码之家 › 专栏 › 技术社区 › Apis

用于对多个不同表中的字段求和的SQL查询

sum group-by join select sql

0

Apis · 技术社区 · 8 年前

我正在尝试组合两个查询以获得单个数据。但值不正确。

表A

t_item      t_cwar  t_koor  t_qstk
G00046301   EWH200  2   5000
G00046301   EWH200  2   5000
G00046301   EWH200  2   5000
G00046301   EWH200  2   5000
G00046301   EWH200  2   5000
G00046301   EWH200  22  5000
G00046301   EWH401  22  5000
G00046301   EWH401  1   72
G00053001   EWH200  2   5000
G00053001   EWH200  22  5000
G00053001   EWH200  2   5000
G00053001   EWH200  22  5000
G00053001   EWH401  22  5000
G00053001   EWH401  1   5
G00053001   EWH401  51  -4995
G00053001   EWH401  22  5000
G00053001   EWH401  1   50
G00251701   EWH200  2   900
G00251701   EWH200  22  900
G00251701   EWH200  2   900
G00251701   EWH200  2   900
G00251701   EWH200  2   900
G00251701   EWH200  2   900
G00251701   EWH200  22  900

表B

t_waid  t_dicl
EWH100  EV
EWH200  EV
EWH300  EV
EWH301  EV
EWH400  EV
EWH401  EV
EWH800  EV
EWH801  EV

表C

t_item  t_cwar  t_qhnd
G00046301   EWH200  20000
G00046301   EWH401  4928
G00053001   EWH401  4950
G00053001   EWH200  0
G00245101   EWH200  0
G00245101   EWH401  0
G00245101   EWH301  0

查询是

select
    Table_B.t_dicl as Department,
    Table_A.t_item as Item,
    sum(Table_B.t_qstk) as "Production Order" 
from
    Table_A,
    Table_B 
where
    Table_A.t_waid = Table_B.t_cwar
    and
    Table_A.t_koor = 1

以上查询获取此数据

Department       Item       Production Order
EV           G00046301      72
EV           G00053001      55
EV           G00251701      220
EV           G00386201      244
EV           G01021001      55
EV           G01037101      355
EV           G02280100      6
EV           G02280200      55

下一个查询

select
    Table_B.t_dicl as Department,
    Table_C.t_item as Item,
    sum( Table_C.t_qhnd ) as Quantity 
from
    Table_C, 
    Table_B
where
    Table_B.t_waid = Table_C.t_cwar
group by
    Table_C.t_item,
    Table_B.t_dicl

以上查询

Department      Item        Quantity
EV              123         0
EV              A1234       0
EV              G00046301   24928
EV              G00053001   4950
EV              G00245101   0
EV              G00251701   3400
EV              G00386201   11776
EV              G01021001   650
EV              G01030801   2000
EV              G01037101   5650

因此,我尝试将这两个查询结合起来,通过使用内部联接获得单个表

select
    Table_B.t_dicl as Department,
    Table_A.t_item as Item,
    sum( Table_A.t_qstk )  as "Production Order" , 
    sum(Table_C.t_qhnd) as Quantity
from
(
    (
        Table_A 
        inner join Table_C on
            Table_A.t_item = Table_C.t_item
    )
    inner join Table_B on Table_B.t_waid =Table_A.t_cwar
)
where
    Table_A.t_koor = 1
group by
    Table_A.t_item,
    Table_B.t_dicl


Department    Item      Production Order    Quantity on Hand
EV           G00046301  144                 24928
EV           G00053001  110                 9900
EV           G00251701  440                 6800
EV           G00386201  976                 35328
EV           G01021001  110                 1300
EV           G01037101  710                 28250
EV           G02280100  12                  1994
EV           G02280200  110                 23900
EV           G03536100  15                  700
EV           G04655100  710                 16950
EV           G05009800  426                 8376

预期输出示例

Department  Item    Production Order    Quantity
EV           G00046301      72  24928
EV           G00053001  55      4950
EV           G00251701  220     3400
EV           G00386201  244     11776
EV           G01021001  55      650
EV           G01037101  355     5650
EV           G02280100  6       1994
EV           G02280200  55      11950
EV           G03536100  5       700
EV           G04655100  355     5650

有些值不是远程的,而是成倍的。提前谢谢,我只是想在这里学习SQL

1 回复 | 直到 8 年前

1

0

Prabhath Amaradasa 8 年前

SELECT
    ab.*, cb.*
FROM(
    select
        b.t_dicl as Department,
        a.t_item as Item,
        sum(b.t_qstk) as "Production Order" 
    FROM Table_A   AS a INNER JOIN Table_B  AS b ON a.t_waid  = b.t_cwar
    WHERE a.t_koor = 1
    GROUP BY b.t_dicl, a.t_item
    )As ab FULL OUTER JOIN(

        select
        b.t_dicl as Department,
        c.t_item as Item,
        sum( c.t_qhnd ) as Quantity 
    from
        Table_C AS c INNER JOIN Table_B AS B ON  b.t_waid = c.t_cwar

    group by
        c.t_item,
        b.t_dicl
    )AS cb  ON  ab.Department = cb.Department  AND ab.Item = cb.Item