SQL函数教程

介绍

SQL 拥有很多可用于计数和计算的内建函数。

Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

有用的 Aggregate 函数：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

**提示：**在下面，我们会详细讲解 Aggregate 函数和 Scalar 函数。

注意: SQL函数一般都是配合分组进行使用,很少单独的使用

AVG() 函数

AVG() 函数返回数值列的平均值。

AVG() 语法

SELECT AVG(column_name) FROM table_name

AVG() 实例

下面的 SQL 语句从 “access_log” 表的 “count” 列获取平均值：

mysql> SELECT AVG(count) AS CountAverage FROM access_log; 
+--------------+
| CountAverage |
+--------------+
| 174.3333     |
+--------------+
1 row in set (0.04 sec)

下面的 SQL 语句选择访问量高于平均访问量的 “site_id” 和 “count”：

mysql> 
  SELECT site_id, count FROM access_log
  WHERE count > (SELECT AVG(count) FROM access_log); 
+---------+-------+
| site_id | count |
+---------+-------+
|       1 |   230 |
|       5 |   205 |
|       3 |   220 |
|       5 |   545 |
|       3 |   201 |
+---------+-------+
5 rows in set (0.04 sec)

COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

COUNT(column_name) 语法

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

COUNT(*) 语法

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name) 语法

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目(去重后的)：

SELECT COUNT(DISTINCT column_name) FROM table_name;

**注释：**COUNT(DISTINCT) 适用于 oracle mysql SQL Server，但是无法用于 Microsoft Access。

COUNT(column_name) 实例

下面的 SQL 语句计算 “access_log” 表中 “site_id”=3 的总访问量：

mysql>    
  SELECT COUNT(count) AS nums FROM access_log
  WHERE site_id=3; 
+------+
| nums |
+------+
|    3 |
+------+
1 row in set (0.04 sec)

COUNT(*) 实例

下面的 SQL 语句计算 “access_log” 表中总记录数：

mysql> SELECT COUNT(*) AS nums FROM access_log; 
+------+
| nums |
+------+
|    9 |
+------+
1 row in set (0.06 sec)

COUNT(DISTINCT column_name) 实例

下面的 SQL 语句计算 “access_log” 表中不同 site_id 的记录数：

mysql>  SELECT COUNT(DISTINCT site_id) AS nums FROM access_log; 
+------+
| nums |
+------+
|    5 |
+------+
1 row in set (0.04 sec)

FIRST() 函数

FIRST() 函数返回指定的列中第一个记录的值。

FIRST() 语法

SELECT FIRST(column_name) FROM table_name;

**注释：**只有 MS Access 支持 FIRST() 函数

而在 oracle mysql SQL Server我们可以这样:

**SQL Server 语法 **

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

实例

SELECT TOP 1 name FROM Websites
ORDER BY id ASC;

MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

实例

SELECT name FROM Websites
ORDER BY id ASC
LIMIT 1;

Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;

实例

SELECT name FROM Websites
ORDER BY id ASC
WHERE ROWNUM <=1;

LAST() 函数

LAST() 函数返回指定的列中最后一个记录的值。

LAST() 语法

SELECT LAST(column_name) FROM table_name;

**注释：**只有 MS Access 支持 LAST() 函数。

而在 oracle mysql SQL Server我们可以这样:

SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

实例

SELECT TOP 1 name FROM Websites
ORDER BY id DESC;

MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

实例

SELECT name FROM Websites
ORDER BY id DESC
LIMIT 1;

Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;

实例

SELECT name FROM Websites
ORDER BY id DESC
WHERE ROWNUM <=1;

MAX() 函数

MAX() 函数返回指定列的最大值。

MAX() 语法

SELECT MAX(column_name) FROM table_name;

MAX() 实例

下面的 SQL 语句从 “Websites” 表的 “alexa” 列获取最大值：

mysql> SELECT MAX(alexa) AS max_alexa FROM Websites; 
+-----------+
| max_alexa |
+-----------+
|      5000 |
+-----------+
1 row in set (0.03 sec)

MIN()函数

MIN() 函数返回指定列的最小值。

MIN() 语法

SELECT MIN(column_name) FROM table_name;

MIN() 实例

下面的 SQL 语句从 “Websites” 表的 “alexa” 列获取最小值：

