这个SQL语句怎么写阿？？？？ 80分求购

[chenfeng3000]

各位老大：这个SQL语句怎么写阿？？？？
数据表： MYtable1
Field1, Field2, Field3, Field4 Field5 Field6
chen 1 23 34 67
chen 0 45 34 33
feng 1 35 22 33
feng 0 34 34 234
zhong 1 32 22 11
zhong 0 10 12 13

Field2 1:男 0：女
问题：
如何将chen 中的（1，0）男女两组数据中的 field5中的数值累加到 field6中呢？？？
写一个这样的sql语句，如何能够得到如下的结果呢？？？？？
Field1, Field2, Field3, Field4 Field5 Field6
chen 1 23 34 67 100
chen 0 45 34 33 100
feng 1 35 22 33 264
feng 0 34 34 234 264
zhong 1 32 22 11 24
zhong 0 10 12 13 24
我很急阿，在线等候您的答复

[chenfeng3000]

怎么没有人答呢？？？？

[ll7777]

update table1 set Field6=(select sum(t.Field5) from table1 t where t.Field1=table1.Field1 group by t.Field1)

[chenfeng3000]

孤魂兄，我现在就下去调试，好用，即给分

[wdsimon]

update MYtable1 set Field6=(select sum(t.Field5) from MYtable1 t where t.Field1=MYtable1.Field1 group by t.Field1)

[yzf111]

select Field1,sum(Field5) as field6 from MYtable1 group by field1

[chenfeng3000]

好象，不好用啊，还有么？？？？

[wdsimon]

什么不好用，好要复杂的么？
建立测试表，加入测试数据；
create table MYtable1(Field1 char(10),Field2 char(1),Field3 int,Field4 int,Field5 int,Field6 int)
insert into MYtable1 values('chen','1',23,34,67,0)
insert into MYtable1 values('chen','0',45,34,33,0)
insert into MYtable1 values('feng','1',35,22,33,0)
insert into MYtable1 values('feng','0',34,34,234,0)
insert into MYtable1 values('zhong','1',32,22,11,0)
insert into MYtable1 values('zhong','0',10,12,13,0)
执行更新语句
update MYtable1 set Field6=(select sum(t.Field5) from MYtable1 t where t.Field1=MYtable1.Field1 group by t.Field1)
执行查询
select * from MYtable1
结果集如下：
Field1 Field2 Field3, Field4 Field5 Field6
chen 1 23 34 67 100
chen 0 45 34 33 100
feng 1 35 22 33 267
feng 0 34 34 234 267
zhong 1 32 22 11 24
zhong 0 10 12 13 24

[chenfeng3000]

wdsimon的sql 我用vb6调试了一下
系统报错 ：
实时错误：操作必须使用一个可更新的查询

[chenfeng3000]

update MYtable1 set Field6=(select sum(t.Field5) from MYtable1 t where t.Field1=MYtable1.Field1 group by t.Field1)
是delphi 论坛朋友提供的，为什么vb就不行呢，熟悉vb数据库的朋友帮我啊！！！

[wdsimon]

试试这个：
select A.Field1,A.Field2,A.Field3,A.Field4,A.Field5,B.Fiedl6
from MYtable1 A,
(select Field1,sum(Field5) as Fiedl6 from MYtable1 group by Field1) B
where A.Field1=B.Fiedl1

[wdsimon]

拷，我说怎么一个三角了，楼主你移过来了吗？

[jilate]

select Field1,sum(Field5)field5 into #t from MYtable1 group by Field1
update t1 set field6=t2.field5 from mytable1 t1,#t t2 where t1.field1=t2.field1
drop #t

[wfwater]

mark

[yoki]

strsql="update MYtable1 set Field6=t.field5" _
& " from (select field1,sum(Field5) as field5 from MYtable1 group by field1)t" _
& " where t.Field1=MYtable1.Field1 "
cn.execute strsql

[sword281]

我说的有点问题
现摘录一段原文"某些数据库为标准update格式提供了一个扩展格式,比如sql server的transact-sql语言,它使程序设计员能修改一个表的内容,该表内容基于其它一些使用了from子句的表的内容,扩展的格式如下:
update table_name set columnname1=value1[,columname2=value2] from table_list
where search_condition,估计你用的不是sqlserver数据库,

[sword281]

标准update语句的目的是改变记录中现有的值

[sword281]

好象在update 中不能接 from 子句,

[sword281]

update 更新的是值

[sword281]

给你一个例子
dim cn as adodb.connection
dim rs as adodb.recordset
Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim Orignal As New ADODB.Recordset
Set Orignal = New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbname;Mode=ReadWrite|Share Deny None;Persist Security Info=False"
Dim a As String
a = "select field1,sum(mytable.field5) as amount from mytable group by mytable.field1"
Set rs = cn.Execute(a, 0, adCmdText)
Orignal.Open "mytable", cn, , adLockPessimistic, adCmdTable
Dim i As Integer
Dim j As Integer
rs.MoveFirst
For j = 1 To rs.RecordCount
Orignal.MoveFirst
For i = 1 To Orignal.RecordCount
If rs.Fields("field1").Value = Orignal.Fields("field1").Value Then
Orignal.Fields("field6").Value = rs.Fields("amoount").Value
End If
Orignal.MoveNext
Next i
rs.MoveNext
Next j
End Sub
能完成你的要求

[greatqn]

学习。
1.用group by t1.field1 生成 临时表 t2 ,字段为 field1,field6 = sum(t1.field5)
2.再用 update 把 t1.field6=t2.field6 条件 t1.field1=t2.field1

看不懂？是我表达不清。