选择内部联接返回错误结果 [英] Select inner join returns wrong result

问题描述

我正在使用如下所示的选择内部联接.如何获得预期的结果?

I'm using select inner join as below. How do I get my expected result?

这是sql语法:

select tgr.id, tgr.venid, sum(tgrd.qty*tgrd.pri), sum(tgrp.amo)
from tgr inner join tgrd on tgr.id = tgrd.id
inner join tgrp on tgr.id = tgrp.id
where tgr.id = 3
group by tgr.id, tgr.venid
having sum(tgrd.qty*tgrd.pri)-sum(tgrp.amo)>0;

结果:

3 | 1 | 462000 | 262000

但是我期望结果:

3 | 1 | 231000 | 131000

来自3个表的源代码:tgr，tgrd，tgrp

source from 3 tables: tgr, tgrd, tgrp

tgr table
id  venid
3   1

tgrd table
id  plu qty pri
3   2   2.7 45000
3   1   7.3 15000

tgrp table
id  type  amo
3   2     0
3   2     131000

任何帮助将不胜感激.

推荐答案

因为您有从 tgr 到 tgrd 和 tgrp的多对多关系，则需要在对表进行 JOIN 之前执行汇总，否则可以对每个值加倍(或更多)计数.该查询将为您提供所需的结果:

Because you have a many-to-many relationship from tgr to tgrd and tgrp, you need to perform aggregation before JOINing the tables, otherwise you can double (or more) count each value. This query will give you the results you want:

select tgr.id, tgr.venid, total, amo
from tgr 
inner join (select id, sum(qty*pri) as total
            from tgrd
            group by id) tgrd on tgr.id = tgrd.id
inner join (select id, sum(amo) as amo
            from tgrp
            group by id) tgrp on tgr.id = tgrp.id
where tgr.id = 3
group by tgr.id, tgr.venid
having total - amo > 0;

输出:

id  venid   total       amo
3   1       231000.00   131000

3v4l.org上的演示

这篇关于选择内部联接返回错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！