频道栏目
首页 > 数据库 > MySQL > 正文
mysql函数实现省市的分组
2017-03-20 09:28:09      个评论    来源:qq_24888953的博客  
收藏   我要投稿

mysql函数实现省市的分组

//城市表

CREATE TABLE city (
serialId int(11) AUTO_INCREMENT NOT NULL,
cityId varchar(255) NULL,
cityName varchar(255) NULL,
cityUpId varchar(255) NULL,
PRIMARY KEY(serialId)
)
GO

//省份表

CREATE TABLE province (
serialId int(20) AUTO_INCREMENT NOT NULL,
provinceId varchar(255) NOT NULL,
provinceName varchar(255) NOT NULL,
PRIMARY KEY(serialId)
)
GO

//函数

create function x_tojson(x varchar(255) ,y varchar(255) )
returns varchar(1000) CHARACTER SET gbk
begin
return (concat('"',x,y,'"')) ;
end

//sql写法

SELECT di.serialId as dserialId,di.districtName as ddistrictName,
ci.serialId as cserialId,ci.cityName as ccityName,
pr.serialId as pserialId,pr.provinceName as pprovinceName ,group_concat(distinct x_tojson(ci.serialId,ci.cityName) )
FROM district di
LEFT JOIN city ci ON ci.cityId = di.districtUpId
LEFT JOIN province pr ON pr.provinceId = ci.cityUpId
group by cityUpId

//结果集

dserialId ddistrictName cserialId ccityName pserialId pprovinceName group_concat(distinct x_tojson(ci.serialId,ci.cityName) )
------------ ---------------- ------------ ------------ ------------ ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------
1 东城区 1 北京市 1 北京市 "1北京市"
738 玄武区 74 南京市 10 江苏省 "74南京市","75无锡市","76徐州市","77常州市","78苏州市","79南通市","80连云港市","81淮安市","82盐城市","83扬州市","84镇江市","85泰州市","86宿迁市"
844 上城区 87 杭州市 11 浙江省 "87杭州市","88宁波市","89温州市","90嘉兴市","91湖州市","92绍兴市","93金华市","94衢州市","95舟山市","96台州市","97丽水市"
934 瑶海区 98 合肥市 12 安徽省 "98合肥市","99芜湖市","100蚌埠市","101淮南市","102马鞍山市","103淮北市","104铜陵市","105安庆市","106黄山市","107滁州市","108阜阳市","109宿州市","110巢湖市","111六安市","112亳州市","113池州市","114宣城市"
1039 鼓楼区 115 福州市 13 福建省 "115福州市","116厦门市","117莆田市","118三明市","119泉州市","120漳州市","121南平市","122龙岩市","123宁德市"
1124 东湖区 124 南昌市 14 江西省 "124南昌市","125景德镇市","126萍乡市","127九江市","128新余市","129鹰潭市","130赣州市","131吉安市","132宜春市","133抚州市","134上饶市"
1223 历下区 135 济南市 15 山东省 "135济南市","136青岛市","137淄博市","138枣庄市","139东营市","140烟台市","141潍坊市","142济宁市","143泰安市","144威海市","145日照市","146莱芜市","147临沂市","148德州市","149聊城市","150滨州市","151荷泽市"
1363 中原区 152 郑州市 16 河南省 "152郑州市","153开封市","154洛阳市","155平顶山市","156安阳市","157鹤壁市","158新乡市","159焦作市","160濮阳市","161许昌市","162漯河市","163三门峡市","164南阳市","165商丘市","166信阳市","167周口市","168驻马店市"
1523 江岸区 169 武汉市 17 湖北省 "169武汉市","170黄石市","171十堰市","172宜昌市","173襄樊市","174鄂州市","175荆门市","176孝感市","177荆州市","178黄冈市","179咸宁市","180随州市","181恩施土家族苗族自治州","182神农架"
1625 芙蓉区 183 长沙市 18 湖南省 "183长沙市","184株洲市","185湘潭市","186衡阳市","187邵阳市","188岳阳市","189常德市","190张家界市","191益阳市","192郴州市","193永州市","194怀化市","195娄底市","196湘西土家族苗族自治州"
1747 东山区 197 广州市 19 广东省 "197广州市","198韶关市","199深圳市","200珠海市","201汕头市","202佛山市","203江门市","204湛江市","205茂名市","206肇庆市","207惠州市","208梅州市","209汕尾市","210河源市","211阳江市","212清远市","215潮州市","216揭阳市","217云浮市"
19 和平区 2 天津市 2 天津市 "2天津市"

之前DB2函数写过一些梯级提醒,把函数放进去,sql一个分组就可以了。mysql遇到了很多问题。

比如too data to ...字符集的问题,mysql的配置文件改成gbk即可

然后就是,在函数外面包一个group_concat(),之前db2是不需要这个的。

db2函数

CREATE or replace FUNCTION "ADD"(a int ,b int)
returns int
LANGUAGE SQL
BEGIN ATOMIC
DECLARE sum int default 0;
set sum = a + b;
return sum;
END
GO

select add(a,b)

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

补充知识:(转:https://blog.csdn.net/a355586533/article/details/52085905)

MySQL中group_concat函数


完整的语法如下:


group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基本查询

Sql代码 收藏代码
select * from student;


+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔(默认)

Sql代码 收藏代码
select id,group_concat(name) from student group by id;


+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,分号分隔

Java代码 收藏代码
select id,group_concat(name separator ';') from aa group by id;


+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

以id分组,把去冗余的name字段的值打印在一行,


逗号分隔

Sql代码 收藏代码
select id,group_concat(distinct name) from aa group by id;


+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序

Sql代码 收藏代码
select id,group_concat(name order by name desc) from aa group by id;


+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)


拼接字符串(https://www.cnblogs.com/ZDPPU/p/5811976.html)

MySQL中concat函数
使用方法:
CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。

注意:
如果所有参数均为非二进制字符串,则结果为非二进制字符串。
如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如:
SELECT CONCAT(CAST(int_col AS CHAR), char_col)

MySQL的concat函数可以连接一个或者多个字符串,如
mysql> select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)

mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

MySQL的concat函数在连接字符串的时候,只要其中一个是NULL,那么将返回NULL
mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

MySQL中concat_ws函数
使用方法:
CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
注意:
如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

如连接后以逗号分隔
mysql> select concat_ws(',','11','22','33');

+-------------------------------+
| concat_ws(',','11','22','33') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set (0.00 sec)

和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
mysql> select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)

MySQL中group_concat函数
完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基本查询

mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,分号分隔

mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

以id分组,把去冗余的name字段的值打印在一行,

逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

repeat()函数

用来复制字符串,如下'ab'表示要复制的字符串,2表示复制的份数

mysql> select repeat('ab',2);

+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+

1 row in set (0.00 sec)

又如
mysql> select repeat('a',2);

+---------------+
| repeat('a',2) |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)

点击复制链接 与好友分享!回本站首页
上一篇:mysql5.5绿色版服务配置
下一篇:MySQL数据库基础语句操作整理
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站