如何从多个表中减去两列 [英] how to subtract two column from multiple tables

问题描述

如何从多个表中减去sql中的两个值，例如我们在同一个表中减去两个值.Name = tbl1，字段是a = column1，b = column2

like = sum as column1 -column2

现在我的问题是如何从不同的列中减去两个值

例子我有两个表table1 = tbl1和table2 = tbl2

在tbl1我有列A，b&

在tbl2我有列c



现在我想要像这样的东西= c as tbl1.a-tbl1.b

解决方案

如果我理解你的问题，你需要创建一个加入表的查询。完成后，你可以进行计算。



考虑以下示例

  SELECT  t1.A，
 t1.B，
 t2.C，
 t1.A  -  t1.B  -  t2.C  AS 计算
  FROM 表1 t1  INNER   JOIN 表2 t2 
  ON  t1.ParentColumn = t2.ChildColumn 

有关联接的更多信息，请查看 SQL的可视化表示加入 [ ^ ]



ADDITION



如果这是一个更新情况，例如Table1.D然后考虑以下示例

 更新 t1 
  SET  D = t1.A  - t1.B  -  t2.C 
  FROM 表1 t1  INNER   JOIN 表2 t2 
  ON  t1.ParentColumn = t2.ChildColumn 

有关在更新语句中使用联接的信息，请参阅更新 [ ^ ]

另一种变体可能是例如

 更新表1 
  SET  D =（ SELECT  Table1.A  -  Table1.B  -  t2.C 
  FROM 表2 t2 
  WHERE  Table1.ParentColumn = t2.ChildColumn） 

请注意，这必须在Table1中每行只返回一个值，因此如果关系是一对多，则需要限制查询或使用SUM等聚合

how to subtract two values in sql from the multiple table for example we subtract two values in same table table Name=tbl1 and fields are a=column1 and b=column2
like =sum as column1-column2
Now my question is that how to subtract the two values from different column
example i have two table table1=tbl1 and table2=tbl2
in tbl1 i have column A,b &
in tbl2 i have column c

now i want thing like this= c as tbl1.a-tbl1.b

解决方案

If I understand your question correctly, you need to create a query where you join the tables. After doing that you can do the calculation.

Consider the following example

SELECT t1.A,
       t1.B,
       t2.C,
       t1.A - t1.B - t2.C AS Calculation
FROM Table1 t1 INNER JOIN Table2 t2
     ON t1.ParentColumn = t2.ChildColumn

For more information about the joins, have a look at Visual Representation of SQL Joins[^]

ADDITION

If this is to be an update situation for for example Table1.D then consider the following example

UPDATE t1
SET D = t1.A - t1.B - t2.C
FROM Table1 t1 INNER JOIN Table2 t2
     ON t1.ParentColumn = t2.ChildColumn

For info about using joins in update statements, see UPDATE[^]
Another variation could be for example

UPDATE Table1
SET D = (SELECT Table1.A - Table1.B - t2.C
         FROM Table2 t2
         WHERE Table1.ParentColumn = t2.ChildColumn)

Note that this must return only one value per each row in Table1 so if the relation is one-to-many you need to restrict the query or use aggregates such as SUM

这篇关于如何从多个表中减去两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！