sql一些常用的函数--decode，case when ，nvl

实例：

select a.t$scpc, --批次
       a.t$jjno, --交接单
       a.t$xuno, --行序
       decode(a.t$khxh, ' ', p.t$khxh, a.t$khxh) khxh,
       a.t$ponb, --行序
       a.t$qana, --数量
       a.t$sitm, --物料代码
       a.t$qana, --数量
       a.t$wcdt, --完成时间
       c.t$dsca, --物料名称（主体机型）
       decode(d.t$spec,1,'家用',2,'商用',3,'通用',4,'出口',5,'小家电',6,'家用(客户化)',7,'商用(客户化)',8,'empty1',9,'小家电(客户化) ',10,'晶弘',11,'手机',12,'设备',13,' 洗衣机',14,'机电',15,'晶弘(客户化)',16,'洗衣机(客户化)' ), --品类
       p.t$rzlx, --认证
       (select f.t$dsca from ttcmcs015677 f where f.t$ctyp = c.t$ctyp) dsca1, --产品类型
       (select h.t$dsca from ttcmcs010677 h where h.t$ccty = b.t$ccty) dsca2, --出口地
       (select l.t$nama from ttccom001677 l where l.t$emno = b.t$ccon) nama, --业务员
       w.t$osta,
       w.t$pdno
  from Ttdsct092677 a,
       ttdsct090677 b,
       ttcibd001677 c,
       ttcibd500677 d,
       ttisfc001677 w,
       ttdsct091677 p
 where a.t$jjno = b.t$jjno
   and a.t$sitm = d.t$item
   and d.t$item = c.t$item
   and a.t$jjno = p.t$jjno
   and a.t$xuno = p.t$xuno
   and a.t$scpc > ' '
   and a.t$wcdt between to_date('2022/01/01', 'yyyy/MM/dd') and
       to_date('2023/12/31', 'yyyy/MM/dd')
   and w.t$pdno = (select q.t$orno
                      from ttisfc998677 q
                     where q.t$lykr = 10
                       and q.t$Lyno = a.t$jjno
                       AND q.T$PONO = a.t$xuno
                       AND q.T$XUNO = a.t$ponb
                       And q.T$KOOR = 1
                       and rownum = 1
                       )
   and w.t$osta < 8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

and rownum = 1 //选取多条数据中的第一条。
decode函数是：
含义解释：

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN
　　　　RETURN(翻译值1) ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　… ELSIF 条件=值n THEN
　　　　RETURN(翻译值n) ELSE
　　　　RETURN(缺省值) END IF

decode(字段或字段的运算，值1，值2，值3）

   这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3  当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
1

使用方法：

1、比较大小 select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 例如： 变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、此函数用在SQL语句中，功能介绍如下：

Decode函数与一系列嵌套的
IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。如果base_exp和 第i
个compare项匹配，就返回第i 个对应的value
。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值顺次求值，如果发现一个匹配，则剩下的compare值（如果还有的话）就都不再求值。一个为NULL的base_exp被认为和NULL
compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。

Decode函数在实际开发中非常的有用

结合Lpad函数，如何使主键的值自动加1并在前面补0 select
LPAD(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,‘0’) 记录编号 from
tetdmis eg:

select decode(dir,1,0,1) from a1_interval

dir 的值是1变为0，是0则变为1

比如我要查询某班男生和女生的数量分别是多少?

通常我们这么写:

select count(*) from 表 where 性别 ＝ 男；

select count(*) from 表 where 性别 ＝ 女；

要想显示到一起还要union一下，太麻烦了

用decode呢，只需要一句话

select decode(性别，男，1，0），decode(性别，女，1，0） from 表

同时，我们要知道：

1.DECODE 只有Oracle 才有，其它数据库不支持;

2.CASE WHEN的用法， Oracle、SQL Server、 MySQL 都支持;

3.DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断,CASE when可用于=,>=,<,<=,<>,is null,is not null 等的判断;

4.DECODE 使用其来比较简洁，CASE 虽然复杂但更为灵活;

5.另外，在decode中，null和null是相等的，但在case when中，只能用is null来判断
1
2
3
4
5
6
7
8
9

CASE WHEN用法

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END


1
2
3
4
5

等同于，使用CASE WHEN条件表达式函数实现：

   CASE WHEN SCORE = 'A' THEN '优'
         WHEN SCORE = 'B' THEN '良'
         WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
1
2
3

THEN后边的值与ELSE后边的值类型应一致，否则会报错。如下：

CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE 0 END

'优’和0数据类型不一致则报错：

[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。

CASE WHEN条件表达式函数：类似JAVA中的IF ELSE语句。

格式：

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END
1
2
3
4
5
6
7

condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

下面介绍几种常用场景。

场景1：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE
1
2
3
4
5
6
7
8
9
10

注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：

CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END
1

nvl：
NVL()函数是Oracle中的一个函数，NVL()函数的功能是实现空值的转换。

例如NVL(string1,replace_with)中：
当第一个参数（string1）为空时，返回第二个参数（replace_with）；
当第一个参数（string1)不为空时，则返回第一个参数（string1）。
1
2
3

NVL()函数的第一个参数和第二个参数类型必须相同，或者可以由隐式转换得到。

拓展：
NVL2()函数：Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。

如NVL2(E1,E2,E3)中：
当E1为NULL时，返回E3；当E1不为NULL时，返回E2
1
2