如何从SQL 2表计算? [英] How to Calculate From 2 Tables in SQL?
问题描述
我有以下2个表:
<预类=郎无prettyprint-覆盖>
A(ID,1号,NO2,D )
b(ID,NO3,4号,D)
和表中的数据如下:
一个TABEL数据
<预类=郎无prettyprint-覆盖>
ID = 1,1号= 23,NO 2 = 45,D = 1
的ID = 2,1号= 12,NO 2 = 5,D = 1
的ID = 3时,1号= 14,NO 2 = 4,D = 1
ID = 4,1号= 41,NO2 = 12,D = 1
ID = 5,1号= 2,NO 2 = 25,D = 1
ID = 6,1号= 3,NO2 = 96,D = 1
乙表的数据没有任何数据
如果我通过下面的SQL查询
<预类=郎-SQL prettyprint-覆盖>
SELECT
ISNULL(SUM(dbo.A.No1),0)AS No1Sum,
ISNULL(SUM(dbo.B.No3),0)AS No3Sum,
ISNULL(SUM(dbo.A。 1号),0) - ISNULL(SUM(dbo.B.No3),0)AS探源
FROM dbo.A LEFT
OUTER JOIN dbo.B ON dbo.A.id = dbo.B. ID
GROUP BY dbo.B.No3
那么下面的数据是未来
<预类=郎无prettyprint-覆盖>
No1Sum = 95,No3Sum = 0,探源= 95
不过,当我加入 WHERE
状态和写入查询则没有数据在下面的查询到来p>
<预类=郎-SQL prettyprint-覆盖> SELECT
ISNULL(SUM(dbo.A.No1),0)AS No1Sum,
ISNULL(SUM(dbo.B.No3),0)AS No3Sum,
ISNULL(SUM(dbo.A.No1),0) - ISNULL(SUM(dbo.B.No3),0 )AS探源
FROM dbo.A
LEFT OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id
WHERE(dbo.AD = 1)
和(dbo.BD = 1)GROUP BY dbo.B.No3
如果数据将precent两个表中那么结果将会显示例如:
<预类=郎无prettyprint-覆盖>
No1Sum = 95,No3Sum = 10,探源= 85,
如果我给下面的查询
<预类=郎-SQL prettyprint-覆盖>
SELECT
ISNULL(SUM(dbo.A.No1),0)AS No1Sum,
ISNULL(SUM(dbo.B.No3) 0)作为No3Sum,
ISNULL(SUM(dbo.A.No1),0) - ISNULL(SUM(dbo.B.No3),0)AS探源
FROM dbo.A
LEFT OUTER JOIN dbo.B
关于dbo.A.id = dbo.B.id
和dbo.AD = 1
和dbo.BD = 1
GROUP BY DBO。 B.No3
那么下面的结果显示
<前类=郎无prettyprint-覆盖>
No1Sum = 570,No3Sum = 0,探源= 570
(它总结6次为一个表有6行)
因此,如何从两个表中,当两个表中的数据结果计算是准确的,但是,当一个表有数据和b没有数据那么结果是事先不来了。
感谢
这查询不工作的原因是因为它只有返回记录,其中dbo.bd为1。但在查询它由空有时会。
SELECT
ISNULL(SUM(dbo.A.No1),0)AS No1Sum,
ISNULL(SUM(dbo.B .No3),0)AS No3Sum,
ISNULL(SUM(dbo.A.No1),0) - ISNULL(SUM(dbo.B.No3),0)AS探源
起价
dbo.A LEFT OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id
,其中
(dbo.AD = 1)和(dbo.BD = 1)GROUP BY DBO。 B.No3
所以,改变这一行
(dbo.AD = 1)和(dbo.BD = 1)GROUP BY dbo.B.No3
要这个
(dbo.AD = 1)和(ISNULL(DBO。 BD,1)= 1)GROUP BY dbo.B.No3
ISNULL(阿根廷,默认值)为设置为精氨酸如果arg不为空,否则将其设置为默认值。
I have the following 2 tables:
A (ID, No1, No2,D)
B (ID, No3, No4,D)
And table data is following:
A Tabel Data
ID = 1, No1 = 23, No2 = 45, D = 1
ID = 2, No1 = 12, No2 = 5, D = 1
ID = 3, No1 = 14, No2 = 4, D = 1
ID = 4, No1 = 41, No2 = 12, D = 1
ID = 5, No1 = 2, No2 = 25, D = 1
ID = 6, No1 = 3, No2 = 96, D = 1
B Table Data have not any Data
If I Pass Following SQL query
SELECT
ISNULL(SUM(dbo.A.No1), 0) AS No1Sum,
ISNULL(SUM(dbo.B.No3), 0) AS No3Sum,
ISNULL(SUM(dbo.A.No1), 0) - ISNULL(SUM(dbo.B.No3), 0) AS Diffrence
FROM dbo.A LEFT
OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id
GROUP BY dbo.B.No3
then following Data is coming
No1Sum = 95, No3Sum = 0, Diffrence = 95
But When I Add WHERE
condition and write query then no data is coming in the following query
SELECT
ISNULL(SUM(dbo.A.No1), 0) AS No1Sum,
ISNULL(SUM(dbo.B.No3), 0) AS No3Sum,
ISNULL(SUM(dbo.A.No1), 0) - ISNULL(SUM(dbo.B.No3), 0) AS Diffrence
FROM dbo.A
LEFT OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id
WHERE (dbo.A.D = 1)
AND (dbo.B.D = 1) GROUP BY dbo.B.No3
If data will precent in both tables then Result will show for example
No1Sum = 95, No3Sum = 10, Diffrence = 85,
If I give following query
SELECT
ISNULL(SUM(dbo.A.No1), 0) AS No1Sum,
ISNULL(SUM(dbo.B.No3), 0) AS No3Sum,
ISNULL(SUM(dbo.A.No1), 0) - ISNULL(SUM(dbo.B.No3), 0) AS Diffrence
FROM dbo.A
LEFT OUTER JOIN dbo.B
ON dbo.A.id = dbo.B.id
AND dbo.A.D = 1
AND dbo.B.D = 1
GROUP BY dbo.B.No3
then Following Result is Showing
No1Sum = 570, No3Sum = 0, Diffrence = 570
(it is summing 6 times as A Tables has 6 Rows)
So how to calculate from both tables, when data in both tables result is accurate, but when A Table has data and B has no data then the result is not coming.
Thanks in Advance
The reason this query does not work is because it only returns records where dbo.b.d is 1. But in your query it can sometimes by null.
SELECT
ISNULL(SUM(dbo.A.No1), 0) AS No1Sum,
ISNULL(SUM(dbo.B.No3), 0) AS No3Sum,
ISNULL(SUM(dbo.A.No1), 0) - ISNULL(SUM(dbo.B.No3), 0) AS Diffrence
FROM
dbo.A LEFT OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id
WHERE
(dbo.A.D = 1) AND (dbo.B.D = 1) GROUP BY dbo.B.No3
So change this line
(dbo.A.D = 1) AND (dbo.B.D = 1) GROUP BY dbo.B.No3
To this
(dbo.A.D = 1) AND (isnull(dbo.B.D, 1) = 1) GROUP BY dbo.B.No3
isnull(arg, default) is set to arg if arg is not null otherwise it sets it to default.
这篇关于如何从SQL 2表计算?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!