如何在一个SQL数据表中将一列的总和减去另一列的总和 [英] How can I subtract from sum of one column to sum of another column in one SQL data table
问题描述
这是我的Sql数据表,请帮助我如何查看与Deepak和Abdul分开的余额金额,我是Vb.net的新手。请帮助我。
人名日期信用卡借方
迪帕克5/4/2017 5000.00 Null
迪帕克6/4/2017 Null 4500.00
迪帕克30/4/2017 2000.00 Null
Abdul 1/5/2017 3000.00 Null
Abdul 2 / 5月15日Null 2000.00
Abdul 4/5/2017 Null 5000.00
如果我将从窗口形式的文本框搜索Deepak然后平衡在手应该显示2500.00和
如果我将从窗口形式的文本框中搜索Abdul然后应该显示在手中的余额-4000.00
我尝试了什么:
Dim Cmd As New SqlClient.SqlCommand
Dim Con As New SqlClient .SqlConnection
Dim Rd As SqlDataReader
Con.ConnectionString =Data Source =(localdb)\ MSSQLLocalDB; Initial Catalog = dbase; Integrat ed Security = True; Pooling = False
Cmd.Connection = Con
Con.Open()
Cmd.CommandText =Select *来自Daybookcash,其中PersonName ='& Tbdbkname.Text& '
Rd = Cmd.ExecuteReader
Rd.Read()
如果Rd.HasRows那么
Tbamtcashih.Text = Rd.Item(Credit) - Rd.Item(借记)
结束如果
从不连接用于创建SQL查询的字符串 - 改为使用参数化查询。该代码应为
Cmd.CommandText = 选择*来自Daybookcash,其中PersonName = @tbdbkname
Cmd.Parameters.AddWithValue( @ tbdbkname ,Tbdbkname.Text)
也不要使用选择*从
- 列出列你想明确地选择 [PersonName],[日期],Credit,从...借记
获得余额的最简单方法可能是使用选择 [人名],总和(信用) - 总和(借方)作为余额来自 Daybookcash GROUP BY [PersonName]使用与上面相同的Where子句你需要。
另一种选择是以你的方式查看你所拥有的查询结果。问题是你的表上有null
数据,所以你可能会得到一个异常Quote:运算符' - '没有为'Decimal'类型定义并输入'DBNull'。
你可以 在使用之前检查空项目,如下所示:
Dim y As Decimal
如果 不 十进制 .TryParse (Rd.Item( 借记)。ToString(),y)然后
y = 0
结束 如果
Dim x As 十进制
如果 不 十进制 .TryParse(Rd.Item( Credit)。ToString(),x)然后
x = 0
结束 如果
Tbamtcashih.Text =(x - y).ToString()
或者保持计算的更多或更少,并更改查询以使空值不是返回,如下所示:
选择 [PersonName],[日期], ISNULL(Credit, 0 ) as Credit,ISNULL(借记, 0 ) as 借记来自...。别忘了使用上面的.ToString()方法。
这里要求的是一个完整的解决方案:使用 con 作为新的SqlConnection
con.ConnectionString = constring
con。 打开()
使用 cmd As New SqlCommand
cmd.Connection = con
cmd.CommandText = select [Personname] ,sum(Credit)为Credit,sum(Debit)为Daybookcash的借方,[PersonName] = @tbdbkname GROUP BY [PersonName]
cmd.Parameters.AddWithValue( @ tbdbkname, Deepak)
Dim rd As SqlDataReader = cmd.ExecuteReader
如果 rd.HasRows 那么
rd。读()
Dim y 作为 十进制
如果 不 十进制 .TryParse(rd。 Item( 借记)。ToString(),y)然后
y = 0
结束 If
Dim x As Decimal
如果 不 十进制 .TryParse(rd。 Item( Credit)。ToString(),x)然后
x = 0
结束 如果
Tbamtcashih。 Text =(x - y).ToString()
结束 如果
结束 使用
结束 使用
或者这种方式(我更喜欢)
使用 con 作为 新 SqlConnection
con.ConnectionString = constring
con.Open()
使用 cmd As 新 SqlCommand
cmd.Connection = con
cmd.CommandText = 选择[Personname],sum(Credit ) - 总和(借记)作为Daybookcash的余额,其中[PersonName] = @tbdbkname GROUP BY [PersonName]
cmd.Parameters.AddWithValue( @ tbdbkname, Deepak)
Dim rd As SqlDataReader = cmd.ExecuteReader
如果 rd.HasRows 那么
rd.Read()
Tbamtcashih.Text = rd.Item( Balance)。ToString()
结束 如果
结束 使用
结束 使用
Here is my Sql Data Table, Please assist how can i check Balance Amount In hand with Deepak and Abdul Separately, i am new in Vb.net. Please assist me.
Person Name Date Credit Debit
Deepak 5/4/2017 5000.00 Null
Deepak 6/4/2017 Null 4500.00
Deepak 30/4/2017 2000.00 Null
Abdul 1/5/2017 3000.00 Null
Abdul 2/5/2017 Null 2000.00
Abdul 4/5/2017 Null 5000.00
If I will Search Deepak from text Box in Window form Then Balance in Hand should be shown 2500.00 and
If i will Search for Abdul from text Box in Window form Then Balance In Hand should be shown -4000.00
What I have tried:
Dim Cmd As New SqlClient.SqlCommand
Dim Con As New SqlClient.SqlConnection
Dim Rd As SqlDataReader
Con.ConnectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=dbase;Integrated Security=True;Pooling=False"
Cmd.Connection = Con
Con.Open()
Cmd.CommandText = "Select * From Daybookcash Where PersonName = '" & Tbdbkname.Text & "'"
Rd = Cmd.ExecuteReader
Rd.Read()
If Rd.HasRows Then
Tbamtcashih.Text = Rd.Item("Credit") - Rd.Item("Debit")
End If
Never concatenate strings to create your SQL query - use parameterized queries instead. That code should be
Cmd.CommandText = "Select * From Daybookcash Where PersonName = @tbdbkname" Cmd.Parameters.AddWithValue("@tbdbkname",Tbdbkname.Text)
Also don't useSelect * From
- list the columns you want explicitlySelect [PersonName], [Date], Credit, Debit From ...
Probably the easiest way to get the balance is to query the database withselect [Personname], sum(Credit) - sum(Debit) as balance from Daybookcash GROUP BY [PersonName]Use the same Where clause as above if you need to.
The other option would be to look at the results of the query you have, in the way you are doing it. The trouble is you havenull
data on your table so you are probably getting an exceptionQuote:Operator '-' is not defined for type 'Decimal' and type 'DBNull'.
You can either check for null items before using them, like this:
Dim y As Decimal If Not Decimal.TryParse(Rd.Item("Debit").ToString(), y) Then y = 0 End If Dim x As Decimal If Not Decimal.TryParse(Rd.Item("Credit").ToString(), x) Then x = 0 End If Tbamtcashih.Text = (x - y).ToString()OR leave the calculation more or less as it is and change the query so that nulls are not returned, like this:
Select [PersonName], [Date], ISNULL(Credit,0) as Credit, ISNULL(Debit,0) as Debit From ....Don't forget to use the .ToString() method as above.
[EDIT]
As requested here is a full solution:Using con As New SqlConnection con.ConnectionString = constring con.Open() Using cmd As New SqlCommand cmd.Connection = con cmd.CommandText = "select [Personname], sum(Credit) as Credit, sum(Debit) as Debit from Daybookcash where [PersonName] = @tbdbkname GROUP BY [PersonName]" cmd.Parameters.AddWithValue("@tbdbkname", "Deepak") Dim rd As SqlDataReader = cmd.ExecuteReader If rd.HasRows Then rd.Read() Dim y As Decimal If Not Decimal.TryParse(rd.Item("Debit").ToString(), y) Then y = 0 End If Dim x As Decimal If Not Decimal.TryParse(rd.Item("Credit").ToString(), x) Then x = 0 End If Tbamtcashih.Text = (x - y).ToString() End If End Using End UsingOR this way (which I prefer)
Using con As New SqlConnection con.ConnectionString = constring con.Open() Using cmd As New SqlCommand cmd.Connection = con cmd.CommandText = "select [Personname], sum(Credit) - sum(Debit) as Balance from Daybookcash where [PersonName] = @tbdbkname GROUP BY [PersonName]" cmd.Parameters.AddWithValue("@tbdbkname", "Deepak") Dim rd As SqlDataReader = cmd.ExecuteReader If rd.HasRows Then rd.Read() Tbamtcashih.Text = rd.Item("Balance").ToString() End If End Using End Using
这篇关于如何在一个SQL数据表中将一列的总和减去另一列的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!