mysql> SELECT MIN(alexa) AS min_alexa FROM Websites; 
+-----------+
| min_alexa |
+-----------+
|         0 |
+-----------+
1 row in set (0.04 sec)

SUM() 函数

SUM() 函数返回数值列的总数(也就是列的所有值之和)。

SUM() 语法

SELECT SUM(column_name) FROM table_name;

SUM() 实例

下面的 SQL 语句查找 “access_log” 表的 “count” 字段的总数：

mysql> SELECT SUM(count) AS nums FROM access_log; 
+------+
| nums |
+------+
| 1569 |
+------+
1 row in set (0.04 sec)

GROUP BY 语句(重要)

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

注意: 在 GROUP BY 后面有什么字段那么在SELECT 中就要添加 GROUP BY后面的字段,聚合函数根据情况自己选择

比如:

SELECT name,age, count(name) as 数量 ,SUM(age) FROM t_student
GROUP BY name,age

GROUP BY 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

GROUP BY 简单应用

统计 access_log 各个 site_id 的访问量：

mysql>    
   SELECT site_id, SUM(access_log.count) AS nums
   FROM access_log GROUP BY site_id; 
+---------+------+
| site_id | nums |
+---------+------+
|       1 | 275  |
|       2 | 10   |
|       3 | 521  |
|       4 | 13   |
|       5 | 750  |
+---------+------+
5 rows in set (0.04 sec)

GROUP BY 多表连接

下面的 SQL 语句统计有记录的网站的记录数量：

mysql>    
  SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
  LEFT JOIN Websites
  ON access_log.site_id=Websites.id
  GROUP BY Websites.name; 
+----------+------+
| name     | nums |
+----------+------+
| NULL     |    2 |
| Google   |    2 |
| 微博     |    1 |
| 淘宝     |    1 |
| 菜鸟教程 |    3 |
+----------+------+
5 rows in set (0.05 sec)

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

HAVING 语法

SELECT column_name , aggregate_function ( column_name ) 
FROM table_name 
WHERE column_name operator value 
GROUP BY column_name 
HAVING aggregate_function ( column_name ) operator value ;

HAVING 实例

现在我们想要查找总访问量大于 200 的网站。

mysql> 
SELECT Websites.name , Websites.url , SUM(access_log.count) AS nums 
FROM access_log 
INNER JOIN Websites ON access_log.site_id = Websites.id 
GROUP BY Websites.name 
HAVING SUM(access_log.count ) > 200 ;
+----------+------------------------+------+
| name     | url                    | nums |
+----------+------------------------+------+
| Google   | https://www.google.cm/ | 275  |
| 菜鸟教程 | http://www.runoob/ | 521  |
+----------+------------------------+------+
2 rows in set (0.05 sec)

现在我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200。

我们在 SQL 语句中增加一个普通的 WHERE 子句：

mysql> 
SELECT Websites.name , Websites.url , SUM(access_log.count) AS nums 
FROM access_log 
INNER JOIN Websites ON access_log.site_id = Websites.id 
WHERE Websites.alexa < 200 
GROUP BY Websites.name 
HAVING SUM(access_log.count ) > 200 ;
+--------+------------------------+------+
| name   | url                    | nums |
+--------+------------------------+------+
| Google | https://www.google.cm/ | 275  |
+--------+------------------------+------+
1 row in set (0.04 sec)

EXISTS 运算符

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

EXISTS 语法

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

EXISTS 实例

现在我们想要查找总访问量(count 字段)大于 200 的网站是否存在。

mysql> 
SELECT Websites.name , Websites.url 
FROM Websites 
WHERE EXISTS ( SELECT count FROM access_log 
              WHERE Websites.id = access_log.site_id AND count > 200 ) ;
+----------+------------------------+
| name     | url                    |
+----------+------------------------+
| Google   | https://www.google.cm/ |
| 菜鸟教程 | http://www.runoob/ |
+----------+------------------------+
2 rows in set (0.05 sec)

EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：比如:原本是有记录的加上not就是取反,就是false

mysql> SELECT Websites.name , Websites.url 
FROM Websites 
WHERE NOT EXISTS ( SELECT count FROM access_log 
              WHERE Websites.id = access_log.site_id AND count > 200 ) ;
