未加星标

Applying MySQL’s GROUP_CONCAT Function to Multiple Columns

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

In last month’s Why You Should be Using the mysql GROUP_CONCAT Function article, I highlighted some of the benefits to using MySQL’s GROUP_CONCAT function. What was missing from that introduction was the full breadth of what the GROUP_CONCAT function can do. So this follow-up will delve into the full syntax and provide some guidance on how to use the GROUP_CONCAT function to chain together data from multiple columns in a way that’s both easy to read and parse with a script or application.

A Syntax Primer

In order to reap the GROUP_CONCAT function’s full benefit, it helps to have a general understanding of what it can do. As you look over the full syntax, I’d like to draw your attention to the following points:

The optional extra expressions. The ORDER BY clause. The SEPARATOR argument.

Here they are within the syntax definition:

GROUP_CONCAT([DISTINCT] expr [,expr ...] (1) [ORDER BY (2){unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val] (3)) Adding a Second Column To the “Multiple Horror Movie Rentals” Query

The main query of the Why You Should be Using the MySQL GROUP_CONCAT Function article returned a list of customers who had rented three or more horror movies. The GROUP_CONCAT function was utilized to concatenate the list of movie titles. We can add a second column, such as the film ID, to the list as another argument to the function. Arguments can be any type of expression, including string literals, so you can include a delimiter of your choice between the two fields (I went with a comma). Note that this delimiter is different than the function delimiter, which separates each grouped expression i.e. “film_id, title - delimiter - film_id, title - delimiter - film_id, title - delimiter etc…”. Since it also defaults to a comma, we can change it to something other than our field delimiter. For that I chose the vertical bar ( | ) character.

Related Articles MySQL Numeric Overflow Gotcha An Overview of the MySQL Performance Schema MySQL Date Gotchas Importing Into MySQL from Other Databases

Here is the revised query with the updated GROUP_CONCAT line in red, along with the results:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer, A.phone, date(R.rental_date) AS rental_date, GROUP_CONCAT(F.film_id, ',', F.title SEPARATOR '|') AS 'ids & titles', COUNT(*) AS rentals_count FROM sakila.rental R LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id LEFT JOIN sakila.film F ON I.film_id = F.film_id LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id LEFT JOIN sakila.category C ON FC.category_id = C.category_id LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id LEFT JOIN sakila.address A ON CU.address_id = A.address_id WHERE C.name = "Horror" GROUP BY R.customer_id HAVING rentals_count >= 3 ORDER BY customer, title, rental_date DESC; customer phone rental_date ids & titles rentals_count -------------------------------------------------------------------------------------------------------------------------------- ADAM, NATHANIEL 111177206479 2005-08-22 334,FREDDY STORM|24,ANALYZE HOOSIERS|854,STRANGERS GRAFFITI 3 ANDREW, JOSE 961370847344 2005-08-02 527,LOLA AGENT|415,HIGH ENCINO|313,FIDELITY DEVIL|275,EGYPT TENENBAUMS 4 AQUINO, OSCAR 474047727727 2005-07-28 4,AFFAIR PREJUDICE|258,DRUMS DYNAMITE|856,STREETCAR INTENTIONS|275,EGYPT TENENBAUMS 4 ARTIS, CARL 20064292617 2005-07-27 313,FIDELITY DEVIL|749,RULES HUMAN|92,BOWFINGER GABLES|995,YENTL IDAHO 4 BARBEE, CLAYTON 380077794770 2005-05-26 65,BEHAVIOR RUNAWAY|870,SWARM GOLD|535,LOVE SUICIDES 3 etc... Handling NULL Values

As stated in the MySQL docs, by default, GROUP_CONCAT “returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.”

Special care may be required in the handling of Null values if:

you want to include null values in the expression you don’t want extra commas

To get a taste of GROUP_CONCAT’s handling of Nulls, we can query the rental table. The return_date column is Null while a customer still has a movie in their possession, i.e. has rented a movie but has not yet returned it.

First, we’ll omit the return_date column so that no Null values are passed to the GROUP_CONCAT function:

SELECT CONCAT(C.last_name, ', ', C.first_name) AS customer, GROUP_CONCAT(F.title, ',', date(rental_date) ORDER BY rental_date DESC, title SEPARATOR '|') AS rental_history FROM rental R INNER JOIN customer C ON R.customer_id = C.customer_id INNER JOIN address A ON C.address_id = A.address_id INNER JOIN inventory I ON R.inventory_id = I.inventory_id INNER JOIN film F ON I.film_id = F.film_id GROUP BY R.customer_id ORDER BY customer;

We only need to focus on the first row because RAFAEL ABNEY just happens to have a movie out.

Customer rental_history --------------------------------------------------------------------------------------------------------------------------- ABNEY, RAFAEL CONVERSATION DOWNHILL,2006-02-14|FREDDY STORM,2005-08-23|CLASH FREDDY,2005-08-22|BLACKOUT PRIVATE...

Make a mental note of the first movie, CONVERSATION DOWNHILL, and watch what happens to the results when we add the return_date column to the GROUP_CONCAT field list:

SELECT CONCAT(C.last_name, ', ', C.first_name) AS customer, GROUP_CONCAT(F.title, ',', date(rental_date) , ',', date(return_date) ORDER BY rental_date DESC, title SEPARATOR '|') AS rental_history FROM rental R INNER JOIN customer C ON R.customer_id = C.customer_id INNER JOIN address A ON C.address_id = A.address_id INNER JOIN inventory I ON R.inventory_id = I.inventory_id INNER JOIN film F ON I.film_id = F.film_id GROUP BY R.customer_id ORDER BY customer;

No more CONVERSATION DOWNHILL!

Customer rental_history -----------------------------------

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLTICUAUEPASUUICUTHIG
分页:12
转载请注明
本文标题:Applying MySQL’s GROUP_CONCAT Function to Multiple Columns
本站链接:http://www.codesec.net/view/522832.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(50)