如何从两个不同的表中减去两个不同列的值?

问题描述：

示例表结构

表 1

ID      |    Name    |  Price
-----------------------------
1       |   Casio    |  30
2       |   Titan    |  40

表 2

ID      |    Place   |  Price
-----------------------------
1       |   Cali    |  30 

2       |   Mexi    |  10

要执行的操作:ID = 1 的 Table1(Price) - Table2(Price)

新表 1

ID      |    Name    |  Price
-----------------------------
1       |   Casio    |  0
2       |   Titan    |  40

两个表中的 ID 匹配

ID matches in both tables

答

正如您所说，两个表将具有相同的 ID 列，您可以使用以下查询.

As you told both table will have same ID column you can use following query.

SELECT table1.ID, table1.Name, (table1.Price-table2.Price) AS Price 
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID

如果你想更新记录，你可以使用以下内容:

If you want to update record you can use following:

UPDATE table1
    INNER JOIN table2 ON table1.ID = table2.ID
        SET table1.Price = (table1.Price-table2.Price)