DB2常用SQL示例,DB2 with的使用，DB2十六进制数据的使用

luzl

下面是一些SQL的示例:
inner join/like
select count(*)  from tab1.s_employee a inner join inst1.s_emp_postn b on a.row_id = b.emp_id where not login like 'NO-LOGIN%'

大于，小于
select count(*)  from inst1.s_employee ainner join inst1.s_emp_postn b on a.row_id = b.emp_id inner join inst1.s_postn c on b.position_id = c.row_idwhere c.created > '2002-03-12-00.00.01.000000' and c.created < '2002-03-17-23.59.59.999999'and not a.login like 'NO-LOGIN%'

value的示例，如果c.yes为空则显示'NO'
select distinct a.login KERBEROS,e.name POSITION ,value(c.yes,'NO') WITH_COVERAGE from inst1.s_employee ainner join inst1.s_emp_postn b on a.row_id = b.emp_idinner join inst1.s_postn e on b.position_id = e.row_id left outer join (select 'YES' yes, position_id from inst1.s_accnt_postn) c on e.row_id = c.position_id where not a.login like 'NO-LOGIN%'

Left join,左全关联，左边的都显示出来，如果关联不到右边的显示为null
    select       value(z.KERBEROS,Y.KERBEROS) MY_KERBEROS,       value(Z.FIRST_NAME,Y.FIRST_NAME) MY_FIRST_NAME,        value(Z.LAST_NAME,Y.LAST_NAME) MY_LAST_NAME,        value(Z.REP_ID,Y.REP_ID) MY_REP_ID,        value(Z.DESK_NAME,Y.DESK_NAME) MY_DESK_NAME     from      (         select          a.login KERBEROS,           a.FST_NAME FIRST_NAME,           a.Last_Name LAST_NAME,           a.PAGER_PIN REP_ID,           c.X_POSITION_NAME DESK_NAME         from          inst1.s_employee a, inst1.s_postn b, inst1.s_emp_postn d, inst1.s_postn c         where          a.row_id = d.emp_id  and d.position_id = b.row_id and b.PAR_POSTN_ID = c.row_id  and  a.pager_pin is not null       ) y      left outer join      (         select          a.login KERBEROS,           a.FST_NAME FIRST_NAME,          a.Last_Name LAST_NAME,          a.PAGER_PIN REP_ID,           c.X_POSITION_NAME DESK_NAME        from          inst1.s_employee a, inst1.s_postn b, inst1.s_postn c         where          a.PR_POSTN_ID = b.row_id and b.PAR_POSTN_ID = c.row_id and a.pager_pin is not null       ) z     on y.KERBEROS = z.KERBEROS  where value(z.KERBEROS,Y.KERBEROS) not in ('something1','something2')   ORDER BY MY_REP_ID

这几个很简单就不解释了
select col1 from tab1 group by col1 having count(*) > 1select * from tab1 where lcase(col1) = 'o''brien'Some (non)-trivial examples:In Sybase we frequently use temp. tables and stored procedure when we need to get a result from many tables.For example,

DB2 中的临时表可以一个SQL表示(当然这只是一种,你可以创建session的临时表)：
with query1 ( col1, col2, col3, ..., colN) as ( select ... ),       query2 (col, ....) as (select .. from query1 left outer join ... on ... ),       query3 (col, ....) as (select .. from query2 left outer join ... on ... ),       query4 (col, ....) as (select .. from query3 left outer join ... on ... )select  ..... from query4 where ...---------------------------- one more query using the 'with' constructwith  myKid (kid) as (    select user_id from ... where ...    union    select kid from ... where ...  ),  myAccountIDs (AccountId) as (    select distinct AccountId from myKid t, ... where ...  ),  mySomething (col1, col2, ...) as (    select ... from ... where ...  )select ... from ... where ... ---------------------------- return 1 if something exists, otherwise return 0with t1 (is_mgr) as (  select 1 is_mgr from table (values 1) as t2  where exists (select 1 from ... where ...)  union  select 0 is_mgr from table (values 0) as t2)select * from t1 order by is_mgr desc fetch first 1 rows only
----------------------------

select distinct ... , cast(NULL as char) as first_name, cast(NULL as char) as last_namefrom tab1, table (select ... from ... where ... ) as Twhere ... = cast(substr(T.user_id,1,locate('_', T.user_id) -1_ as int)----------------------------select col1 from tab1 group by col1 having count(*) > 1select * from tab1 where lcase(col1) = 'o''brien'

----------------------------

获取排名前十行:

select name, salary from employee awhere 10 > (select count(*) from employee b where a.salary < b.salary)order by salary descselect name, salary from employee a order by salary desc fetch first 10 rows only

----------------------------

一个表自己跟自己关联，抽取满足一定条件的数据，下面是抽取童年同月同日生的员工，并且第一列的员工比第三列的员工员工号大:

select a.name, a.birthday, b.namefrom employee a, employee bwhere a.birthday = b.birthdayand a.emp_no > b.emp_no



用16进制工作，db2中16进制数据的使用和操作:

values( hex(1024) )values ( X'3233' )values ( cast(X'3233' as integer) + 2 )values ( cast(X'3233' as char(2)) || ' - twenty three' )values ( X'4672616E6B' )-- Frankvalues ( X'30' )-- 0values ( X'30313233' )-- 0123-- now let's get names with a digit 8 in them:select name from siebel.s_org_ext where name like '%' || X'38' || '%'fetch first 10 rows only