精华内容
下载资源
问答
  • 更多相关内容
  • 即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL...
  • MySQL中将一逗号分隔的值行转列

    万次阅读 2019-01-26 16:46:08
    即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求: 得到所有的不重复的值,如 value ...
    • 前言
      有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表
    pkvalue
    1ET,AT
    2AT,BT
    3AT,DT
    4DT,CT,AT

    一般有这两种常见需求(测试数据见文末)

    1. 得到所有的不重复的值,如
    value
    AT
    BT
    CT
    DT
    ET

    SQL如下:

    select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
    from
    	(select group_concat(distinct `value`) as col from `row_to_col`) as a
    join
    	mysql.help_topic as b
    on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)
    
    1. 显示每个值及其对应主键,如
    pkvalue
    1ET
    1AT
    2AT
    2BT
    3AT
    3DT
    4DT
    4CT
    4AT

    SQL如下:

    select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)
    from
    	(select `value` as col,pk from `row_to_col`) as a
    join
    	mysql.help_topic as b
    on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)
    
    • 实现思路:
      需求1:
       1. 通过group_concat函数将value列的值拼接成一个逗号隔开的字符串,然后通过substring_index函数对字符串进行截取
       2. 通过substring_index函数特性,我们就需要知道字符串有多少个逗号,并且要告诉每个逗号的位置
       3. 逗号个数=char_length(字符串)-char_length(replace(字符串,’,’,’’))
       4. 逗号位置=mysql.help_topic.id < 逗号个数[+1]
       5. 最后通过distinct函数将截取后的单个值进行去重
      注意:
       1. mysql.help_topic表的自增id是从0开始,所以在进行截取时要对id进行+1。见: substring_index(a.col,’,’,b.help_topic_id+1)
       2. value列最后一个字符不是逗号时:逗号个数+1是为了截取时不漏掉最后一个逗号后的值,即: char_length(a.col) - char_length(replace(a.col,’,’,’’))+1;
        value列最后一个字符是逗号时:逗号个数就不需要+1了,直接:char_length(a.col) - char_length(replace(a.col,’,’,’’))
       3. 因为截取时id要+1,所以在连接时取的< ,而不是 <= 。见:b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,’,’,’’))[+1])
       4. mysql.help_topic(mysql version: 5.7.21-1)表的自增id,最大值为636。如果group_concat后的字符串中逗号个数大于该值,需要自己单独处理自增id的值

      需求2:思路基本与需求1同,只是最后的查询不一样

    • 涉及到的函数:
      length:返回字符串所占的字节数,是计算字段的长度。一个汉字或是中文符号是算三个字符,一个数字或字母或英文符号算一个字符。
      char_length:返回字符串所占的字符数,不管汉字还是数字或者是字母或者符号(不分中英文)都算是一个字符。
      replace(str,old_string,new_string): 将字符串中str中所有的old_string替换成new_string。
      substring_index(被截取字段,关键字,关键字出现的次数):截取字符串。如果关键字出现的次数是负数 , 则是从后倒数,到字符串结束。
      group_concat([DISTINCT] 要连接的字段(可多个,逗号隔开) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])
      将group by产生的同一个分组中的值连接起来,返回一个字符串结果。分隔符,默认逗号。

    • 测试数据:

    DROP TABLE IF EXISTS `row_to_col`;
    CREATE TABLE `row_to_col` (
      `pk` int(11) NOT NULL AUTO_INCREMENT,
      `value` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`pk`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
    
    INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
    INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
    INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
    INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');
    

    参考:
    https://blog.csdn.net/liuzhoulong/article/details/51729168
    https://blog.csdn.net/ldl22847/article/details/47609727

    展开全文
  • mysql 逗号分隔符 行转列转行

    千次阅读 2021-01-19 00:37:14
    行转列,直接使用 group_concat 分组函数select id,group_concat(name) from aa group by id;由于这个是分组函数,还可以distinct去重select id,group_concat(distinct name) from aa group by id;还可以指定分割...

    行转列,

    直接使用 group_concat  分组函数

    select id,group_concat(name) from aa group by id;

    由于这个是分组函数,还可以用distinct去重

    select id,group_concat(distinct name) from aa group by id;

    还可以指定分割符,默认是,耗

    select id,group_concat(name separator ';') from aa group by id;

    最后他还能排序

    select id,group_concat(name order by name desc) from aa group by id;

    列转行,把group_concat转过来的东西,再变回去,使用 find_in_set 需要做左联

    SELECT

    Books.BookId,

    Books.Book,

    BookAuthors.AuthorId,

    BookAuthors.Author

    FROM Books

    LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

    参考

    http://stackoverflow.com/questions/14591779/how-to-represent-cross-apply-and-split-string-in-mysql

    0.00 avg. rating (0% score) - 0 votes

    展开全文
  • oracle和mysql转行用逗号分隔函数

    千次阅读 2022-02-15 15:17:11
    简单语法:wmsys.wm_concat...合并其它列,role_name列用逗号隔开: select u.user_id, u.dept_id, u.login_name, u.user_name, wmsys.wm_concat(r.role_name) as role_name from sys_user u left join sys_user_r

    一、 Oracle

    oracle11g以前旧版本语法:wmsys.wm_concat(列名)

    select wmsys.wm_concat(username) FROM students;-- 将username列记录值用逗号连接起来
    

    以其它列分组,role_name列打印成一行用逗号隔开:

    select u.user_id, u.dept_id, u.login_name, u.user_name, wmsys.wm_concat(r.role_name) as role_name from sys_user u
    		left join sys_user_role ur on ur.user_id = u.user_id
    		left join sys_role r on r.role_id = ur.role_id
    		where u.del_flag = '0'
    		group by u.user_id, u.dept_id, u.login_name, u.user_name
    		order by u.create_time desc
    

    oracle11g以上版本不支持wmsys.wm_concat,使用listagg() WITHIN GROUP ()函数代替:

    select u.user_id, u.dept_id, u.login_name, u.user_name, listagg(r.role_name,',') within group(order by r.role_name) as role_name from sys_user u
    		left join sys_user_role ur on ur.user_id = u.user_id
    		left join sys_role r on r.role_id = ur.role_id
    		where u.del_flag = '0'
    		group by u.user_id, u.dept_id, u.login_name, u.user_name
    		order by u.create_time desc
    

    二、Mysql中group_concat函数

    完整的语法:

    group_concat([DISTINCT] 要连接的字段 [Order BY  排序字段 ASC/DESC] [Separator '分隔符'])
    
    1. 以其它列分组,role_name列打印成一行用逗号隔开(默认逗号分隔):
    select user_id,group_concat(role_name) as role_name from sys_user group by user_id;
    

    ±-----±-------------------+
    | user_id|role_name |
    ±-----±-------------------+
    |1 | 安全员,施工人员,安全员|
    |2 | 项目经理,现场监理 |
    |3 | 总监|
    ±-----±-------------------+

    1. role_name列打印成一行,用分号分隔
    select user_id,group_concat(role_name separator ';') as role_name from sys_user group by user_id; 
    

    ±-----±-------------------+
    | user_id|role_name |
    ±-----±-------------------+
    |1 | 安全员;施工人员,安全员|
    |2 | 项目经理;现场监理 |
    |3 | 总监|
    ±-----±-------------------+

    1. role_name列打印成一行,去重后用逗号隔开
    select user_id,group_concat(distinct  role_name) as role_name from sys_user group by user_id; 
    

    ±-----±-------------------+
    | user_id|role_name |
    ±-----±-------------------+
    |1 | 安全员,施工人员|
    |2 | 项目经理,现场监理 |
    |3 | 总监|
    ±-----±-------------------+

    1. ole_name列打印成一行,逗号分隔,倒序排列
    select user_id,group_concat(role_name order by role_name desc) as role_name from sys_user group by user_id; 
    

    ±-----±-------------------+
    | user_id|role_name |
    ±-----±-------------------+
    |1 | 施工人员,安全员|
    |2 | 现场监理,项目经理 |
    |3 | 总监|
    ±-----±-------------------+

    参考博文:https://www.cnblogs.com/yingmo/p/6148360.html

    展开全文
  • 1:定义存储过程,用于分隔字符串 DELIMITER $$ USE `mess`$$ DROP PROCEDURE IF EXISTS `splitString`$$ CREATE DEFINER=`root`@`%` PROCEDURE `splitString`(IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5...
  • MySQL 逗号分隔成多行

    千次阅读 2022-03-14 10:43:15
    MySQL 转行、MySQL逗号拆分多行
  • 行转列逗号隔开&逗号隔开列转行

    万次阅读 2016-01-27 15:47:06
    ORACLE: SQL> create table t(id int,name varchar2(30)); 表已创建。... insert into t values...已创建 1 。 SQL> insert into t values(1,'b'); 已创建 1 。 SQL> insert into t values(1,'c...
  • mysql多行合并成一,值用逗号隔开

    千次阅读 2020-06-02 16:25:41
    SELECT t.id,GROUP_CONCAT(t.name SEPARATOR ',') FROM USER t GROUP BY t.id GROUP_CONCAT()中的值为你要合并的数据的字段名;...必须要GROUP BY 语句来进行分组管理,不然所有的数据都会被合并成一条记录 ...
  • Oracle逗号分隔转行实现方法

    千次阅读 2021-05-06 01:45:00
    Oracle逗号分隔转行实现方法那么,现在如果给出一个权限编号,要去检索出后这个权限的用户集合,就会需要在逗号分隔的多个权限编号中去匹配给出的这个权限编号。如果使用like去做,一则效率不高,二则匹配不精确...
  • SELECT GROUP_CONCAT( label.label_name) FROM mooc_label_relevance relevance
  • 展开全部MYSQL列中的数据查询命令:SELECT *FROMtask_detatilsWHERE FIND_IN_SET( ‘1’62616964757a686964616fe58685e5aeb931333431376532, responsible_user)命令的含义:将含有1的responsible_user数据全部搜索...
  • 1例如:要把如图1的字段拆分图2 select account_id, substring_index(substring_... mysql.help_topic b on b.help_topic_id < (length(a.related_shop_ids) - length(replace(a.related_shop_ids,',',''))+1
  • mysql将查询结果用逗号分隔

    千次阅读 2022-03-04 17:07:53
    使用group_concat函数来实现功能 This function returns a string result with the concatenated non-NULL values from a ...--Mysql官方文档 mysql> SELECT student_name, GROUP_CONCAT(test_score) FROM s
  • 获取到的商品名称用逗号隔开 例如: 苹果,梨,橘子 select count('goods_name') as count from goods; 统计商品的个数 select sum('goods_number') as sum from order;统计订单中商品的数量和 ...
  • GROUP_CONCAT()是MySQL数据库提供的一个函数,通常跟GROUP BY一起,并把查询的字段逗号分隔,只支持字符类型的字段,整数型须要特殊写法。下面是一些使用注意事项:1.int字段的连接陷阱当你group_concat的时候...
  • MySQL查询以逗号隔开的字符串

    千次阅读 2021-12-22 15:56:24
    场景举例 参数为课程(多个),查询选了这些课程的学生 数据库没有分表,只有一个学生表,里面有个字段记录了学生选的所有课程 数据如下: 现在需要查询,选择了美术、语文、体育这三门课程的学生 ...
  • 例如 : 在MySQL 字段中有值为 1,2,3,4,5,6,8,10 99,98,91,95,97 两条数据 而我想查出 两个数据 1 和 95 如果使用 find_in_set 查询 则无法查询 出来 则需要使用mysql 内置 方法 SELECT * FROM pay_...
  • 本文自:https://jingyan.baidu.com/album/29697b91478e91ab20de3c9d.html?picindex=1 mysql中把selct中的数据列表转换成逗号分隔的字符串 标签:rem code ODB sel 图片 test tps nod float 本条技术文章...
  • MySQL字段根据逗号隔开查询

    千次阅读 2022-03-22 16:00:06
           在做统计的时候需要根据...MySQL中的一个字段是一个逗号隔开的字符串,需要匹配逗号前的第一个字段。 select count(*)from cmt_gxxx where SUBSTRING_INDEX(gxfl,',', 1)
  • MySQL 将查询多行结果以逗号分隔组成为一输出在开发过程中,我们常常会遇到查询数据表某个字段的值,查询该字段对应的值一般都是多条记录的,然后我们还需要将查询结果再遍历组装成一个变量,这样就有点繁琐。...
  • 被分割的字段一定是有限而且数量较少的,我们不可能在一个字符串中存储无限多个字符 这个字段所属的表与这个字段关联的表,一定是一对多的关系 比如下面这个表结构所代表的content与tag这两个对象 代码如下: mysql> ...
  • mysql 出表列名,用逗号隔开

    千次阅读 2019-06-25 16:30:25
    SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';
  • MySql查询关联ID为逗号隔开的字符串的数据 1、一种产品的规格不唯一,查看表 2、在规格表中存放对应的规格数据,应该如何查询显示呢?在使用FIND_SET_IN函数的时候先尝试IN函数。例如图,利用in函数,传入每一个ID...
  • mysql逗号分隔List字段多行

    千次阅读 2020-07-03 14:26:10
    具体的逻辑我还没整明白,先记上再说,亲测可用。 1、原表数据 select a1.id,a1.job_depends from job_version_...2、一行转 select a1.id,a1.job_depends,substring_index(substring_index(a1.job_d...
  • MySQL逗号分割字段的转行

    千次阅读 2019-10-03 04:05:58
    前言: 由于很多业务表因为...这种模式下,应用常常需要将这个依据分隔符进行分割,并得到转行的结果。 表数据: ID  Value 1 tiny,small,big 2 small,medium 3 tiny,big ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 43,475
精华内容 17,390
关键字:

mysql行转列用逗号隔开

mysql 订阅