如何从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 状态和写入查询则没有数据在下面的查询

<预类=郎-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屋！