+---------------+---------------------------+
| name          | url                       |
+---------------+---------------------------+
| 淘宝          | https://www.taobao/   |
| 微博          | http://weibo/         |
| 百度          | https://www.baidu/    |
| stackoverflow | http://stackoverflow/ |
| QQ APP        |                           |
| 微博 APP      |                           |
| 淘宝 APP      |                           |
| QQ APP        |                           |
+---------------+---------------------------+
8 rows in set (0.05 sec)

UCASE() 函数

UCASE() 函数把字段内英文的值转换为大写。

SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name;

用于 SQL Server 的语法

SELECT UPPER(column_name) FROM table_name;

**UCASE() 实例 ** (mysql 版)

下面的 SQL 语句从 “Websites” 表中选取 “name” 和 “url” 列，并把 “name” 列的值转换为大写：

mysql>    
  SELECT UCASE(name) AS site_title, url
  FROM Websites; 
+---------------+---------------------------+
| site_title    | url                       |
+---------------+---------------------------+
| GOOGLE        | https://www.google.cm/    |
| 淘宝          | https://www.taobao/   |
| 菜鸟教程      | http://www.runoob/    |
| 微博          | http://weibo/         |
| 百度          | https://www.baidu/    |
| STACKOVERFLOW | http://stackoverflow/ |
| QQ APP        |                           |
| 微博 APP      |                           |
| 淘宝 APP      |                           |
| QQ APP        |                           |
+---------------+---------------------------+
10 rows in set (0.07 sec)

LCASE() 函数

LCASE() 函数把字段内英文的值转换为小写。

SQL LCASE() 语法

SELECT LCASE(column_name) FROM table_name;

用于 SQL Server 的语法

SELECT LOWER(column_name) FROM table_name;

LCASE() 实例(mysql 版)

下面的 SQL 语句从 “Websites” 表中选取 “name” 和 “url” 列，并把 “name” 列的值转换为小写：

mysql>    
  SELECT LCASE(name) AS site_title, url
  FROM Websites; 
+---------------+---------------------------+
| site_title    | url                       |
+---------------+---------------------------+
| google        | https://www.google.cm/    |
| 淘宝          | https://www.taobao/   |
| 菜鸟教程      | http://www.runoob/    |
| 微博          | http://weibo/         |
| 百度          | https://www.baidu/    |
| stackoverflow | http://stackoverflow/ |
| qq app        |                           |
| 微博 app      |                           |
| 淘宝 app      |                           |
| qq app        |                           |
+---------------+---------------------------+
10 rows in set (0.06 sec)

MID() 函数

MID() 函数用于从文本字段中提取字符。

MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name;

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

MID() 实例

下面的 SQL 语句从 “Websites” 表的 “name” 列中提取前 4 个字符(空格也算)：

mysql>    
  SELECT MID(name,1,4) AS ShortTitle
  FROM Websites; 
+------------+
| ShortTitle |
+------------+
| Goog       |
| 淘宝       |
| 菜鸟教程   |
| 微博       |
| 百度       |
| stac       |
| QQ A       |
| 微博 A     |
| 淘宝 A     |
| QQ A       |
+------------+
10 rows in set (0.06 sec)

LEN() 函数

LEN() 函数返回文本字段中值的长度。

LEN() 语法

SELECT LEN(column_name) FROM table_name;

MySQL 中函数为 LENGTH():

SELECT LENGTH(column_name) FROM table_name;

LEN() 实例

下面的 SQL 语句从 “Websites” 表中选取 “name” 和 “url” 列中值的长度：

mysql> 
  SELECT name, LENGTH(url) as LengthOfURL
  FROM Websites; 
+---------------+-------------+
| name          | LengthOfURL |
+---------------+-------------+
| Google        |          22 |
| 淘宝          |          23 |
| 菜鸟教程      |          22 |
| 微博          |          17 |
| 百度          |          22 |
| stackoverflow |          25 |
| QQ APP        |           0 |
| 微博 APP      |           0 |
| 淘宝 APP      |           0 |
| QQ APP        |           0 |
+---------------+-------------+
10 rows in set (0.05 sec)

ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。

ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name;

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

ROUND() 实例

ROUND(X)： 返回参数X的四舍五入的一个整数。

mysql>  select ROUND(-1.23);
+--------------+
| ROUND(-1.23) |
+--------------+
| -1           |
+--------------+
1 row in set (0.06 sec)
        
