http://www.th7.cn/db/mysql/201712/260897.shtml

--若转载，请标明出处
mysql 关键字做字段名
将关键字改成 `关键字` 就可以，如 count 改成`count` （注意是反引号` 而 不是

' 单引号）
若sql文无要求，直接修改关键字 如 count 改成 mcount

1、时间
日期和字符相互转换方法

DB2的函数： to_char(TWR.REPAIR_DATE,'yyyy-mm-dd')
mysql的函数：date_format(TWR.REPAIR_DATE,'%Y-%m-%d')

DB2的函数： to_date('"+exportDate+"23:59:59','YYYY-MM-DD HH24:MI:SS')
mysql的函数： str_to_date('"+exportDate+"23:59:59','%Y-%m-%d %T')

获取当前日期：
DB2的函数： select current date from sysibm.sysdummy1;
mysql的函数： select now();

获取当前年份
DB2的函数：
select current timestamp from sysibm.sysdummy1;
values year(current timestamp);
mysql的函数：SELECT DATE_FORMAT(NOW(), '%Y') FROM DUAL ;

计算两个日期之间的天数
DB2的函数：
select current date from sysibm.sysdummy1;
values days(current date)- days(date('2010-02-20'));
mysql的函数：
select to_days(now()) - to_days('20120512')
select datediff(now(),'20120512')
select timestampdiff(day,'2011-09-30','2015-05-04');

日期增加n个月
DB2的函数：add_months(date,n)
mysql的函数：date_add(date,interval n month)

2、DECODE函数
该数值与后面的一系列的偶序相比较，以决定返回值。
DB2的函数：
DECODE(TWR.IS_CLAIM,'10041001','索赔','未索赔')
mysql的函数：
CASE WHEN TWR.IS_CLAIM = '10041001' THEN '索赔' ELSE '未索赔' END

3、WITH AS短语，也叫做子查询部分（subquery factoring）
DB2的函数：
sql.append("WITH TA AS /n");
sql.append(" (SELECT T2.* /n");
sql.append(" FROM TT_SHOWROOM_FORECAST AS T1, /n");
sql.append(" TABLE(VALUES('Call In', /n");
sql.append(" T1.CALLIN_SERIES_NUM,

/n");
sql.append(" T1.CURRENT_DATE,

/n");
sql.append(" T1.CURRENT_WEEK,

/n");
sql.append(" T1.DEALER_CODE),

/n");
sql.append(" ('Traffic',

T1.WALKIN_SERIES_NUM, T1.CURRENT_DATE, /n");
sql.append(" T1.CURRENT_WEEK,

T1.DEALER_CODE), /n");
sql.append(" ('HSL', T1.HOT_SERIES_NUM,

T1.CURRENT_DATE, T1.CURRENT_WEEK, /n");
sql.append(" T1.DEALER_CODE), /n");
sql.append(" ('Retail Order',

T1.SALES_SERIES_NUM, T1.CURRENT_DATE, /n");
sql.append(" T1.CURRENT_WEEK,

T1.DEALER_CODE), /n");
sql.append(" ('Test Drive',

T1.TEST_DRIVE, T1.CURRENT_DATE, T1.CURRENT_WEEK, /n");
sql.append(" T1.DEALER_CODE)) AS T2

(CATEGORY, NUM, CURRENT_DATE, CURRENT_WEEK, DEALER_CODE) ");
if(!CheckUtil.checkNull(dealerCodes)){
sql.append(" WHERE T1.DEALER_CODE in ('" +

dealerCodes.replace(",", "','") + "') /n");
}
sql.append(" ),/n");

mysql的函数： :直接将这一段sql抽出来，再作为表使用
sqlTA.append(" SELECT

T2.CATEGORY,T2.NUM,T2.CURRENT_DATET,T2.CURRENT_WEEK,T2.DEALER_CODE

/n");
sqlTA.append(" FROM ( /n");
sqlTA.append(
"select 'Call In' AS CATEGORY,

T3.CALLIN_SERIES_NUM AS NUM, T3.CURRENT_DATET AS CURRENT_DATET,

T3.CURRENT_WEEK AS CURRENT_WEEK, T3.DEALER_CODE AS DEALER_CODE from

tt_showroom_forecast_dcs AS T3 /n");
sqlTA.append("UNION ALL /n");
sqlTA.append(
"select 'Traffic' AS

CATEGORY,T4.CALLIN_SERIES_NUM AS NUM,T4.CURRENT_DATET AS

CURRENT_DATET,T4.CURRENT_WEEK AS CURRENT_WEEK, T4.DEALER_CODE AS

DEALER_CODE from tt_showroom_forecast_dcs AS T4 /n");
sqlTA.append("UNION ALL /n");
sqlTA.append(
"select 'Traffic' AS

CATEGORY,T4.CALLIN_SERIES_NUM AS NUM,T4.CURRENT_DATET AS

CURRENT_DATET,T4.CURRENT_WEEK AS CURRENT_WEEK, T4.DEALER_CODE AS

DEALER_CODE from tt_showroom_forecast_dcs AS T4 /n");
sqlTA.append("UNION ALL /n");
sqlTA.append(
"select 'HSL' AS

CATEGORY,T5.HOT_SERIES_NUM AS NUM,T5.CURRENT_DATET AS

CURRENT_DATET,T5.CURRENT_WEEK AS CURRENT_WEEK, T5.DEALER_CODE AS

DEALER_CODE from tt_showroom_forecast_dcs AS T5 /n");
sqlTA.append("UNION ALL /n");
sqlTA.append(
"select 'Test Drive' AS

CATEGORY,T7.TEST_DRIVE AS NUM,T7.CURRENT_DATET AS

CURRENT_DATET,T7.CURRENT_WEEK AS CURRENT_WEEK, T7.DEALER_CODE AS

DEALER_CODE from tt_showroom_forecast_dcs AS T7 /n");

if (!CheckUtil.checkNull(dealerCodes)) {
sqlTA.append(" WHERE T1.DEALER_CODE in ('" +

dealerCodes.replace(",", "','") + "') /n");
}
sqlTA.append(" ) AS T2 /n");
直接使用：
sqlTB.append("SELECT CATEGORY FROM ( /n");
sqlTB.append(sqlTA);

4、截取给定字符串的字符
DB2的函数： substr （hello,a,b） -- a=0和a=1是一样的，都是从第一个开始
mysql的函数： substring（hello,a,b）-- 是从0开始。

5、控制流函数
DB2的函数： nvl(sum(TOTALL),0)
mysql的函数： IFNULL(sum(TOTALL),0)

6、 listagg函数 (行列转换)
DB2的函数：
LISTAGG(B.ORG_ID, ',') WITHIN GROUP(ORDER BY A.ORG_ID) 。。 (order by)
mysql的函数：
(select GROUP_CONCAT(ORG_ID) from TM_ORG )AS SMALL_AREA_ID 。。。

(order by)