Dim cn As New SqlConnection
cn.ConnectionString = "server=(local);database=Northwind;integrated security=sspi"
cn.Open()
Dim da As New SqlDataAdapter("select jiaocai.课本名称 as 课本名称,jiaocai.进库数量-table12.领取数量 as 剩余数量 from (select 课本名称,sum(进库数量) as 数量 from jiaocai group by 课本ID)jiaocai inner join (select 书名,sum(领取数量) as 数量 from table12 group by 课本ID) table12 on jiaocai.课本ID =table12.课本ID", cn)
ds = New DataSet
da.Fill(ds)
dt = ds.Tables(0)
DataGrid1.DataSource = dt
哪里错了
运行时提示“ da.Fill(ds)”错误
更正一下上面的:
SELECT C.货物名,SUM(C.数量) AS 数量 FROM (
SELECT 货物名,到货数量 AS 数量 FROM A
UNION
SELECT -货物名,销售数量 AS 数量 FROM B
) C
GROUP BY C.货物名
或者
select a.货物名 as 货物名,a.数量-b.数量 as 剩余数量 from (select id ,货物名,sum(数量) as 数量 from a group by 货物名)a inner join (select id ,货物名,sum(数量) as 数量 from b group by 货物名) b on a.id=b.id
不好意思,更正下:
select a.货物名 as 货物名,a.数量-b.数量 as 剩余数量 from (select id ,货物名,sum(数量) as 数量 from a group by 货物名,id)a inner join (select id ,货物名,sum(数量) as 数量 from b group by 货物名,id) b on a.id=b.id
于是你的应该改为:
select jiaocai.课本名称 as 课本名称,jiaocai.进库数量-table12.领取数量 as 剩余数量 from (select 课本id,课本名称,sum(进库数量) as 数量 from jiaocai group by 课本ID,课本名称)jiaocai inner join (select 课本id,sum(领取数量) as 数量 from table12 group by 课本ID) table12 on jiaocai.课本ID =table12.课本ID