sql请问减去两个字段[关闭]

Question

I want to ask how to make something like this:

Ive got two tables lets call it A and B. A has a amount_a and in B we have amount_b. I want to ask how to make amount_a - amount_b and then the result asign to amount_a. How can I make it ? All simple examples nice to see. Thank you very much

Answer 1

Very simple example (JOINing on id):

CREATE two tables with five rows of numbers:

CREATE TABLE table_a (
   id int auto_increment primary key
 , amount_a int(2)
);

INSERT INTO table_a (amount_a)
VALUES (6),(72),(58),(39),(0);

CREATE TABLE table_b (
   id int auto_increment primary key
 , amount_b int(2)
);

INSERT INTO table_b (amount_b)
VALUES (1),(23),(31),(14),(51);

Query:

SELECT
   amount_a - amount_b AS amount
FROM table_a
JOIN table_b ON table_a.id = table_b.id

http://sqlfiddle.com/#!2/9b9c6/1

Results in:

amount
5
49
27
25
-51

And running an UPDATE to amount_a:

UPDATE table_a
JOIN table_b ON table_a.id = table_b.id
SET amount_a = amount_a - amount_b

http://sqlfiddle.com/#!2/0c39e/1