SQL的CASE WHEN函数、CAST函数、CONVERT() 函数、COALESCE()函数、DATEDIFF()函数
一、CASE WHEN简单使用
SELECT
CASE
WHEN age >= 18 AND age <= 25 THEN '18-25'WHEN age > 25 AND age <= 35 THEN '25-35'WHEN age > 35 AND age <= 45 THEN '36-45'ELSE '45+'END AS age_groupFROM peopleGROUP BY age_group;
二、CASE WHEN语句与聚合函数一起使用
SELECT
COUNTRY,
SUM(CASE SEX WHEN '1' THEN POPULATION ELSE 0 END) AS '男',
SUM(CASE SEX WHEN '2' THEN PUPULATION ELSE 0 END) AS '女'
FORM TABLE_A GROUP BY COUNTRY；三、CAST函数
用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
语法：CAST (expression AS data_type)expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
| 二进制 | BINARY |
| 字符型 | 可带参数 : CHAR() |
| DATE | DATE |
| 时间 | TIME |
| 日期时间型 | DATETIME |
| 浮点数 | DECIMAL |
| 整数 | SIGNED |
| 无符号整数 | UNSIGNED |
:
实例：
1.SELECT CAST('1.0' AS decimal) 结果：12.SELECT CAST('9.5' AS decimal(10,2)) 结果：9.50(精度与小数位数分别为10与23.SELECT CAST(NOW() AS DATE) 结果：2017-11-27
四、CONVERT() 函数
定义和用法:
ONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。
语法
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式
| Style ID | Style 格式 |
|---|---|
| 100 或者 0 | mon dd yyyy hh:miAM （或者 PM） |
| 101 | mm/dd/yy |
| 102 | yy.mm.dd |
| 103 | dd/mm/yy |
| 104 | dd.mm.yy |
| 105 | dd-mm-yy |
| 106 | dd mon yy |
| 107 | Mon dd, yy |
| 108 | hh:mm:ss |
| 109 或者 9 | mon dd yyyy hh:mi:ss:mmmAM（或者 PM） |
| 110 | mm-dd-yy |
| 111 | yy/mm/dd |
| 112 | yymmdd |
| 113 或者 13 | dd mon yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 120 或者 20 | yyyy-mm-dd hh:mi:ss(24h) |
| 121 或者 21 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-ddThh:mm:ss.mmm（没有空格） |
| 130 | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yy hh:mi:ss:mmmAM |
我们将使用 GETDATE() 函数来获得当前的日期/时间：
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)结果：Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635
五、COALESCE（）函数
COALESCE函数是用来处理NULL值的一个函数
简介：
COALESCE函数并非某个数据库的专有函数，它是标准的SQL函数，被许多数据库系统支持。一些广泛使用的数据库系统，如MySQL、PostgreSQL、SQLite、SQL Server、Oracle等，都实现了COALESCE函数。尽管各数据库实现和性能可能略有不同，但其核心功能和语法在各个数据库中基本保持一致。
语法：
COALESCE函数是SQL中的一个非常实用的函数，它可以用于返回一系列参数中的第一个非空值。如果所有参数都为NULL，COALESCE函数将返回NULL。
COALESCE(value1, value2, ..., valueN)
SELECTpid,SUM(COALESCE(units_sold, 0)) AS total_units_sold
FROMA
GROUP BYpid;
进阶：SELECT*,COALESCE((CASEWHEN e1.pid IS NOT NULL THENe2.first_name || ' ' || e2.last_nameEND),'N/A') AS sn
FROMA e1
LEFT JOIN B e2 ON e1.pid = e2.eid;
注意：
- 数据类型一致性：尽管COALESCE函数允许检查多个值，但请确保这些值具有相同或兼容的数据类型。如果数据类型不兼容，可能会导致错误或难以预测的结果。
- 避免过多嵌套：虽然可以在COALESCE函数内嵌套其他函数，但过多的嵌套可能会导致代码难以阅读和维护。如果需要实现复杂逻辑，请考虑使用CASE语句或将逻辑拆分到多个查询中。
- NULL值处理：了解如何在特定场景下处理NULL值很重要。在某些情况下，使用COALESCE函数可能不是最佳选择。例如，如果你需要筛选出非NULL值的行，可以使用IS NOT NULL条件而不是COALESCE。
- 数据库特定实现：虽然COALESCE函数在许多数据库系统中都有实现，但它们之间可能存在一些差异。在使用COALESCE函数时，请参考你正在使用的数据库系统的文档，了解其特定实现和最佳实践。
- 适当的默认值：在使用COALESCE函数指定默认值时，请确保选择的默认值在上下文中具有意义。例如，如果您正在处理数字数据，使用0作为默认值可能比较合适；而对于文本数据，空字符串（‘’）可能是一个更好的选择。
- 性能影响：COALESCE函数在处理大量数据时可能会影响性能。尽量优化查询，避免不必要的计算，以提高性能。
六、DATEDIFF ( datepart , startdate , enddate )释义：计算时间差；
datepare值：year | quarter | month | week | day | hour | minute | second | millisecond
startdate：开始日期
enddate ：结束日期
GetDate()：获取当前的系统日期
下面例子中表名为tablename,条件字段名为inputdate
查询今天SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=0查询昨天SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=1查询本周SELECT * FROM tablename where DATEDIFF(week,inputdate,GETDATE())=0查询上周SELECT * FROM tablename where DATEDIFF(week,inputdate,GETDATE())=1查询本月SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=0查询上月SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=1