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;

二、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)  结果:1

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

3.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)
SELECT
  pid,
  SUM(COALESCE(units_sold, 0)) AS total_units_sold
FROM
  A
GROUP BY
  pid;
进阶:

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. 数据类型一致性:尽管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