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

akyou56

下面是一些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 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