2007-04-09

SQL也能这样写

关键字: sql
根据一个表的数据update另一个表数据的SQL语句通常如下：
Mysql语法： 
update T1 inner join (select ID, TYPE, sum(AMOUNT)
as NEW_AMOUNT from T2 group by ID, TYPE) as T3
on T1.ID = T3.ID and T1.TYPE = T3.TYPE 
set T1.AMOUNT = T1.AMOUNT+T3.NEW_AMOUNT

SqlServer语法： 
update T1 set T1.AMOUNT = T1.AMONT+T3.NEW_AMOUNT
from T1 inner join (select ID, TYPE, sum(AMOUNT)
as NEW_AMOUNT from T2 group by ID, TYPE) as T3
on T1.ID = T3.ID and T1.TYPE = T3.TYPE


但不幸的是上面两种语法均不符合ANSI92的SQL标准，也不能在Hsql中使用。
难道ANSI92不支持这样常用的update功能吗？答案是否，使用如下的SQL语句
即可实现与上面写法相同的功能： 

update T1 set T1.AMOUNT = T1.AMOUNT+
(select sum(T2.AMOUNT) from T2 
group by T2.ID, T2.TYPE 
having T2.ID=T1.ID and T2.TYPE=T1.TYPE)


上述SQL在Hsql1.7.2和Mysql5.1下测试成功，测试数据如下： 

CREATE TABLE T1 (
ID varchar(255), 
TYPE varchar(255), 
AMOUNT decimal
);
CREATE TABLE T2 (
ID varchar(255), 
TYPE varchar(255), 
AMOUNT decimal
);


delete from T2;
delete from T1;

insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'P', 10);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'P', 20);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'P', 30);

insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'N', 10);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'N', 20);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'N', 30);

insert into T1 values('0000000001', 'P', 0);
insert into T1 values('0000000001', 'N', 0);
insert into T1 values('0000000002', 'P', 0);
insert into T1 values('0000000002', 'N', 0);
评论
发表评论

您还没有登录，请登录后发表评论

jxb8901
搜索本博客
最近加入圈子
存档
最新评论