1> delete from a where exists (select null from b where b.id1=a.id1 and b.id2=a.id2)
?? delete from a where exists (select 1 from b where b.id1=a.id1 and b.id2=a.id2) 很多朋友喜欢这样写
??
2> delete a from a , b where a.id1 = b.id1 and b.id2=a.id2
?
3> delete from a left join b on a.id1 = b.id1 and a.id2 = b.id2
4> delete from a where (select count(*) from b where b.id1 = a.id1 and b.id2 = a.id2) > 0
5> delete from a where (a.id1,a.id2) in (select b.id1,b.id2) from b
6> delete from a where a.id1 in (select? b.id1 from b )? and a.id2? in (select? b.id2 from b)
5，6 效率低，不建议使用，其他还不错
Oracle 中start with -- connect by ;ORACLE中常用如下语句实现父子关联.SELECT ORG_CODE FROM TABLE1CONNECT BY PRIOR ORG_CODE = PARENT_CODE START WITH ORG_CODE = '21101010' ANDDEALER_ID = '21101010' 在DB2中可以用以下方式实现.with b(org_code,parent_code,1 name) as ( select org_code,parent_code from TABLE1 where ORG_CODE = '21101010' AND DEALER_ID = '21101010' union all select a.org_code,a.parent_code from TABLE1 as a,b where b.ORG_CODE = a.PARENT_CODE )select * from b谓词WHERE 可以在后面添加,比如......select * from b where parent_code='21101010'
?
?
?
?
77
DB2中sequence的创建与使用：?
?
? 1、创建sequence
?????? CREATE sequence my_seq AS INTEGER START WITH 628 increment BY 1
? 2、获取sequence的下一个值
?????? values nextval for my_seq(my_seq是sequence名称)
3、在sql中使用sequence的下一个值
?????? insert into table_name(id) values(nextval for my_seq)