sql中几个好用的函数

红太狼 2023-10-05 23:57 6阅读 0赞

**COALESCE(expression,0)**

语法：

**COALESCE ( expression \[ ,…n \] )**

如果所有参数均为 NULL，则 COALESCE 返回 NULL。至少应有一个 Null 值为 NULL 类型。尽管 ISNULL 等同于 COALESCE，但它们的行为是不同的。包含具有非空参数的 ISNULL 的表达式将视为 NOT NULL，而包含具有非空参数的 COALESCE 的表达式将视为 NULL。在 SQL Server 中，若要对包含具有非空参数的 COALESCE 的表达式创建索引，可以使用 PERSISTED 列属性将计算列持久化

**sql 中的if else表达式**

SELECT T1.NAME, CASE WHEN T2.DATA IS NULL THEN 0 ELSE T2.DATA END  
FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID

Case具有两种格式：简单Case函数和Case搜索函数。  
一：简单Case函数

case sex
        when '1' then '男'
        when '2' then '女'
        else '其他' end

二：Case搜索函数

case 
        when sex = '1' then '男' 
        when sex = '2' then '女' 
        else '其他' end

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。  
– 比如说，下面这段sql，你永远无法得到“第二类”这个结果

case 
        when col_1 in ( 'a', 'b') then '第一类' 
        when col_1 in ('a')  then '第二类' 
        else'其他' end

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

CASE SCORE WHEN 'A' THEN '优' ELSE 0 END

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

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

**下面介绍几种常用场景。**  
场景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

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

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

场景2：现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。  
表结构如下：其中STU\_SEX字段，0表示男生，1表示女生。

STU\_CODE STU\_NAME STU\_SEX STU\_SCORE  
XM 小明 0 88  
XL 小磊 0 55  
XF 小峰 0 45  
XH 小红 1 66  
XN 晓妮 1 77  
XY 小伊 1 99

SELECT 
    	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
    	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
    	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
    	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
    FROM 
    	THTF_STUDENTS

输出结果如下：

MALE\_COUNT FEMALE\_COUNT MALE\_PASS FEMALE\_PASS  
3 3 1 3

**场景3：经典行转列，并配合聚合函数做统计**  
现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

有能耗表如下：  
E\_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

![在这里插入图片描述][2adbc0e8afcf4e5cb4663c0f65a28711.png_pic_center]

SELECT 
    	E_CODE,
    	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
    	SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
    	SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
    FROM 
    	THTF_ENERGY_TEST
    GROUP BY
    	E_CODE

输出结果如下：  
![在这里插入图片描述][44ca7707c23f42bbaea11327266e6e94.png_pic_center]  
**场景4：CASE WHEN中使用子查询**  
根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。  
价格表如下：

当能耗值小于10时，使用P\_LEVEL=0时的P\_PRICE的值，能耗值大于10小于30使用P\_LEVEL=1时的P\_PRICE的值…

![在这里插入图片描述][ff678c76436a40dbb8825fbb54779722.png_pic_center]

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
        WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
        WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

[2adbc0e8afcf4e5cb4663c0f65a28711.png_pic_center]: https://img-blog.csdnimg.cn/2adbc0e8afcf4e5cb4663c0f65a28711.png#pic_center
[44ca7707c23f42bbaea11327266e6e94.png_pic_center]: https://img-blog.csdnimg.cn/44ca7707c23f42bbaea11327266e6e94.png#pic_center
[ff678c76436a40dbb8825fbb54779722.png_pic_center]: https://img-blog.csdnimg.cn/ff678c76436a40dbb8825fbb54779722.png#pic_center