mysql函数实现省市的分组
来源:互联网 发布:手机电池修复软件下载 编辑:程序博客网 时间:2024/05/22 12:15
//城市表
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)
-------------------------------------------------------------------------------------------------------------------
补充知识:(转:http://blog.csdn.net/a355586533/article/details/52085905)
MySQL中group_concat函数
完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
基本查询
- 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字段的值打印在一行,逗号分隔(默认)
- 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字段的值打印在一行,分号分隔
- 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字段的值打印在一行,
逗号分隔
- 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排倒序
- 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)
拼接字符串(http://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)
- mysql函数实现省市的分组
- Mysql| Mysql分组函数
- 省市联动的实现
- MYSQL 分组合并函数
- MYSQL 分组合并函数
- MYSQL 分组合并函数
- MYSQL 分组合并函数
- HIBERNATE中分组函数的实现
- MySQL 使用 INTERVAL() 函数 实现数据按区间分组
- mysql实现分组排序
- java 实现 分组 函数
- java 实现 分组 函数
- EasyUI+MySQL+Struts2实现省市二级联动
- js实现的省市联动
- 省市联动的简单实现
- 省市县联动的实现。
- mysql聚合函数和分组
- mysql分组查询聚合函数
- 风险管理图与四六级步骤
- 【深入Java虚拟机】之二:Class类文件结构
- java反射
- 【Maven】2、Maven安装
- 线程间通信学习笔记
- mysql函数实现省市的分组
- 如何在命令行程序中运行Java程序
- 不允许创建临时变量,交换两个数的内容
- Collection接口
- 【深入Java虚拟机】之三:类初始化
- 单调递增子序列(二)
- TypeLite (一) -- 安装及使用
- cf 758 B. Blown Garland模拟
- BezierPath精讲