导语

这次要实现的是一对多查询，使用 MySQL 的 group_concat 函数实现。

group_concat

简单来说，这个函数的作用就是连接多个字段，函数的具体详解可以看这篇文章。

数据表

首先我们先建立两个表

CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', 'tom');

INSERT INTO `student` VALUES ('2', 'jerry');

CREATE TABLE `course` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`s_id` int(11) NOT NULL,

`c_name` char(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '1', '语文');

INSERT INTO `course` VALUES ('2', '1', '数学');

INSERT INTO `course` VALUES ('3', '2', '英语');

INSERT INTO `course` VALUES ('4', '2', '体育');

INSERT INTO `course` VALUES ('5', '2', '美术');

实例

如果我们用平时使用的 SQL 查询

SELECT s.`name`,c.`c_name` FROM student AS s LEFT JOIN course AS c ON c.s_id = s.id;

查询出的结果是

查询结果

下面用 group_concat 函数查询

SELECT s.`name`,(SELECT group_concat(course.c_name) FROM course WHERE course.s_id = s.id) FROM student AS s;

此时查询的结果

查询结果

今天和大家分享一组常用函数公式的使用方法，用心掌握这些函数，工作效率会大大提升。欢迎转发给更多有需要的人。

一、相关概念

(一)函数语法

由函数名+括号+参数组成

例： 求和函数：SUM(A1,B2,…) 。参数与参数之 间用逗号“,”隔开

(二)运算符

1. 公式运算符：加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(＾)

2. 比较运算符：大与(>)、小于(=) 、不等于(<>)

3. 引用运算符：区域运算符(：)、联合运算符(,)

(三)单元格的相对引用与绝对引用

例： A1

$A1 锁定第A列

A$1 锁定第1行

$A$1 锁定第A列与第1行

二、常用函数

(一)数学函数

1. 求和 =SUM(数值1,数值2,……)

2. 条件求和 =SUMIF(查找的范围,条件(即对象),要求和的范围)

例：(1)=SUMIF(A1:A4,”>=200”,B1:B4)

函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和

(2)=SUMIF(A1:A4,”<300”,C1:C4)

函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和

3. 求个数 =COUNT(数值1,数值2,……)

例：(1) =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数

(2) =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数

4. 条件求个数 =COUNTIF(范围,条件)

例：(1) =COUNTIF(A1:A4，”<>200”)

函数意思：第A1栏至A4栏中不等于200的栏求个数

(2) =COUNTIF(A1:C4,”>=1000”)

函数意思：第A1栏至C4栏中大于等1000的栏求个数

5. 求算术平均数 =AVERAGE(数值1,数值2,……)

例：(1) =AVERAGE(A1,B2)

(2) =AVERAGE(A1:A4)

6. 四舍五入函数 =ROUND(数值,保留的小数位数)

7. 排位函数 =RANK(数值,范围,序别) 1-升序 0-降序

例：(1) =RANK(A1,A1:A4,1)

函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。

(2) =RANK(A1,A1:A4,0)

函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。

8. 乘积函数 =PRODUCT(数值1,数值2,……)

9. 取绝对值 =ABS(数字)

10. 取整 =INT(数字)

(二)逻辑函数

条件函数：=IF(条件,执行条件,执行条件假)—可执行七层嵌套

例： A1=5 A2=3 A3=6 A4=7

=IF(A1>A2,1,0)=1(真)

=IF(A1

=IF(A1>A2,IF(A3>A4,8,9),1)=9

(三)文本函数

1. 截取函数： =LEFT(文本,截取长度)—从左边截取

=RIGHT(文本,截取长度)—从右边截取

=MID(文本,开始位,截取长度)—从中间截取

2. 计算字符长度：LEN(文本)—文本的长度

3. 合并字符函数：CONCATENATE(文本1,文本2,…)

4. 在字符串中查找特定字符：FIND(文本,范围,数值)—数值表示查找第几个

=FIND(“a”,“abcadeafga”,2)=4

=FIND(“a”,“abcadeafga”,3)=7

=FIND(“a”,“abcadeafga”,4)=10

5. 比较两个字符是否完全相符：EXACT(文本1,文本2)

=EXACT(“a”,“a”)=TRUE

=EXACT(“a”,“ab”)=FALSE

=EXACT(“a”,“A”)=FALSE

6. 将数值转化为文本：TEXT(数值,参数) )—参数一般为0

7. 将数值型字符转换为数值：VALUE(数值文本)

(四)判断公式

1. 把公式产生的错误显示为空

公式：C2=IFERROR(A2/B2,””)

说明：如果是错误值则显示为空，否则正常显示。

2. IF多条件判断返回值

公式：C2=IF(AND(A2<500,B2=”未到期”),”补款”,””)

说明：两个条件同时成立用AND，任一个成立用OR函数。

(五)统计公式

1. 统计两个表格重复的内容

公式：B2=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2. 统计不重复的总人数

公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明：用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

(六)求和公式

1. 隔列求和

公式：H3=SUMIF($A$2: $G$2,H$2,A3:G3)

或者： =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

2. 单条件求和

3. 单条件模糊求和

4. 多条件模糊求和

5. 多表相同位置求和

公式：B2=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6. 按日期和产品求和

(七)查找与引用公式

1. 单条件查找公式

公式1： C11=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

2. 双向查找公式

公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

3. 查找最后一条符合条件的记录。

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

4. 多条件查找

公式：详见下图

说明：公式原理同上一个公式

5. 指定区域最后一个非空值查找

6. 按数字区域间取对应的值