记录几个sql

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 TABLE1 CONNECT BY PRIOR ORG_CODE = PARENT_CODE START WITH ORG_CODE = '21101010' AND DEALER_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 )