这样的SQL该怎么写？ ？

[加嘞个油]

--database : MySQL
--version  : 5.0.77

--TEMPORARY table temp
create TEMPORARY table temp(
id int(11) not null auto_increment primary key, 
logNo varchar(32), 
qid varchar(32),
brokerage decimal(16, 2), 
balanceAmount decimal(16, 2) 
);

-- temp record
insert into temp (qid, logNo, brokerage, balanceAmount) values ('A1','B1','100','100');
insert into temp (qid, logNo, brokerage, balanceAmount) values ('A1','B1','200','200');


--table PayStream
create table PayStream(
id int(11) not null auto_increment primary key, 
logNo varchar(32), 
qid varchar(32),
brokerage decimal(16, 2), 
balanceAmount decimal(16, 2) 
);

-- PayStream record
insert into PayStream(qid, logNo, brokerage, balanceAmount) values ('A1','B1','0.00','0.00');
insert into PayStream(qid, logNo, brokerage, balanceAmount) values ('A2','B2','0.00','0.00');
insert into PayStream(qid, logNo, brokerage, balanceAmount) values ('A3','B3','0.00','0.00');


--需要得到的结果，PayStream：

+----+-------+-----+-----------+---------------+
| id | qid  | logNo| brokerage | balanceAmount |
+----+-------+-----+-----------+---------------+
|  1 | A1    | B1  | 200.00    | 200.00        |
|  2 | A2    | B2  | 0.00      | 0.00          |
|  3 | A3    | B3  | 0.00      | 0.00          |
+----+-------+-----+-----------+---------------+

我写的SQL结果正确，但是把表的每行都update了一遍，应该是不对的，怎么用一条SQL，且加上过滤条件，只影响符合条件的第一行，我写的语句：

update PayStream p set brokerage = (select sum(brokerage) from temp t where t.logNo = p.logNo and t.qid = p.qid );


update PayStream p set balanceAmount = (select sum(balanceAmount) from temp t where t.logNo = p.logNo and t.qid = p.qid );

[liuxum]

你这结果是要用temp表中id最大的那条符合条件的数据？

[jielovehuan]

试试这样写： update PayStream a inner join (select logNo,qid,sum(brokerage) brokerage from temp group by select logNo,qid) b on a.logNo = b.logNo and a.qid = b.qid set a.brokerage = b.brokerage

[加嘞个油]

引用 2 楼 jielovehuan 的回复:

试试这样写： update PayStream a inner join (select logNo,qid,sum(brokerage) brokerage from temp group by select logNo,qid) b on a.logNo = b.logNo and a.qid = b.qid set a.brokerage = b.brokerage

谢谢。验证OK。不过你写的时候可能手误了 group by select logNo,qid 应该没有这个select

[jielovehuan]

引用 3 楼 z7452678 的回复:

[quote=引用 2 楼 jielovehuan 的回复:] 试试这样写： update PayStream a inner join (select logNo,qid,sum(brokerage) brokerage from temp group by select logNo,qid) b on a.logNo = b.logNo and a.qid = b.qid set a.brokerage = b.brokerage

谢谢。验证OK。不过你写的时候可能手误了 group by select logNo,qid 应该没有这个select[/quote] 嗯，应该是复制粘贴的时候不小心粘错了。