There are two tables, coating_out (table), coating_in (income), as shown in figure:




Two tables have a common field coatoutno, coatingout coatoutno is unique in the table, coatingin (income) in the table coatoutno have repeat, now need to coatingin (income) against coatoutno number in the table, put the coatinqty (income) the number of columns, reoccupy coatingout (a) in the table coatoutqty minus the coatinqty and (issue number), and the result on the balance (remainder) column,
Write the SQL statement is as follows:


The results should be 10000, actual it is 60000, the figure below:


What a great god see mistakes in where? How to solve? Thank you very much!

CodePudding user response:

First GROUP, the SUM of two tables, respectively, and then the JOIN

CodePudding user response:

reference 1st floor ACMAIN_CHM response:

first GROUP, the SUM of two tables, respectively, and then JOIN

Moderator: hello! Just learning to mysql, specific to how to write? Can you write out? Thank you very much!

CodePudding user response:

The select Arthur c. oatoutqty - the biggest oatinqtysum balance from coating_out c,
(select a. *, sum (coatinqty) coatinqtysum from coating_in a group by a.c oatoutno) b where Arthur c. oatoutno=biggest oatoutno

CodePudding user response:

The select a.c oatoutqty - b.s umcoatinqty balance from coating_out a,
(select coatoutno, sum (coatinqty) sumcoatinqty
The from coating_in
Group by coatoutno) b
Where a.c oatoutno=biggest oatoutno
, I mean you run the results of the SQL language to ask for produce cartesian product, calculation is the cartesian product result set after the value of the
3 floor to write SQL statement is right, but not too serious,

CodePudding user response:

The

reference 4 floor chaigang response:

select a.c oatoutqty - b.s umcoatinqty balance from coating_out a,
(select coatoutno, sum (coatinqty) sumcoatinqty
The from coating_in
Group by coatoutno) b
Where a.c oatoutno=biggest oatoutno
, I mean you run the results of the SQL language to ask for produce cartesian product, calculation is the cartesian product result set after the value of the
3/f write SQL statement is right, but not too serious,

The result by the way, but write PHP code in a web page can run more, web page code to understand? How to write?

CodePudding user response:

reference 5 floor wxjdonny reply:

Quote: refer to 4th floor chaigang response:

The select a.c oatoutqty - b.s umcoatinqty balance from coating_out a,
(select coatoutno, sum (coatinqty) sumcoatinqty
The from coating_in
Group by coatoutno) b
Where a.c oatoutno=biggest oatoutno
, I mean you run the results of the SQL language to ask for produce cartesian product, calculation is the cartesian product result set after the value of the
3/f write SQL statement is right, but not too serious,

The result by the way, but write PHP code in a web page can run more, web page code to understand? How to write?

I'm sorry, don't understand the code

CodePudding user response:

The select Arthur c. oatoutqty - the biggest oatinqtysum balance from coating_out c,
(select a. *, sum (coatinqty) coatinqtysum from coating_in a group by a.c oatoutno) b where Arthur c. oatoutno=biggest oatoutno

CodePudding user response:

Can use window function, refer to "how to implement window functions help MySQL" this article