mysql> select ROUND(-1.58);
+--------------+
| ROUND(-1.58) |
+--------------+
| -2           |
+--------------+
1 row in set (0.05 sec)
        
mysql> select ROUND(1.58);
+-------------+
| ROUND(1.58) |
+-------------+
| 2           |
+-------------+
1 row in set (0.04 sec)

ROUND(X,D)： 返回参数X的四舍五入和保留D 位小数(自动四舍五入小数部分)。如果D为0，结果将没有小数点或小数部分。

mysql> select ROUND(1.298, 1);
+-----------------+
| ROUND(1.298, 1) |
+-----------------+
| 1.3             |
+-----------------+
1 row in set (0.04 sec)


mysql> select ROUND(1.298, 0);
+-----------------+
| ROUND(1.298, 0) |
+-----------------+
| 1               |
+-----------------+
1 row in set (0.04 sec)

NOW() 函数

NOW() 函数返回当前系统的日期和时间。

NOW() 语法

SELECT NOW() FROM table_name;

NOW() 实例

下面的 SQL 语句从 “Websites” 表中选取 name，url，及当天日期：

mysql>    
  SELECT name, url, Now() AS date
  FROM Websites; 
+---------------+---------------------------+---------------------+
| name          | url                       | date                |
+---------------+---------------------------+---------------------+
| Google        | https://www.google.cm/    | 2021-03-17 21:33:44 |
| 淘宝          | https://www.taobao/   | 2021-03-17 21:33:44 |
| 菜鸟教程      | http://www.runoob/    | 2021-03-17 21:33:44 |
| 微博          | http://weibo/         | 2021-03-17 21:33:44 |
| 百度          | https://www.baidu/    | 2021-03-17 21:33:44 |
| stackoverflow | http://stackoverflow/ | 2021-03-17 21:33:44 |
| QQ APP        |                           | 2021-03-17 21:33:44 |
| 微博 APP      |                           | 2021-03-17 21:33:44 |
| 淘宝 APP      |                           | 2021-03-17 21:33:44 |
| QQ APP        |                           | 2021-03-17 21:33:44 |
+---------------+---------------------------+---------------------+
10 rows in set (0.05 sec)

FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化。

FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name;

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

FORMAT() 实例

下面的 SQL 语句从 “Websites” 表中选取 name, url 以及格式化为 YYYY / MM / DD 的日期：

mysql>    SELECT name, url, DATE_FORMAT(Now(),'%Y/%m/%d') AS date
  FROM Websites; 
+---------------+---------------------------+------------+
| name          | url                       | date       |
+---------------+---------------------------+------------+
| Google        | https://www.google.cm/    | 2021/03/17 |
| 淘宝          | https://www.taobao/   | 2021/03/17 |
| 菜鸟教程      | http://www.runoob/    | 2021/03/17 |
| 微博          | http://weibo/         | 2021/03/17 |
| 百度          | https://www.baidu/    | 2021/03/17 |
| stackoverflow | http://stackoverflow/ | 2021/03/17 |
| QQ APP        |                           | 2021/03/17 |
| 微博 APP      |                           | 2021/03/17 |
| 淘宝 APP      |                           | 2021/03/17 |
| QQ APP        |                           | 2021/03/17 |
+---------------+---------------------------+------------+
10 rows in set (0.08 sec)

case when 的用法

应用场景

行变成列

有这样一张表

E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

E_CODE	E_VALUE	E_TYPE
北京	28.50	0
北京	23.51	1
北京	28.12	2
北京	12.30	0
北京	15.46	1
上海	18.88	0
上海	16.66	1
上海	19.99	0
上海	10.05	0

创建表

