MySQL中concat_ws函数应用实例
来源：互联网 发布：美国留学知乎 编辑：程序博客网 时间：2024/04/30 02:14
本文通过实例介绍了MySQL中的concat_ws函数的使用方法，比如select concat_ws(',','11','22','33')。
MySQL中concat_ws函数
使用方法：
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
如连接后以逗号分隔
mysql> select arTitle,concat_ws(';',content,createTime,siteID,id) as content from post where uCenterUId = 4
+-------------------------------+
| concat_ws(';',content,createTime,siteID,id) |
+-------------------------------+
| MjM0MzU=;2017-06-13 11:44:22;hxhslm_20170523;3710 |
+-------------------------------+
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)
一对多的分组查询实现：
当查询数据是一对多的时候，我们需要组装的数据格式是一个标题对应多个字段内容的时候，我们需要将多个字段合并到一个字段中（如下图）
这种就需要使用一下sql实现查询：
select arTitle,group_concat(concat_ws(';',content,createTime,siteID,id)) as content from post where uCenterUId = 4 and isdisplay = 0 group by articleID order by createTime desc
数据查询结果如下截图（content字段中每个数据是用，分割，每个字段是用；分割）
将查询的数据需要在后台进行组装，以方便在也面展示：
封装demo:
/* 获取用户评论列表（App接口） * 参数 * 用户id uCenterUId * 第几页： pno * */ public void getUserPostApp(){ //获取用户id参数 /*int uCenterUId = getParaToInt(0); int pno = getParaToInt(1);*/ String uCenterUId = getPara("uCenterUId"); int pno = getParaToInt("pno"); //根据用户id分页查询该用户对应的文章信息与文章标题 //String sql = "select arTitle,group_concat(concat_ws(';',content,createTime,siteID)) as content from post group by articleID LIMIT 0,8;"; //Db.paginate(); Page<Record> postPage = Db.paginate(pno,8, "select arTitle,group_concat(concat_ws(';',content,createTime,siteID,id)) as content","from post where uCenterUId = ? and isdisplay = 0 group by articleID order by createTime desc",uCenterUId); PostApp postApp = new PostApp(); List<Record> list = postPage.getList(); pageApp pap =null; List aa = new ArrayList<PostApp>(); if(null!=list&&list.size()>0){ for(int i=0;i<list.size();i++){ String content = list.get(i).get("content"); String[] content1 = content.split(","); List<PostA> postAList = new ArrayList<>(); PostA postA = null; for(int j = 0;j<content1.length;j++){ String[] content2 =content1[j].split(";"); if(content2.length==4){ postA = new PostA(); postA.setContent(Base64Utils.decodeData(content2[0])); postA.setTime(content2[1]); postA.setSiteId(content2[2]); postA.setPostId(Integer.parseInt(content2[3])); postAList.add(postA); } } Map map = new HashMap<String,List>(); map.put("arTitle", list.get(i).get("arTitle")); map.put("List", postAList); aa.add(map); } } int pageNumber = postPage.getPageNumber(); int pageSize = postPage.getPageSize(); int totalPage = postPage.getTotalPage(); int totalRow = postPage.getTotalRow(); pap = new pageApp<>(aa, pageNumber, pageSize, totalPage, totalRow); getResponse().setHeader("Access-Control-Allow-Origin", "*");getResponse().setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");getResponse().setHeader("Access-Control-Max-Age", "3600");getResponse().setHeader("Access-Control-Allow-Headers", "Origin, No-Cache, X-Requested-With, If-Modified-Since, Pragma, Last-Modified, Cache-Control, Expires, Content-Type, X-E4M-With");renderJson(pap);获得数据的样例：
{ "lastPage": true, "pageSize": 8, "pageNumber": 1, "list": [ { "arTitle": "袭击事件频发 欧洲安全形势不容乐观", "List": [ { "content": "…`", "time": "2017-06-13 13:40:13", "siteId": "1", "postId": 3728 } ] }, { "arTitle": "青岛面条鱼获丰再现鱼满舱", "List": [ { "content": "rrr", "time": "2017-06-13 13:31:11", "siteId": "1", "postId": 3726 } ] }, { "arTitle": "南非骚乱波及多家华人店铺 中国使馆提醒注意安全", "List": [ { "content": "图图他", "time": "2017-06-13 11:16:45", "siteId": "1", "postId": 3703 }, { "content": "太阳雨u", "time": "2017-06-13 14:52:12", "siteId": "1", "postId": 3759 }, { "content": "发挥好回家", "time": "2017-06-13 14:52:17", "siteId": "1", "postId": 3760 } ] }, { "arTitle": "美国海关启动最严打假 中国货品是主要目标", "List": [ { "content": "123", "time": "2017-06-13 09:29:37", "siteId": "1", "postId": 3673 }, { "content": "123", "time": "2017-06-13 09:45:35", "siteId": "1", "postId": 3674 }, { "content": "123", "time": "2017-06-13 09:46:07", "siteId": "1", "postId": 3675 }, { "content": "123", "time": "2017-06-13 09:46:12", "siteId": "1", "postId": 3676 }, { "content": "123", "time": "2017-06-13 09:46:21", "siteId": "1", "postId": 3677 }, { "content": "123", "time": "2017-06-13 09:48:35", "siteId": "1", "postId": 3678 }, { "content": "123", "time": "2017-06-13 09:48:38", "siteId": "1", "postId": 3679 }, { "content": "123", "time": "2017-06-13 09:48:40", "siteId": "1", "postId": 3680 }, { "content": "123", "time": "2017-06-13 09:53:15", "siteId": "1", "postId": 3682 }, { "content": "123", "time": "2017-06-13 10:05:00", "siteId": "1", "postId": 3685 }, { "content": "123", "time": "2017-06-13 10:11:51", "siteId": "1", "postId": 3686 }, { "content": "日日日", "time": "2017-06-13 10:12:29", "siteId": "1", "postId": 3688 }, { "content": "图图他", "time": "2017-06-13 10:19:46", "siteId": "1", "postId": 3692 }, { "content": "人人让她", "time": "2017-06-13 10:35:13", "siteId": "1", "postId": 3695 }, { "content": "rryyy", "time": "2017-06-13 11:56:30", "siteId": "1", "postId": 3719 }, { "content": "rryyy", "time": "2017-06-13 11:56:34", "siteId": "1", "postId": 3720 }, { "content": "rryyy", "time": "2017-06-13 11:56:37", "siteId": "1", "postId": 3721 }, { "content": "热饿", "time": "2017-06-13 14:51:37", "siteId": "1", "postId": 3758 }, { "content": "关羽", "time": "2017-06-13 16:41:18", "siteId": "1", "postId": 3779 } ] }, { "arTitle": "华裔候选人陈文雄法国议会选举首轮投票大幅胜出", "List": [ { "content": "分割哈哈", "time": "2017-06-13 08:51:44", "siteId": "1", "postId": 3667 } ] }, { "arTitle": "可以改变世界，但“中国吃货”不背锅!", "List": [ { "content": "嗯嗯哒", "time": "2017-06-12 19:36:43", "siteId": "1", "postId": 3648 }, { "content": "让他英语", "time": "2017-06-12 19:36:48", "siteId": "1", "postId": 3649 }, { "content": "让体育UIi", "time": "2017-06-12 19:36:53", "siteId": "1", "postId": 3650 }, { "content": "天谕", "time": "2017-06-12 19:36:58", "siteId": "1", "postId": 3651 }, { "content": "分割哈哈", "time": "2017-06-13 14:13:03", "siteId": "1", "postId": 3739 }, { "content": "如图ui", "time": "2017-06-13 14:13:07", "siteId": "1", "postId": 3740 }, { "content": "体育局", "time": "2017-06-13 14:13:10", "siteId": "1", "postId": 3741 }, { "content": "雨ii", "time": "2017-06-13 14:13:15", "siteId": "1", "postId": 3742 }, { "content": "让她雨ii哦", "time": "2017-06-13 14:13:19", "siteId": "1", "postId": 3743 } ] }, { "arTitle": "南非布隆方丹华人店铺遭暴民哄抢 商铺暂停营业", "List": [ { "content": "地负海涵", "time": "2017-06-12 19:34:22", "siteId": "1", "postId": 3647 } ] }, { "arTitle": "中国女足第一美女是谁？", "List": [ { "content": "额", "time": "2017-06-12 19:17:00", "siteId": "1", "postId": 3646 } ] } ], "firstPage": true, "totalRow": 8, "totalPage": 1}阅读全文
0 0
- MySQL中concat_ws函数应用实例
- MySQL中concat_ws函数
- MySQL中函数CONCAT&CONCAT_WS及GROUP_CONCAT
- MySQL中concat函数,concat_ws函数,group_concat函数,repeat()函数
- MySql中的几个小函数的应用(group_concat,concat_ws)
- MySQL中concat函数（连接字符串）、concat_ws函数
- MySQL中concat函数,concat_ws,group_concat,repeat() 函数用法
- MySQL中concat concat_ws repeat函数等操作
- mysql中CONCAT_WS的用法
- mysql group_concat函数和concat_ws 函数 详解
- MYSQL中的concat()函数和concat_ws()函数
- MySQL concat、concat_ws、group_concat函数用法
- mysql字符串函数CONCAT_WS与CONCAT()用法
- MySQL连接字符串函数concat, concat_ws, group_concat
- MySQL的concat、concat_ws、group_concat函数用法
- mysql字符串拼接函数:concat和concat_ws
- CONCAT_WS函数
- concat和concat_ws()区别Oracle及MySQL的几个实用字符串函数 concat_ws
- [Codeforces 235E/236B] Number Challenge 莫比乌斯反演
- java之yield(),sleep(),wait()区别详解-备忘笔记
- JDBC-Java连接数据库
- ios UIView 转 UIImage，解决模糊失真问题
- Cordova 4.0+ CordovaWebView问题
- MySQL中concat_ws函数应用实例
- 【数据结构与算法】计数排序
- 程序设计语言范型
- leetcode.136.Single Number
- A/B测试终极指南（翻译）
- 负载均衡的配置
- 相关事件及event.keyCode 按键 值/弹窗
- centos7安装scrapy
- HNCU 1328: 算法2-18~2-19：双向循环链表
