用VB求ACCESS两个表的和a表里有
ID 名称 数量 总价
1 a 13 11
2 c 20 20
3 c 2 52
4 b 4 85
5 b 7 10
6 b 20 20
7 a 11 11
8 a 19 5
9 c 1 8
B表里有
id 名称 先付 再付
1 a 0 18
2 c 21 19
3 a 35 27
4 b 0 35
5 b 52 67
6 c 0 58
7 a 50
8 a 31
9 b 17 10
10 c 0 5
要怎么样写语句能实现下面的状态
在ListView1中显示ID 名称 先付总合 再付总和 数量总和 总价总合
1 a 35+50+31=116 18+27=45 13+11+19 11+11+5
2 b 0+52+17=69 35+67+10=112 4+7+20 85+10+20
3 c 21+0+0 19+58+5=82 20+2+1 20+52+8 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path + "\DATEBASE\ACCESSDATE.mdb;Persist Security Info=False"
sq = "select 名称,sum(先付) as 先付合计,sum(再付) as 再付合计 from 表B group by 名称"
这个语句该怎么写能实现上面的形式 rs.Open sq, cn, adOpenKeyset, adLockOptimistic
For i = ListView1.ListItems.Count To 1 Step -1 '每次查询之前先删除全部内容
ListView1.ListItems.Remove (i)
Next i
For i = 1 To rs.RecordCount
Set Fliv = ListView1.ListItems.Add()
Fliv.Text = rs.Fields("名称")
Fliv.SubItems(1) = Format(rs.Fields("先付合计"), "0.00")
Fliv.SubItems(2) = Format(rs.Fields("再付合计"), "0.00")
Fliv.SubItems(3) = Format(rs.Fields("总价合计"), "0.00")
Fliv.SubItems(4) = Format(rs.Fields("先付合计") + rs.Fields("再付合计") - rs.Fields("总价合计"), "0.00")
rs.MoveNext
Next i
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
ID 名称 数量 总价
1 a 13 11
2 c 20 20
3 c 2 52
4 b 4 85
5 b 7 10
6 b 20 20
7 a 11 11
8 a 19 5
9 c 1 8
B表里有
id 名称 先付 再付
1 a 0 18
2 c 21 19
3 a 35 27
4 b 0 35
5 b 52 67
6 c 0 58
7 a 50
8 a 31
9 b 17 10
10 c 0 5
要怎么样写语句能实现下面的状态
在ListView1中显示ID 名称 先付总合 再付总和 数量总和 总价总合
1 a 35+50+31=116 18+27=45 13+11+19 11+11+5
2 b 0+52+17=69 35+67+10=112 4+7+20 85+10+20
3 c 21+0+0 19+58+5=82 20+2+1 20+52+8 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path + "\DATEBASE\ACCESSDATE.mdb;Persist Security Info=False"
sq = "select 名称,sum(先付) as 先付合计,sum(再付) as 再付合计 from 表B group by 名称"
这个语句该怎么写能实现上面的形式 rs.Open sq, cn, adOpenKeyset, adLockOptimistic
For i = ListView1.ListItems.Count To 1 Step -1 '每次查询之前先删除全部内容
ListView1.ListItems.Remove (i)
Next i
For i = 1 To rs.RecordCount
Set Fliv = ListView1.ListItems.Add()
Fliv.Text = rs.Fields("名称")
Fliv.SubItems(1) = Format(rs.Fields("先付合计"), "0.00")
Fliv.SubItems(2) = Format(rs.Fields("再付合计"), "0.00")
Fliv.SubItems(3) = Format(rs.Fields("总价合计"), "0.00")
Fliv.SubItems(4) = Format(rs.Fields("先付合计") + rs.Fields("再付合计") - rs.Fields("总价合计"), "0.00")
rs.MoveNext
Next i
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
(select 名称,sum(数量) 数量,sum(总价) 合计 from a group by 名称) A LEFT OUTER JOIN
(select 名称,sum(先付) 先付,sum(再付) 再付 from b group by 名称) B ON A.名称=B.名称--考虑里面有NULL的值,好像ACCESS用是ISNULL()来判断
--如果是这样子的话 SUM(ISNULL(数量,0))