类型转换CAST & 数据透视 CASE WHEN

 

1. 利用 CAST 可以修改数据的类型，下面示例中，原本SQL返回的数据类型为decimal，被修改成为整数型；

2. 利用Case when 语句，实现pivot 功能，下面SQL语句实现的搜索结果为下图

SELECT 
    DATE_FORMAT(uniic_db.app_miniline_yield_miniline_yield.Date_Test,
            '%Y%m') AS months,
    CAST(SUM(CASE WHEN (Board_Type = 'D2 U/SO-DIMM' AND PO_Type <> 'F') THEN QTY_Test ELSE 0 END) AS SIGNED) AS 'D2 U/SO-DIMM',
    CAST(SUM(CASE WHEN (Board_Type = 'D3 U/SO-DIMM' AND PO_Type <> 'F') THEN QTY_Test ELSE 0 END) AS SIGNED) AS 'D3 U/SO-DIMM',
    CAST(SUM(CASE WHEN (Board_Type = 'D4 U/SO-DIMM' AND PO_Type <> 'F') THEN QTY_Test ELSE 0 END) AS SIGNED) AS 'D4 U/SO-DIMM',
    CAST(SUM(CASE WHEN (Board_Type = 'D3 R-DIMM' AND PO_Type <> 'F') THEN QTY_Test ELSE 0 END) AS SIGNED) AS 'D3 R-DIMM',
    CAST(SUM(CASE WHEN ((Board_Type='D4 R-DIMM（Z10PE）' or Board_Type= 'D4 R-DIMM（Z11PA）' or Board_Type= 'D4 R-DIMM') AND PO_Type <> 'F') THEN QTY_Test ELSE 0 END) AS SIGNED) AS 'D4 R-DIMM',
    CAST(SUM(CASE WHEN PO_Type <>'F' THEN QTY_Test else 0 end) as signed ) AS 'test_qty',
    CAST(SUM(CASE WHEN PO_Type <>'xxxxx' THEN QTY_Test else 0 end) as signed ) AS 'NO_test_qty'
FROM
    uniic_db.app_miniline_yield_miniline_yield
GROUP BY months order by months ;

搜索结果：

 

 

CAST函数：

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

语法：CAST (expression AS data_type)

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

 

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

• 二进制，同带binary前缀的效果 : BINARY    
• 字符型，可带参数 : CHAR()     
• 日期 : DATE     
• 时间: TIME     
• 日期时间型 : DATETIME     
• 浮点数 : DECIMAL      
• 整数 : SIGNED     
• 无符号整数 : UNSIGNED 

 

例子：

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

 

 2.SELECT CAST('24356349.5535772' AS decimal(30,3))    结果：'24356349.554' (精度与小数位数分别为30与3。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数)

3.SELECT  CAST(NOW() AS   DATE) 结果：'2020-07-30'    原本NOW() 是DATETIME 类型，被更改为DATE类型；

 

 

 

Case 函数

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

 

下面我们来看一下，使用case函数都能做些什么事情。

一，已知数据按照另外一种方式进行分组，分析。

有如下数据：

国家（country）	人口（population）
中国	1200
美国	120
加拿大	320
英国	200
法国	500
日本	550
德国	700
墨西哥	550
印度	1150

 根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。

洲	人口
亚洲	总人数
北美洲	总人数
其他	总人数

想要解决这个问题，你会怎么做？生成一个带有洲code的view，是一个解决方法，但是这样很难动态的改变统计的方式。
假如使用case函数，sql代码如下:

select sum(population),
case country
when '中国'     then'亚洲'
when '印度'     then'亚洲'
when '日本'     then'亚洲'
when '美国'     then'北美洲'
when '加拿大'  then'北美洲'
when '墨西哥'  then'北美洲'
else '其他' end
from   table_a
group by case country
when '中国'     then'亚洲'
when '印度'     then'亚洲'
when '日本'     then'亚洲'
when '美国'     then'北美洲'
when '加拿大'  then'北美洲'
when '墨西哥'  then'北美洲'
else '其他' end;

 

二，用一个sql语句完成不同条件的分组。

普通情况下，用union也可以实现用一条语句进行查询。但是那样增加消耗(两个select部分)，而且sql语句会比较长。
下面是一个是用case函数来完成这个功能的例子

select country,
sum( case when sex = '1' then
population else 0 end),  --男性人口
sum( case when sex = '2' then
population else 0 end)   --女性人口
from table_a
group by country;

这样我们使用select，可以完成pivot 数据透视表。

国家	男	女
中国	1340	1260
美国	450	505
加拿大	140	190
英国	50	55