mysql中函数IF,GROUP_CONCAT的使用_MySQL

bitsCN.com

mysql中函数IF,GROUP_CONCAT的使用

 

mysql中最近用到的函数，记录下    

1、IF(expr1,expr2,expr3)与我们常用的三目运算类似。expr1是一个表达式，如果TRUE，返回expr2否则为expr3

如下数据：

Sql代码  

INSERT INTO a(id,a,b) VALUES ('1', '1', '1');  

INSERT INTO a(id,a,b) VALUES ('2', '1', '0');  

INSERT INTO a(id,a,b) VALUES ('3', '1', '0');  

INSERT INTO a(id,a,b) VALUES ('4', '1', '0');  

INSERT INTO a(id,a,b) VALUES ('5', '0', '0');  

INSERT INTO a(id,a,b) VALUES ('6', '0', '1');  

比如要查询a的返回状态，1代表是，0代表否有：

Sql代码  

SELECT IF(a=1,'是','否') as flag FROM a  

有时需要比较两列数据，如同时比较a、b其取值通过(1,1),(1,0),(0,1),(0,0)来统计：

Java代码  

SELECT  

SUM(IF (a=1 AND b= 1, 1, 0)) as flag1,  

SUM(IF (a=1 AND b= 0, 1, 0)) as flag2,  

SUM(IF (a=0 AND b= 1, 1, 0)) as flag3,  

SUM(IF (a=0 AND b= 0, 1, 0)) as flag4  

FROM a  

Java代码  

1   3   1   1  

这样就完成了按照类型来统计。

 

2、GROUP_CONCAT将一组数据中的non-NULL作为串联的字符串返回，常与group在一起使用。简单的说就是行转列，如下数据：

Sql代码  

INSERT INTO `table2(id, a)` VALUES ('1', '0');  

INSERT INTO `table2(id, a)` VALUES ('1', '1');  

INSERT INTO `table2(id, a)` VALUES ('2', '0');  

INSERT INTO `table2(id, a)` VALUES ('2', '3');  

INSERT INTO `table2(id, a)` VALUES ('1', '4');  

这里需要返回

Sql代码  

id     a  

-----------  

1   |0,1,4  

2   |0,3  

那么我们可以通过该函数来获取

Sql代码  

SELECT id, GROUP_CONCAT(a)  

FROM table2  

GROUP BY id;  

来看看GROUP_CONCAT语法：

Sql代码  

GROUP_CONCAT([DISTINCT] expr [,expr ...]  

             [ORDER BY {unsigned_integer | col_name | expr}  

                 [ASC | DESC] [,col_name ...]]  

             [SEPARATOR str_val])  

参考该函数的doc ：可以DISTINCT去重， ORDER BY排序，SEPARATOR 来指定分隔符(默认为“,”)如有下面数据

Java代码  

INSERT INTO `table2(id, a)` VALUES ('1', '0');  

INSERT INTO `table2(id, a)` VALUES ('1', '1');  

INSERT INTO `table2(id, a)` VALUES ('2', '0');  

INSERT INTO `table2(id, a)` VALUES ('2', '3');  

INSERT INTO `table2(id, a)` VALUES ('1', '4');  

INSERT INTO `table2(id, a)` VALUES ('2', '3');  

我们需要显示出来的按照a降序、不能重复：

Sql代码  

SELECT id, GROUP_CONCAT(DISTINCT a ORDER BY a DESC SEPARATOR '-')  

FROM table2  

GROUP BY id;  

这样输出结果：

Sql代码  

id  a  

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

1   4-1-0  

2   3-0  

 有了这个函数我们就可以处理一些业务上的事情了，比如现在有两张表其中一张table3的id一对多与另一张表table4的rid关联，现在要统计ipad和mac的具体版本，那么我们就可以直接用sql实现了

Java代码  

-- table3(id,  name)  

INSERT INTO `table3(id, name)` VALUES ('1', 'ipad');  

INSERT INTO `table3(id, name)` VALUES ('2', 'mac');  

  

-- table4(id,  rid,  name)  

INSERT INTO `table4(id, rid, name)` VALUES ('1', '1', 'ipad1');  

INSERT INTO `table4(id, rid, name)` VALUES ('2', '1', 'ipad2');  

INSERT INTO `table4(id, rid, name)` VALUES ('3', '1', 'ipad3');  

INSERT INTO `table4(id, rid, name)` VALUES ('4', '2', 'pro');  

INSERT INTO `table4(id, rid, name)` VALUES ('5', '2', 'air');  

INSERT INTO `table4(id, rid, name)` VALUES ('6', '2', 'mini');  

Sql代码  

SELECT   

    a.id,  

    a.name,  

    GROUP_CONCAT(b.name) as version  

FROM table3 a JOIN table4 b ON a.id = b.rid  

GROUP BY a.id;   

  

---  

id  name    version  

1   ipad    ipad1,ipad2,ipad3  

2   mac pro,air,mini  

 

注意事项：

1、连接的长度受group_concat_max_len参数限制，也就是说这个返回这个长度不是所有都会返回，但是默认为1024也很长了，当然具体可能会到当前concat字段的类型限制同时和max_allowed_packet的限制

2、连接返回二进制和非二进制string，依赖当前连接的类型。有可能超过512个后就返回TEXT或BLOB。如果连接的是int或其他最好先转成Char，如使用函数CAST(expr AS type), CONVERT(expr,type)，见CAST文档

Java代码  

-- CAST  

SELECT CAST(id as CHAR) FROM table4;  

-- Convert  

SELECT Convert(id, CHAR) FROM table4;  

 

bitsCN.com