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

下面是一些SQL的示例:
[b]inner join/like[/b]

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 a
inner 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_id
where 
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 a
inner join inst1.s_emp_postn b on a.row_id = b.emp_id
inner 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(*) > 1

select * 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' construct

with
  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 0

with 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_name
from tab1, table (select ... from ... where ... ) as T
where ... = cast(substr(T.user_id,1,locate('_', T.user_id) -1_ as int)

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

select col1 from tab1 group by col1 having count(*) > 1

select * from tab1 where lcase(col1) = 'o''brien'

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

获取排名前十行:

select name, salary from employee a
where 10 > (select count(*) from employee b where a.salary < b.salary)
order by salary desc

select name, salary from employee a order by salary desc fetch first 10 rows only

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

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

select a.name, a.birthday, b.name
from employee a, employee b
where a.birthday = b.birthday
and 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' )
-- Frank
values ( X'30' )
-- 0
values ( 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