Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use GROUP_CONCAT in the mysql function. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

One of the mysql functions-- GROUP_CONCAT

The complete syntax is as follows:

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

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [, col...]]

[SEPARATOR str_val])

This function is added in MySQL 4. 1. Function returns a string result that is composed of value concatenations in the group:

Mysql > SELECT student_name

-> GROUP_CONCAT (test_score)

-> FROM student

-> GROUP BY student_name

Or

Mysql > SELECT student_name

-> GROUP_CONCAT (DISTINCT test_score

-> ORDER BY test_score DESC SEPARATOR "")

-> FROM student

-> GROUP BY student_name

In MySQL, you can get the join value of the combination of expressions. Duplicate values can be excluded by using DISTINCT. If you want to sort the values in the results, you can use the ORDER BY clause. To sort in reverse order, you can add a DESC (decreasing descending) keyword after the column name used for sorting in the ORDER BY clause. The default is ascending order; this can also be explicitly specified by using the ASC keyword. SEPARATOR is a string value that is used to insert into the result value. The default is a comma (,). You can completely remove this delimiter by specifying SEPARATOR "". In your configuration, use the variable group_concat_max_len to set a maximum length. The syntax executed at run time is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer

If the maximum length is set, the resulting value is cut to this maximum length. The GROUP_CONCAT () function is a basic LIST () function supported by the enhanced Sybase SQL Anywhere. If there is only one column and no other options are specified, GROUP_CONCAT () is backward compatible with the extremely restricted LIST () function. LIST () has a default sort order.

Example (translator's Note):

Mysql > CREATE TABLE `ta` (

-> `id`smallint (5) unsigned NOT NULL default'0'

-> `name` char (60) NOT NULL default''

-> KEY `id` (`id`)

->) TYPE=MyISAM

Query OK, 0 rows affected (0.02 sec)

Mysql > INSERT INTO `ta` VALUES ("1", "a"), ("1", "b")

-> ("1", "c"), ("1", "d"), ("2", "a")

-> ("2", "b"), ("2", "c"), ("3", "d")

Query OK, 8 rows affected (0.03 sec)

Records: 8 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM `ta`

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 1 | b | |

| | 1 | c |

| | 1 | d | |

| | 2 | a |

| | 2 | b | |

| | 2 | c |

| | 3 | d | |

+-+ +

8 rows in set (0.00 sec)

Mysql > SELECT `id`

-> GROUP_CONCAT (`name`)

-> FROM `ta`

-> GROUP BY `id`

+-+-

| | id | GROUP_CONCAT (`name`) |

+-+-

| | 1 | a c b d |

| | 2 | a c b |

| | 3 | d | |

+-+-

3 rows in set (0.03 sec)

# SEPARATOR defaults to a space instead of a comma

Mysql > SELECT `id`

-> GROUP_CONCAT (DISTINCT `name`

-> ORDER BY `name` DESC SEPARATOR ",") AS Result

-> FROM `ta`

-> GROUP BY `id`

+-+ +

| | id | Result |

+-+ +

| | 1 | dforce _ creb _ a |

| | 2 | cjorb _ a |

| | 3 | d | |

+-+ +

3 rows in set (0.00 sec)

* the above results are tested in MySQL 4.1

This is how the GROUP_CONCAT in the mysql function shared by Xiaobian is used. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.