如何从SQL中的2个表计算?
我有以下2个表格:
A (ID, No1, No2,D) B (ID, No3, No4,D)
表数据如下:
表格数据
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表数据没有任何数据
如果我通过以下SQL查询
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
随后数据即将到来
No1Sum = 95, No3Sum = 0, Diffrence = 95
但是当我添加WHERE
条件并写入查询时,以下查询中没有数据
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.AD = 1) AND (dbo.BD = 1) GROUP BY dbo.B.No3
如果数据将在两个表中都显示,那么结果将显示为例如
No1Sum = 95, No3Sum = 10, Diffrence = 85,
如果我给出以下查询
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.AD = 1 AND dbo.BD = 1 GROUP BY dbo.B.No3
然后关注结果显示
No1Sum = 570, No3Sum = 0, Diffrence = 570
(由于A表有6行,它总计6次)
那么如何从两个表中计算出两个表中的数据结果是否准确,但是当A表有数据且B没有数据时,结果就不会到来。
提前致谢
此查询不起作用的原因是因为它只返回dbo.bd为1的记录。但在查询中,它有时可以为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.AD = 1) AND (dbo.BD = 1) GROUP BY dbo.B.No3
所以改变这一行
(dbo.AD = 1) AND (dbo.BD = 1) GROUP BY dbo.B.No3
对此
(dbo.AD = 1) AND (isnull(dbo.BD, 1) = 1) GROUP BY dbo.B.No3
如果arg不为null,则将isnull(arg,default)设置为arg,否则将其设置为default。
我认为这样做,即使Second表有数据。
SELECT ISNULL(SUM(A.No1), 0) AS No1Sum , ISNULL(SUM(B.No3), 0) AS No3Sum , ISNULL(SUM(A.No1), 0) - ISNULL(SUM(B.No3), 0) AS Diffrence FROM A LEFT OUTER JOIN B ON A.id = B.id
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.AD = 1) AND (dbo.BD = 1) GROUP BY dbo.B.No3
如果表B
没有任何数据,则此查询将不会获取任何记录,因为这种情况。
WHERE (dbo.AD = 1) AND (dbo.BD = 1)
我认为在连接上应用多个条件是开销。
上述就是C#学习教程:如何从SQL中的2个表计算?分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—计算机技术网(www.ctvol.com)!
LEFT OUTER JOIN dbo.B ON dbo.A.id = dbo.B.id AND dbo.AD = 1 --Overhead AND dbo.BD = 1 --Overhead
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/cdevelopment/1025274.html