CREATE TABLE `thtf_energy_test` (
  `E_CODE` varchar(255) DEFAULT NULL,
  `E_VALUE` varchar(255) DEFAULT NULL,
  `E_TYPE` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('北京', '28.50', '0');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('北京', '23.51', '1');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('北京', '28.12', '2');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('北京', '12.30', '0');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('北京', '15.46', '1');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('上海', '18.88', '0');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('上海', '16.66', '1');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('上海', '19.99', '0');
INSERT INTO `test`.`thtf_energy_test`(`E_CODE`, `E_VALUE`, `E_TYPE`) VALUES ('上海', '10.05', '0');

如果我们统计每个市的总共耗电量这个比较好弄

select E_CODE ,SUM(E_VALUE) as 总耗
from  thtf_energy_test
GROUP BY E_CODE

但是我们如果要分别统计每个成功水耗多少电耗多少热耗多少这个就麻烦了需要使用多条sql 代码如下

水耗

select E_CODE ,SUM(E_VALUE) as 水耗
from  thtf_energy_test
where E_TYPE='0'
GROUP BY E_CODE

点耗

select E_CODE ,SUM(E_VALUE) as 电耗
from  thtf_energy_test
where E_TYPE='1'
GROUP BY E_CODE

热耗

select E_CODE ,SUM(E_VALUE) as 热耗
from  thtf_energy_test
where E_TYPE='2'
GROUP BY E_CODE

可以看到是不是非常麻烦那么我们可以用一条语句完成吗? 使用Case函数就行

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

语法

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

简单Case函数格式：

CASE 列名
WHEN 条件值1 THEN 选项1
WHEN 条件值2 THEN 选项2
……
ELSE 默认值
END

Case搜索函数：

CASE
WHEN 条件1 THEN 选项1
WHEN 条件2 THEN 选项2
……
ELSE 默认值
END

类似java的 switch

CASE= (开始) 或者指定 WHEN的判断条件值类似于 switch(?)

WHEN= 条件

THEN =满足WHEN条件执行的内容

ELSE =default (如果WHEN都不满足那么就执行)

END (结束)

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

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

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

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

简单CASE WHEN函数只能应对一些简单的业务场景，而Case搜索函数表达式的写法则更加灵活。

如果省略了ELSE子句，则返回NULL。

示例

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

有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)

创建表

CREATE TABLE `state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(255) DEFAULT NULL,
  `population` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

插入的数据

INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (1, '中国', '600');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (2, '美国', '100');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (3, '加拿大', '100');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (4, '英国', '200');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (5, '法国', '300');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (6, '日本', '250');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (7, '德国', '200');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (8, '墨西哥', '50');
INSERT INTO `test`.`state`(`id`, `country`, `population`) VALUES (9, '印度', '250');

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

查询代码如下 (下面就是使用简单Case函数格式)

SELECT  
CASE country  WHEN '中国' THEN '亚洲' 
  WHEN '印度' THEN '亚洲' 
  WHEN '日本' THEN '亚洲' 
  WHEN '美国' THEN '北美洲' 
  WHEN '加拿大'  THEN '北美洲' 
  WHEN '墨西哥'  THEN '北美洲' 
ELSE '其他' END   AS 州,
SUM(population) as 人口
FROM    state 
GROUP BY
CASE country 
WHEN '中国' THEN '亚洲' 
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲' 
WHEN '美国' THEN '北美洲' 
WHEN '加拿大'  THEN '北美洲' 
WHEN '墨西哥'  THEN '北美洲' 
ELSE '其他' END;

使用Case搜索函数的写法

SELECT  
CASE  
  WHEN country='中国' THEN '亚洲' 
  WHEN country='印度' THEN '亚洲' 
  WHEN country='日本' THEN '亚洲' 
  WHEN country='美国' THEN '北美洲' 
  WHEN country='加拿大'  THEN '北美洲' 
  WHEN country='墨西哥'  THEN '北美洲' 
ELSE '其他' 
END   AS 州,
SUM(population) as 人口
FROM    state 
GROUP BY
CASE 
	WHEN country='中国' THEN '亚洲' 
	WHEN country='印度' THEN '亚洲'
	WHEN country='日本' THEN '亚洲' 
	WHEN country='美国' THEN '北美洲' 
	WHEN country='加拿大'  THEN '北美洲' 
	WHEN country='墨西哥'  THEN '北美洲' 
ELSE '其他' END;

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

创建一个表

CREATE TABLE `state_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(255) DEFAULT NULL,
  `population` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (1, '中国', '340', '1');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (2, '中国', '260', '2');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (3, '美国', '45', '1');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (4, '美国', '55', '2');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (5, '加拿大', '51', '1');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (6, '加拿大', '49', '2');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (7, '英国', '40', '1');
INSERT INTO `test`.`state_1`(`id`, `country`, `population`, `sex`) VALUES (8, '英国', '60', '2');

