完整代码：
Dim cmd1 As New SQLCommand
Dim dt1 As DataTable
cmd1.C
cmd1.CommandText = "Select sys_user, YEAR(购买_日期) As 年, sum(购买_母猪) As 购买_母猪, sum(购买_公猪) As 购买_公猪, sum(购买_肉猪) As 购买_肉猪, sum(饲料) As 饲料, sum(疫苗) As 疫苗, sum(药品) As 药品, sum(易耗品) As 易耗品, sum(保险费) As 保险费, sum(水电费) As 水电费, sum(工资费用) As 工资费用, sum(工程折旧费) As 工程折旧费, sum(其他支出) as 其他支出 f rom" & _
"(Select sys_user, 购买_日期, 购买_金额 As 购买_母猪, 0 As 购买_公猪, 0 As 购买_肉猪, 0 As 饲料, 0 As 疫苗, 0 As 药品, 0 As 易耗品, 0 As 保险费, 0 As 水电费, 0 As 工资费用, 0 As 工程折旧费, 0 as 其他支出 f rom {母猪基本信息} u nion all " & _
"Select sys_user, 购买_日期, 0, 购买_金额 As 购买_公猪, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 f rom {公猪信息} u nion all " & _
"Select sys_user, 购买_日期, 0, 0, 购买_金额 As 购买_肉猪, 0, 0, 0, 0, 0, 0, 0, 0, 0 f rom {肉猪信息} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 饲料量_总金额 as 饲料, 0, 0, 0, 0, 0, 0, 0, 0 f rom {饲料采购} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 总金额 as 疫苗, 0, 0, 0, 0, 0, 0, 0 f rom {疫苗采购} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 总金额 as 药品, 0, 0, 0, 0, 0, 0 f rom {药品采购} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 总金额 as 易耗品, 0, 0, 0, 0, 0 f rom {易耗品采购} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 0, 投保金额 as 保险费, 0, 0, 0, 0 f rom {保险费} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 0, 0, 金额 as 水电费, 0, 0, 0 f rom {水电费} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 0, 0, 0, 金额 as 工资费用, 0, 0 f rom {工资费用} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 支出金额 as 工程折旧费, 0 f rom {工程折旧费用} u nion all " & _
"Select sys_user, 日期, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 金额 as 其他支出 f rom {其他支出} ) " & _
"As a where [sys_user]='" & user.Name & "' group by year(购买_日期), sys_user order by year(购买_日期), sys_user "
dt1 = cmd1.ExecuteReader()
dt1.DataCols.Add("支出合计",Gettype(Integer), "IsNull([购买_母猪],0) + ISNULL([购买_公猪],0) + ISNULL([购买_肉猪],0) + ISNULL([饲料],0) + ISNULL([疫苗],0) + ISNULL([药品],0) + ISNULL([易耗品],0) + ISNULL([保险费],0) + ISNULL([水电费],0) + ISNULL([工资费用],0) + ISNULL([工程折旧费],0) + ISNULL([其他支出],0) ")
Dim cmd2 As New SQLCommand
Dim dt2 As DataTable
cmd2.C
cmd2.CommandText = "Select sys_user, 年, sum(母猪存栏数) As 母猪存栏数, sum(肉猪存栏数_年末) As 肉猪存栏数_年末, sum(肉猪存栏数_年初) As 肉猪存栏数_年初, sum(肉猪销售数) As 肉猪销售数 f rom " & _
"(Select sys_user, year(日期) As 年, avg(母猪_总) As 母猪存栏数, 0 As 肉猪存栏数_年末, 0 As 肉猪存栏数_年初, 0 As 肉猪销售数 f rom {母猪存栏结构} group by sys_user, year(日期) u nion all " & _
"Select sys_user, year(日期), 0, 合计, 0, 0 f rom{肉猪存栏结构} where month(日期) = 12 u nion all " & _
"Select sys_user, year(日期), 0, 0, 合计, 0 f rom{肉猪存栏结构} where month(日期) = 1 u nion all " & _
"Select sys_user, year(销售_日期), 0, 0, 0, count(销售_日期) f rom {肉猪信息} group by sys_user, year(销售_日期)) " & _
"As a where sys_user='" & user.Name & "' group by 年, sys_user"
dt2 = cmd2.ExecuteReader()
dt2.DataCols.Add("MSY",Gettype(Double), " (IsNull([肉猪存栏数_年末],0) - ISNULL([肉猪存栏数_年初],0) + ISNULL([肉猪销售数],0)) / IIF(IsNull([母猪存栏数],0) = 0, null, IsNull([母猪存栏数],0)) ")
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("sys_user", Gettype(String), 32)
dtb.AddDef("年", Gettype(String),10)
'dtb.AddDef("msy", Gettype(String),40)
dtb.AddDef("支出合计", Gettype(Double))
dtb.Build()
For Each nm() As String In dt1.GetValues("sys_user|年")
Dim dr As DataRow = DataTables("统计").AddNew()
dr("sys_user") = nm(0)
dr("年") = nm(1)
'dr("msy") = nm(2)
Dim dr2 As DataRow = dt2.Find("sys_user='" & nm(0) & "' and 年='" & nm(1) & "'")
If dr2 IsNot Nothing Then
dr("支出合计") = dr2("支出合计")
End If
Next
MainTable= Tables("统计")
加好友 
发短信
Post By：2015/8/29 20:54:00 [显示全部帖子]
