DB2除法的小数位问题（四舍五入问题）以及其他常用的函数

1. DB2取第一条数据

• 语法如下：

  SELECT * FROM SYS_COMPANY_DEPT t FETCH FIRST 1 ROWS only;
  

2. DB2 中指定值排序

2.1 使用case when

• 先看没有任何排序的数据：
  
• 使用case when排序，如下：

  --要求排序顺序：2，3，0，1
  
  SELECT * FROM SYS_COMPANY_DEPT t
  ORDER BY (CASE t.DEPT_LEVEL WHEN '2' THEN 1 WHEN '3' THEN 2 WHEN '0' THEN 3 WHEN '1' THEN 4 ELSE t.DEPT_LEVEL end)
  

  

2.2 使用decode函数

• 如下：

  --要求排序顺序：2，3，0，1
  
  SELECT decode(t.DEPT_LEVEL,'2',1,'3',2,'0',3,'1',4), t.* 
  FROM SYS_COMPANY_DEPT t
  ORDER BY decode(t.DEPT_LEVEL,'2',1,'3',2,'0',3,'1',4)
  

  

3. 拼接函数

• 这个简单了，使用"||" 或者 concat函数，如下：

  SELECT 'aa' ,'aa'||'-bb',CONCAT('cc','@dd')
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  

4. 强制转换类型——cast函数

• 简单使用，如下：

  SELECT CAST(123.66666 AS decimal(10,2)), cast(date('2023-12-31') AS int) num_1
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  
• 如果要处理四舍五入，请继续往下……

5. DB2除法的小数位问题（四舍五入问题）

5.1 关于round函数

5.1.1 round()函数的简单理解

• 与oracle大同小异，可以参考oracle的此函数进行理解（3.5 ROUND() 与 TRUNC() 函数），如下：
  Oracle 常用简单sql操作（insert into、merge into、start with connect by prior以及 regexp_substr等各种函数用法详解）.
• 然后感受一下db2中此函数的使用，如下：

  SELECT round(0.6666,2) num_1,
  (2/3) num_2, --与oracle不同
  round(2/3,2) num_3, --与oracle不同
  round(2/3) num_4,
  round(0.3333,2)
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  

5.1.2 round() + cast()函数 实现四舍五入

• 效果对比sql如下：

  -- 结果对比
  SELECT round(0.6666,2) num_1,--正常四舍五入
  (2/3) num_2,--0 数据不对
  round(2/3,2) num_3,--0 数据不对
  CAST((2/3) AS decimal(10,2)) num_4,--0 数据不对
  CAST(2 AS decimal)/3 num_5,--0.66666666666666666666666666 
  cast(CAST(2 AS decimal)/3 AS decimal(10,2)) num_6,--保留2位小数，不四舍五入
  cast(round(CAST(2 AS decimal)/3,2) AS decimal(10,2)) num_7,--round(CAST(2 AS decimal)/3,2) 使用cast让计算结果正常，使用round保留2位小数
  round(CAST(2 AS decimal)/3,2) num_8 --优化一下 num_7 的写法，最外层直接round处理即可
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  
• 最终sql如下：

  --实现四舍五入最终SQL
  SELECT round(0.6666,2) num_1,--正常四舍五入，但是只处理没有计算过程的数据
  cast(round(CAST(2 AS decimal)/3,2) AS decimal(10,2)) num_7,--round(CAST(2 AS decimal)/3,2) 使用cast让计算结果正常，使用round保留2位小数
  round(CAST(2 AS decimal)/3,2) num_8 --优化一下 num_7 的写法，最外层直接round处理即可
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  

5.2 dec() + cast()函数 实现四舍五入

• 先看dec()函数单独使用，如下：

  SELECT 
  dec(0.6666,10,2) num_1,--保留2位，但不四舍五入
  dec(0.6666 + 0.5) num_2,--四舍五入 且 保留整数
  dec(0.6666 + 0.05,10,1) num_3,--四舍五入 且 保留1位
  dec(0.6666 + 0.005,10,2) num_4,--四舍五入 且 保留2位
  dec(0.6666 + 0.0005,10,3) num_5,--四舍五入 且 保留3位
  dec(2/3) num_6,
  dec(2/3 + 0.0005,10,3) num_7
  from "SYSIBM".SYSDUMMY1 s ;
  

  
• dec() + cast() 结合使用

  SELECT 
  dec(2/3) num_6,
  dec(2/3 + 0.0005,10,3) num_7,
  dec(CAST(2 AS decimal)/3 ,10,2) num_8, --保留2位，但不四舍五入
  dec(CAST(2 AS decimal)/3 + 0.5) num_9, --四舍五入 且 保留整数
  dec(CAST(2 AS decimal)/3 + 0.05 ,10,1) num_10, --四舍五入 且 保留1位
  dec(CAST(2 AS decimal)/3 + 0.005 ,10,2) num_11 --四舍五入 且 保留3位
  from "SYSIBM".SYSDUMMY1 s ;
  

  

6. 其他常用函数

6.1 关于 COALESCE 函数——空字段处理

• 有点 nvl() 函数的意思，如下：

  SELECT NULL,COALESCE(NULL,'')
  from "SYSIBM".SYSDUMMY1 s ;
  

  

6.2 trim()函数

• 如下：

  --rtrim 去掉字符串右边的空格
  --ltrim 去掉字符串左边的空格
  --trim 去掉字符串左右两边的空格
  
  SELECT trim(' abc '),ltrim(' abc '),rtrim(' abc '),length(rtrim(' abc ')),length(trim(' abc '))
  from "SYSIBM".SYSDUMMY1 s ;
  

  

6.3 字符串截取函数

6.3.1 substr()函数

• 简单不多说，直接看，如下：

  SELECT 
  SUBSTR('abcdef',2,3) , --从第二个字符开始截取，截取3个字符
  SUBSTR('123456',2,LENGTH('123456')-2), --截掉前后的字符
  SUBSTR('123456',LENGTH('123456')-1,2)  --只截取最后两个字符
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  

6.3.2 left() 与 right() 函数

• 如下：

  SELECT 
  left('123456',3), --截取前3个
  RIGHT('123456',2) --截取后2个
  FROM "SYSIBM".SYSDUMMY1 s ;
  

  

7. 更多关于DB2的基本操作

• 关于DB2的更多，可参考下面的文章：
  • 使用docker安装db2.
  • DB2中常见基础操作 ➕ DB2实现递归查询 ➕ DB2中自定义递归函数.
  • DB2中实现数据字段的拼接（LISTAGG() 与 xml2clob、xmlagg）.
  • db2中多种方式备份数据（含db2中clob字段如何使用create table as方式备份）.