mysql日常查询时，经常会需要在group by分组逻辑中再“嵌套”一层分组，从而更好的展示数据。
但是group by 本身并不支持嵌套。。。幸好有另一个函数group_concat() 可以解决这个问题。
再搭配字符分隔截取函数 substring_index(),可以很好的展示想要的数据

为了方便理解, 我构建了一些数据用于测试,并模拟了一个简单需求, 用来说明2个函数的用法，若嫌麻烦，可以直接到 “3 解决过程” 中查看使用示例

内容大纲

1 测试表构建与数据导入

1.1 表结构

1.2 测试表创建语句

DROP TABLE IF EXISTS test_tel;
CREATE TABLE test_tel  (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  dest_number varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '号码',
  province varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '省份',
  PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1.3 测试数据导入

(手机号码皆为测试数据，不具有真实性)

INSERT INTO test_tel (dest_number,province) VALUES ('19946250000','上海'),('18918150000','上海'),('18019370000','上海'),('18016000000','上海'),('18930450000','上海'),('18021000000','上海'),('17717350000','上海'),('15316660000','上海'),('17301780000','上海'),('19946220000','上海'),('18087060000','云南'),('15394990000','云南'),('13330500000','云南'),('17787060000','云南'),('18182970000','云南'),('18987760000','云南'),('13312550000','云南'),('18087720000','云南'),('18187320000','云南'),('15398640000','云南'),('18911490000','北京'),('17710230000','北京'),('18910030000','北京'),('15300220000','北京'),('15311410000','北京'),('13381100000','北京'),('18911920000','北京'),('17718410000','北京'),('18001270000','北京'),('15311410000','北京'),('17790050000','吉林'),('18043560000','吉林'),('17390950000','吉林'),('18946630000','吉林'),('18943300000','吉林'),('13331640000','吉林'),('15330600000','吉林'),('18946680000','吉林'),('15354610000','吉林'),('18043120000','吉林'),('19983860000','四川'),('18090410000','四川'),('17302880000','四川'),('15308290000','四川'),('18090430000','四川'),('13340710000','四川'),('18981160000','四川'),('17781770000','四川'),('18081190000','四川'),('18190860000','四川'),('18002080000','天津'),('13302120000','天津'),('18002080000','天津'),('17720160000','天津'),('13370300000','天津'),('15302090000','天津'),('15320160000','天津'),('13389930000','天津'),('18002080000','天津'),('18920330000','天津'),('18995290000','宁夏'),('18152460000','宁夏'),('18195480000','宁夏'),('17395000000','宁夏'),('17711840000','宁夏'),('17752410000','宁夏'),('18195520000','宁夏'),('17309500000','宁夏'),('18169560000','宁夏'),('18169090000','宁夏'),('18956600000','安徽'),('13335660000','安徽'),('18956620000','安徽'),('18905660000','安徽'),('18056610000','安徽'),('13399670000','安徽'),('18019990000','安徽'),('13339280000','安徽'),('18130070000','安徽'),('18956620000','安徽'),('15315800000','山东'),('15345450000','山东'),('17753250000','山东'),('13375500000','山东'),('17753850000','山东'),('13361390000','山东'),('13396260000','山东'),('18105340000','山东'),('15376170000','山东'),('15315880000','山东'),('19135120000','山西'),('13383590000','山西'),('15383470000','山西'),('18035130000','山西'),('18003510000','山西'),('18035990000','山西'),('18903510000','山西'),('13393580000','山西'),('18903590000','山西'),('18903430000','山西'),('18022150000','广东'),('15362150000','广东'),('13326660000','广东'),('15398820000','广东'),('18024080000','广东'),('17765920000','广东'),('18188600000','广东'),('18928090000','广东'),('18925360000','广东'),('18988590000','广东'),('18176290000','广西'),('19978120000','广西'),('13367660000','广西'),('17777570000','广西'),('18977000000','广西'),('19148050000','广西'),('19978740000','广西'),('18078480000','广西'),('18978280000','广西'),('18174990000','广西')

导入数据举例:

2 需求与分析

需求:
在所有省份中各自随机抽取3个号码，列举出来

分析:
日常使用group by分组时，字段若要参与，一般有2种方式：

①要么将字段加入group by内部作为分组的依据，
②要么对该字段使用聚合函数处理

若采用①的方式, 则省份和号码都会成为分组依据, 与需求矛盾 ( 需求中只需要 "省份’ 这一个分组依据)
若采用②的方式, 则每个省对应的号码无法全部列出, 只能被聚合

3 解决过程:

可以在上述②的基础上进行改进, 使用group_concat 函数,将多行号码"聚合"为一行字符串进行展示

sql如下:

SELECT
    province,
  group_concat( dest_number ORDER BY rand()) AS result 
FROM
    test_tel 
GROUP BY
    province;

执行结果

此时虽然实现了简单的分组展示需求, 但是仍然没有解决随机抽取的问题
于是需要用到 substring_index 函数对上述这一长串号码进行分段截取的处理

sql如下:

SELECT
    province,
    substring_index( group_concat( dest_number ORDER BY rand( ) ), ',', 3 ) AS result 
FROM
    test_tel 
GROUP BY
    province;

执行结果:
(由于使用随即排序, 每次结果都可能不相同)

至此,需求都已实现,
此时再回过头简单介绍下2个函数的使用方法

4 group_concat函数

group_concat其实就是将分组后, 对各组内部的"多行数据(逻辑上)"进行处理, 拼接成"一行数据",并最终展示出来

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [ ‘分隔符’] )
分隔符若不指定,则缺省为一个逗号。

上述例子中, 使用group_concat 针对各分组(省份)内部的"多行手机号码", 使用order by rand()随机排序后,由默认的分隔符拼接成"一行数据"

5 substring_index函数

substring_index函数其实就是个按照关键字截取字符串的函数, 日常很实用

语法: substring_index(str, separator,return)
str : 带截取的字段
Separator：分隔符，函数将根据此字符，将str分割成数段
return：需要返回多少段数据，
若return>0, 则从左往右计数
若return<0, 则从右往左计数

例如之前例子中，使用substring_index将group_concat处理后的一整行数据作为待操作数据，使用逗号分隔，最后返回前三个号码（从左往右计数）

6 需求拓展

统计出测试表中号码前3位分别有哪些省份，并按照省份名排序

sql：

SELECT
    left(dest_number,3) AS '号码前3位',
    group_concat( distinct province  ) AS '省份'
FROM
    test_tel 
GROUP BY
    left(dest_number,3)

执行结果

打赏