示例1
- 连接起来的字段如果是int型，一定要转换成char再拼起来
select group_concat(CAST(id as char)) from t_dep; -- 返回逗号隔开的串
select year,month GROUP_CONCAT(volumn) from magazine group by year,month order by year desc, month desc;
year month GROUP_CONCAT(volumn)
2010 12 1,2,3,4
2010 11 1,2,3,4,5
示例2
SELECT
ticket_id,
GROUP_CONCAT(DISTINCT odd_id ORDER BY odd_id ASC) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
HAVING oddsconcat = .....
示例3
在一个表ta中数据为：
+----+------+
| id | name |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | d |
+----+------+
从里面取数据要求完成如下效果:
+----+--------+
| id | name |
+----+--------+
| 1 | a,b,c,d |
| 2 | a,b,c |
| 3 | d |
+----+--------+
SELECT `id`,GROUP_CONCAT(`name` ORDER BY `name` DESC SEPARATOR ',') AS name FROM `ta` GROUP BY `id`;
示例4
-
用法：
SUBSTRING_INDEX(str,delim,count)返回字符串 str 中在第 count 个出现的分隔符 delim 之前的子串。如果 count 是一个正数，返回从最后的(从左边开始计数)分隔符到左边所有字符。如果 count 是负数，返回从最后的(从右边开始计数)分隔符到右边所有字符
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'-- 在上面一个例子中，我们使用“,”来分隔数据，取前面2个，就可以这样写： SELECT `id`,SUBSTRING_INDEX(GROUP_CONCAT(`name` ORDER BY `name` DESC SEPARATOR ','),',',2) AS `name` FROM `ta` GROUP BY `id`;In general, to select the N-th value from a string VALUES that contains values separated by delimiter DELIM, you have to use:
SUBSTRING_INDEX( SUBSTRING_INDEX( VALUES, DELIM, N ), DELIM, -1 )
