使用SQL在数据库的4个不同表中的4列上执行数学运算 [英] Performing mathematical operations on 4 columns in 4 different tables in a database using SQL

问题描述

这是我第一次使用SQL，我很难弄清楚如何执行此查询的语法.我在数据库中有5个表(z，a1，b1，a2，b2)，每个表都有一个名为qty的列(表z除外).我需要选择以下内容:

z.ID，z.Name，z.Name2，z.Cost，(a1.qty + b1.qty)-(a2.qty + b2.qty)作为数量

我可以叫z.ID，z.Name，z.Name2，z.Cost没问题，但是我在其余的表达式上遇到了困难.

这就是我所拥有的，我100％肯定其错误:

This is my first time using SQL and I''m having a hard time figuring out the syntax on how to perform this query. I have 5 tables in a database (z, a1, b1, a2, b2) and each table has a column named qty (except table z). I need to be able to select the following:

z.ID, z.Name, z.Name2, z.Cost, (a1.qty + b1.qty )-(a2.qty + b2.qty) as quantity

I can call z.ID, z.Name, z.Name2, z.Cost no problem, but I''m having difficulty with the rest of the expressions.

This is what I have and I am 100% positive its wrong:

<br />
select<br />
	z.ID,<br />
	z.Name,<br />
	z.Name2,<br />
	z.Cost,<br />
	(a1.qty + b1.qty) - (a2.qty + b2.qty) Quantity<br />
from<br />
	z inner join<br />
		a1 on z.ID = a1.z_ID inner join<br />
		a2 on z.ID = a2.z_ID inner join<br />
		b1 on z.ID = b1.z_ID inner join<br />
		b2 ON z.ID = b2.z_ID<br />

我已经在整个Google上进行了搜索，但仍然无法弄清楚.在这方面的任何帮助将非常有帮助！预先谢谢您.

I''ve searched all over google, but I still can''t figure it out. Any help on this will be extremely helpful! Thank you in advance.

推荐答案

请参阅此处以了解联接的工作原理.
http://msdn.microsoft.com/en-us/library/aa213227 (v = sql.80).aspx [

See this to know how join works.
http://msdn.microsoft.com/en-us/library/aa213227(v=sql.80).aspx[^]

Use this in your case

SELECT z.ID, z.Name, z.Name2, z.Cost, (a1.qty + b1.qty )-(a2.qty + b2.qty) as Quantity
FROM z, a1, a2, b1, b2
WHERE
           z.ID = a1.z_ID
           AND z.ID = a2.z_ID
           AND z.ID = b1.z_ID
           AND z.ID = b2.z_ID

在我进行了一些研究之后，我能够弄清楚这一点一整夜，直到清晨.我使用子查询和联合来实现这一目标.这是我编写的代码

Hi i was able to figure this out after i did some research all night and into the early morning. i used subqueries and unions to pull this off. this is the code i made

SELECT Z_ID, Z.Name, Z.Name2, cast(Z.UnitPrice as numeric(13,2)) as UnitPrice, abs(SUM(IL)) AS Qty, cast(UnitPrice * abs(SUM(IL)) as numeric(13,2)) as TotalAmount
FROM
(
    SELECT A1.Z_ID, sum(A1.QTY) as IL
    FROM A1
    GROUP BY A1.Z_ID
    UNION
    SELECT B1.Z_ID, sum(B1.QTY) as PL
    FROM B1
    GROUP BY B1.Z_ID
    union
    SELECT B2.Z_ID, sum(B2.QTY) * -1 as SL
    FROM B2
    GROUP BY B2.Z_ID
    union
    SELECT A1.Z_ID, SUM(A1.QTY)as ISL
    FROM A1
    GROUP BY A1.Z_ID
) MergedTable, Z
WHERE MergedTable.Z_ID = Z.ID
GROUP BY Z_ID, Z.Name, Z.Name2, Z.UnitPrice

谢谢大家的帮助！还要感谢Prera​​k Patel的有用链接！我会将其保存在工具箱中，以备后用.

thank you everyone who helped! Thank you as well Prerak Patel for the useful link! I''ll keep this in my toolbox for later use.

这篇关于使用SQL在数据库的4个不同表中的4列上执行数学运算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！