decode有点类似java中的switch case. 只能针对具体的值(往往是一些类型字段的值)做判断.不针对condition做判断.
一个计算是否投放(isok)的例子.
DECODE (
b.istime,
1,
DECODE ( a.如果处于投放时间
b.isremain,
1,
DECODE ( b.如果帐户还有钱
a.check_status,
1,
DECODE ( c.如果广告的状态是已审核通过
a.ispause,
0,
DECODE (a.isoptimize, d.如果广告未暂停
0,
DECODE ( e.如果广告未优化
b.place,
3, f.如果达到日限额.
0, 返回0
1 否则返回1
),
0 e/否则为0
),
0 d/否则为0
),
0 c/否则为0
),
0 b/否则为0
),
0 a/否则返回0
)
AS isok,
又比如通过decode临时调整一些值的大小.比如type=6的搬家由于数据量较大,会影响到其它类型的上线.通过decode设置一些临时的值将type=6的类型放到了最后处理.
/* Formatted on 2011-8-3 16:02:55 (QP5 v5.114.809.3010) */
SELECT *
FROM ( SELECT p.ID,
p.AGENTID,
p.TYPE,
p.GRPID,
p.STEP,
p.ACCOUNTID,
a1.C_INDUSTRY AS INDUSID,
a1.C_COMPANY_NAME AS ACCOUNTNAME,
a2.C_COMPANY_NAME AS AGENTNAME,
a1.C_EMAIL AS ACCOUNTEMAIL
FROM (SELECT grpid, minid, ntype
FROM ( SELECT o.grpid,
MIN (o.id) AS minid,
DECODE (o.TYPE,
6, 16,
2, 1,
3, 1,
o.TYPE)
AS ntype
FROM cpc.cpcoperation o,
cpc.account ac,
cpc.bill bi
WHERE o.step = 0
AND o.TYPE IN
(1,
2,
3,
4,
5,
6,
7,
8,
9,
10)
AND o.accountid = ac.i_account_id
AND o.accountid = bi.i_account_id
AND ac.C_STATUS IN ('正常', '转换')
AND O.CREATEDATE <=
SYSDATE - 0.03 / 24
AND bi.I_REMAIN > 0
--AND MOD(o.grpid, 5) = {?}
GROUP BY o.grpid,
DECODE (o.TYPE,
6, 16,
2, 1,
3, 1,
o.TYPE)
ORDER BY DECODE (o.TYPE,
6, 16,
2, 1,
3, 1,
o.TYPE),
MIN (o.id))
WHERE ROWNUM <= 60) op,
(SELECT o.*,
DECODE (o.TYPE, 6, 16, 2, 1, 3, 1, o.TYPE)
AS ntype
FROM cpc.cpcoperation o
WHERE o.step = 0
AND o.TYPE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) p,
CPC.ACCOUNT a1,
CPC.ACCOUNT a2
WHERE op.grpid = p.grpid
AND op.ntype = p.ntype(+)
AND p.accountid = a1.i_account_id(+)
AND a1.i_agent_id = a2.i_account_id(+)
AND p.id IS NOT NULL
AND p.id = op.minid
AND (a1.C_STATUS = '正常' OR a1.C_STATUS = '转换')
ORDER BY op.ntype, op.minid, op.grpid)
WHERE ROWNUM <= 200
min,max函数的参数只有一个,一般是个列
greatest,leaest的参数可以是多个,比大小用这个.