Oracle 中translate和case when的用法

TRANSLATE(string,from_str,to_str)

上述格式的函数返回将from_string中的每个字符替换为to_string中的相应字符以后的string。

TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。

from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值。

CASE WHEN 表达式有两种形式：

1.--简单Case函数，功能比较简单

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END  

2.--Case搜索函数，功能更强大一些，可以进行条件判断

CASE
WHEN sex = '1' THEN '男'  
WHEN sex = '2' THEN '女'  
ELSE '其他' END  

项目中用到的举例：

        $sql = "    SELECT tb_A.STATISTICS_DATE,
                           tb_A.RESOURCEID,
                           tb_A.USERS,
                           tb_A.COUNTS,
                           NVL2(
                               translate(tb_A.resourceid, '\\1234567890 ', '\\'),
                               (select distinct t1.reit_name from jtinfo.tbl_fl_resourceiteminfo t1 where t1.reit_url=tb_A.resourceid),
                               (select distinct t2.a_title from jtinfo.tbl_fl_article t2 where to_char(t2.a_id)=tb_A.resourceid)
                           ) AS A_TITLE
                    FROM PDT_STAT_DETAIL_{$productid}_{$plat} tb_A
                    WHERE tb_A.STATISTICS_DATE>='$starttime' AND tb_A.STATISTICS_DATE<='$endtime'
                    ORDER BY tb_A.STATISTICS_DATE DESC
        ";

select (
       CASE WHEN translate(tb_A.resourceid, '\1234567890 ', '\') is NULL THEN tb_A.Statistics_Date
       ELSE tb_A.resourceid END)
FROM PDT_STAT_DETAIL_1156_a tb_A

注意：要保证then的结果是字符类型，而不能是number类型。

数据统计表中包含两种resourceid，一种是纯数字的elementid，一种是字母和数字组合的itemid，现在需要针对两种id分别联表取文章/资源的标题。原有的SQL仅仅针对elementid进行查询处理，所以现在需要对原有的SQL进行改造。

原来的SQL：

SELECT * FROM( 
    SELECT tb_A.*,tb_B.A_TITLE,TO_CHAR(tb_B.A_CREATEDATE,'yyyy-mm-dd') AS A_CREATEDATE 
    FROM( SELECT RESOURCEID,SUM(USERS) AS users,SUM(COUNTS) AS counts 
          FROM PDT_STAT_DETAIL_1156_a GROUP BY RESOURCEID )tb_A 
    LEFT JOIN jtinfo.tbl_fl_article tb_B 
    ON to_char(tb_A.RESOURCEID) = to_char(tb_B.A_ID) ) 
WHERE A_CREATEDATE>='2013-01-01' AND A_CREATEDATE<='2013-06-04' ORDER BY A_CREATEDATE DESC 

改造后的SQL：

SELECT * FROM
(
SELECT RESOURCEID,
       USERS,
       COUNTS, 
       (CASE WHEN translate(RESOURCEID, '\1234567890 ', '\') is NULL THEN A_TITLE
       ELSE REIT_NAME END) AS A_TITLE,
       (CASE WHEN translate(RESOURCEID, '\1234567890 ', '\') is NULL THEN A_CREATEDATE
       ELSE REIT_LATESTMODDATE END) AS A_CREATEDATE
FROM( 
       SELECT tb_A.RESOURCEID,tb_A.USERS,tb_A.COUNTS,tb_B.A_TITLE,TO_CHAR(tb_B.A_CREATEDATE,'yyyy-mm-dd') AS A_CREATEDATE,tb_C.REIT_NAME,TO_CHAR(tb_C.REIT_LATESTMODDATE,'yyyy-mm-dd') AS REIT_LATESTMODDATE
       FROM( SELECT RESOURCEID,SUM(USERS) AS users,SUM(COUNTS) AS counts 
             FROM PDT_STAT_DETAIL_1156_a GROUP BY RESOURCEID 
           )tb_A 
       LEFT JOIN jtinfo.tbl_fl_article tb_B 
       ON tb_A.RESOURCEID = to_char(tb_B.A_ID)
       LEFT JOIN jtinfo.tbl_fl_resourceiteminfo tb_C
       ON tb_C.REIT_URL = tb_A.RESOURCEID
    ) 
) 
WHERE A_CREATEDATE>='2013-01-01' AND A_CREATEDATE<='2013-06-04' ORDER BY A_CREATEDATE DESC 

说明：

统计数据表PDT_STAT_DETAIL_1156_a的RESOURCEID是varchar2类型，jtinfo.tbl_fl_article表中的A_ID是number类型。所以会用到to_char转换函数

PHP中写SQL中需要对\进行转义，要记得写成两个反斜杠。