问题描述
我有三张桌子加入
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.
推荐答案
这篇关于在mysql中，Join返回四行而不是两行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！