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_group

FROM people

GROUP BY age_group;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

二、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；

1
2
3
4
5
6

三、CAST函数

用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法：CAST (expression AS data_type)

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
1
2
3
4
5

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

:

实例：

1.SELECT CAST('1.0' AS decimal)  结果：1

2.SELECT CAST('9.5' AS decimal(10,2))  结果：9.50(精度与小数位数分别为10与2

3.SELECT  CAST(NOW() AS   DATE) 结果：2017-11-27
1
2
3
4
5

四、CONVERT() 函数

定义和用法:

ONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。
1
2

语法

CONVERT(data_type(length),data_to_be_converted,style)
1

data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式

我们将使用 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
1
2
3
4
5
6
7
8
9
10
11

五、COALESCE（）函数

COALESCE函数是用来处理NULL值的一个函数

简介：

COALESCE函数并非某个数据库的专有函数，它是标准的SQL函数，被许多数据库系统支持。一些广泛使用的数据库系统，如MySQL、PostgreSQL、SQLite、SQL Server、Oracle等，都实现了COALESCE函数。尽管各数据库实现和性能可能略有不同，但其核心功能和语法在各个数据库中基本保持一致。
1

语法：
COALESCE函数是SQL中的一个非常实用的函数，它可以用于返回一系列参数中的第一个非空值。如果所有参数都为NULL，COALESCE函数将返回NULL。

COALESCE(value1, value2, ..., valueN)
1

SELECT
  pid,
  SUM(COALESCE(units_sold, 0)) AS total_units_sold
FROM
  A
GROUP BY
  pid;
1
2
3
4
5
6
7

进阶：

SELECT
  *,
  COALESCE(
    (CASE
      WHEN e1.pid IS NOT NULL THEN
        e2.first_name || ' ' || e2.last_name
    END),
    'N/A'
  ) AS sn
FROM
  A e1
LEFT JOIN B e2 ON e1.pid = e2.eid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：

1. 数据类型一致性：尽管COALESCE函数允许检查多个值，但请确保这些值具有相同或兼容的数据类型。如果数据类型不兼容，可能会导致错误或难以预测的结果。
2. 避免过多嵌套：虽然可以在COALESCE函数内嵌套其他函数，但过多的嵌套可能会导致代码难以阅读和维护。如果需要实现复杂逻辑，请考虑使用CASE语句或将逻辑拆分到多个查询中。
3. NULL值处理：了解如何在特定场景下处理NULL值很重要。在某些情况下，使用COALESCE函数可能不是最佳选择。例如，如果你需要筛选出非NULL值的行，可以使用IS NOT NULL条件而不是COALESCE。
4. 数据库特定实现：虽然COALESCE函数在许多数据库系统中都有实现，但它们之间可能存在一些差异。在使用COALESCE函数时，请参考你正在使用的数据库系统的文档，了解其特定实现和最佳实践。
5. 适当的默认值：在使用COALESCE函数指定默认值时，请确保选择的默认值在上下文中具有意义。例如，如果您正在处理数字数据，使用0作为默认值可能比较合适；而对于文本数据，空字符串（‘’）可能是一个更好的选择。
6. 性能影响：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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23