在mysql中，Join返回四行而不是两行？ [英] Join returns four row instead of two rows in mysql?

问题描述

我有三张桌子加入

1）表格::批次：

batch_no流程seq

1切割1

1锤击2

2切割1

2锤击2



2）表::切割

bno切割返工

170902 1000 20

170902 400 80



3）表::锤击

bno loadqty锤击返工

170902 1000 0 0

170902 500 0 0



这是我退回输出的存储过程：

I have three tables to join
1)Table:: Batches:
batch_no process seq
1 Cutting 1
1 Hammering 2
2 Cutting 1
2 Hammering 2

2)Table::Cutting
bno cutting rework
170902 1000 20
170902 400 80

3)Table:: Hammering
bno loadqty hammering rework
170902 1000 0 0
170902 500 0 0

This is my stored procedure to retrun the output:

CREATE DEFINER=`SKP`@`%` PROCEDURE `Ham`(in batch varchar(100))
BEGIN
declare oseq int;
declare gseq int;
declare proc varchar(30);

set @oseq=(select seq from batches where batch_no=batch and `process`='Hammering');
set @gseq=@oseq-1;
set @proc=(select `process` from batches where batch_no=batch and seq=@gseq);
if(@proc='Cutting') then

select b.esize,b.planned_qty,(ifnull(sum((c.cutting+c.rework)),0)-ifnull(h.loadqty,0)) as `In`,ifnull(sum(h.hammering+h.rework),0) as `Out`
from batches as b
left join cutting as c on c.bno=b.batch_no
left join hammering as h on h.bno=b.batch_no
where 
b.batch_no=batch and b.`process`='Hammering';
end if;
END

上述程序返回四行而不是两行。



样本输出

调用Ham（'1'）;



输出：

The above procedure returns the four rows instead of two.

Sample Output
call Ham('1');

Output:

esize                Planned     In     Out   
'9.000 X 9.000 BPM',  '1500',   '2000', '0'

The above result is calculation of 4 rows

结果应该是

The result should be

esize           Planned     In     Out   
'9.000 X 9.000 BPM',  '1500',    '0', '0'

我尝试过：



上述代码的说明：

1）批次不止一个。条件是过程（唯一）

2）切割不止一个。

3）锤击已超过一个。



搜索谷歌，发现它是数据的问题重复，但我没有找到解决方案。

What I have tried:

For Explanation Of above code:
1) Batches has bno more than one. condition is process(unique)
2) Cutting has bno more than one.
3) Hammering has bno more than one.

Searched on google and found that it is problem duplication of data but i dont found solution.

推荐答案

您发布的示例代码很可能已被修改，因此实际上无法查看真正的问题。例如：

- 查询包含未包含在表定义中的列

- 查询包含 SUM 但不包含 GROUP BY

- 加入列包含彼此不匹配的值

- 等等...



但是看一下代码的一些观察结果



根据错误值，引用表中的批号应该是外来的键（除非已经定义过）



您不应该单独获取不同的数据部分，而是尝试在单个语句中实现逻辑。



但最重要的是，如果计算中有太多行，请尝试运行查询而不对值进行求和。使用SELECT *获取计算中包含的所有数据。这样您就可以看到多次列出哪些行，这可以帮助您查明丢失的条件/连接或无效数据。

The example codes you have posted are most likely modified so that it's actually impossible to see the real problem. For example:
- Query contains columns not included in the table definitions
- The query contains SUM but no GROUP BY
- The joining columns contain values not matching each others
- and so on...

But looking at the code some observations

Based on the false values the batch numbers in referencing tables should be foreign keys (unless already defined so)

You shouldn't fetch the different portions of data separately, instead try to implement the logic in a single statement.

But most importantly, if there are too many rows in your calculation try running the query without summing the values. Use SELECT * to fetch all the data that is included in the calculation. This way you should be able to see which rows are listed multiple times and this should help you to pinpoint the missing condition/join or invalid data.

这篇关于在mysql中，Join返回四行而不是两行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！