照国家和性别进行分组，得出结果如下

下面是一个是用Case函数来完成这个功能的例子

SELECT country, 
SUM(CASE sex WHEN '1' THEN population ELSE 0 END) AS '男',
SUM(CASE sex WHEN '2' THEN population ELSE 0 END) AS '女'
FROM state_1 
GROUP BY country;

扩展

例，有如下更新条件
1.工资5000以上的职员，工资减少10%
2.工资在2000到4600之间的职员，工资增加15%

很容易考虑的是选择执行两次UPDATE语句，如下所示

--条件1 
UPDATE Personnel  SET salary = salary * 0.9  WHERE salary >= 5000; 
--条件2 
UPDATE Personnel  SET salary = salary * 1.15 
WHERE salary >= 2000 AND salary < 4600;

但是事情没有想象得那么简单

假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内，需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了.

如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:

UPDATE  Personnel   SET  salary = 
CASE  WHEN  salary >= 5000  THEN  salary * 0.9
            WHEN  salary >= 2000 AND salary < 4600  THEN  salary * 1.15
ELSE  salary 
END;

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。

这种方法还可以在很多地方使用，比如说变更主键这种累活。
一般情况下，要想把两条数据的Primary key,a和b交换，需要经过临时存储，拷贝，读回数据的三个过程，要是使用Case函数的话，一切都变得简单多了。

创建表

CREATE TABLE `case_1` (
  `p_key` varchar(255) DEFAULT NULL,
  `col_1` varchar(255) DEFAULT NULL,
  `col_2` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`case_1`(`p_key`, `col_1`, `col_2`) VALUES ('a', '1', '张三');
INSERT INTO `test`.`case_1`(`p_key`, `col_1`, `col_2`) VALUES ('b', '2', '李四');
INSERT INTO `test`.`case_1`(`p_key`, `col_1`, `col_2`) VALUES ('c', '3', '王五');

假设有如上数据，需要把主键a和b相互交换。用Case函数来实现的话，代码如下

UPDATE case_1 SET p_key = 
CASE  WHEN  p_key = 'a'  THEN 'b' 
      WHEN p_key = 'b'  THEN  'a'
ELSE 
	p_key 
END
WHERE p_key IN('a', 'b');

意思就是如果值是a那么换成b 如果值是b那么换成a 如果都不满足那么值不动

计算考试成绩

有分数score

score<60返回不及格
score>=60返回及格
score>=80返回优秀
如果为null或者空那么就是缺席考试

创建表

CREATE TABLE `performance` (
  `score` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('77', '张三');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('65', '李四');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('24', '王五');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('88', '孙刘');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('22', '西施');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('', '刘备');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES ('40', '曹操');
INSERT INTO `test`.`performance`(`score`, `name`) VALUES (NULL, '傻逼');

SELECT
    name,
    CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END AS 成绩,
     CASE WHEN score IS NULL or score ='' THEN '缺席考试' 
        ELSE '正常' END AS 考勤
 FROM  performance

结果:

现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个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

还有很多case 的方式这里就不说了到时候根具业务情况自行到网上查询资料一般都是查询用的比较多

点赞收藏加关注

更多推荐

SQL函数教程

SQL函数教程

SQL函数教程

介绍

Aggregate 函数

Scalar 函数

AVG() 函数

COUNT() 函数

FIRST() 函数

LAST() 函数

MAX() 函数

MIN()函数

SUM() 函数

GROUP BY 语句(重要)

HAVING 子句

EXISTS 运算符

UCASE() 函数

LCASE() 函数

MID() 函数

LEN() 函数

ROUND() 函数

NOW() 函数

FORMAT() 函数

case when 的用法

发布评论取消回复

最近发表

热门文章

标签列表

SQL函数教程

SQL函数教程

介绍

Aggregate 函数

Scalar 函数

AVG() 函数

COUNT() 函数

FIRST() 函数

LAST() 函数

MAX() 函数

MIN()函数

SUM() 函数

GROUP BY 语句(重要)

HAVING 子句

EXISTS 运算符

UCASE() 函数

LCASE() 函数

MID() 函数

LEN() 函数

ROUND() 函数

NOW() 函数

FORMAT() 函数

case when 的用法

相关文章

发布评论取消回复

最近发表

热门文章

标签列表