MySql根据某个字段规则行转列及一些字符串函数的使用

MySql根据某个字段规则行转列及一些字符串函数的使用

基础数据源

1.字符串函数

• group_concat

  该函数返回带有来自一个组的连接的非NULL值的字符串结果，默认逗号连接

  select g.SHOWORDER, group_concat(OASHORTNAME)
  from g_bank g
  where OASHORTNAME is not null
  group by SHOWORDER;
  

• FIND_IN_SET

  官网说明：

  如果字符串str在由N个子字符串组成的字符串列表strlist中，则返回1到N的范围内的值。
  字符串列表是由由字符分隔的子字符串组成的字符串。
  如果第一个参数是常量字符串，第二个参数是SET类型的列，
  那么FIND_IN_SET()函数将优化为使用位算术。
  如果str不在strlist中或者strlist是空字符串，则返回0。
  如果任一参数为NULL则返回NULL。如果第一个参数包含逗号，则此函数无法正常工作。
  mysql> SELECT FIND_IN_SET('b','a,b,c,d');
          -> 2
  

  eg:

  select g.BANKNO ,g.OASHORTNAME ,FIND_IN_SET('农行',OASHORTNAME) as "index" from g_bank g
   where OASHORTNAME is not null ;
  

  

• LOCATE

  第一个语法返回子字符串substr在字符串str中第一次出现的位置。
  第二个语法返回子字符串substr在字符串str中第一次出现的位置:str，从pos位置开始。
  如果substr不在str中，则返回0
  mysql> SELECT LOCATE('bar', 'foobarbar');
          -> 4
  mysql> SELECT LOCATE('xbar', 'foobar');
          -> 0
  mysql> SELECT LOCATE('bar', 'foobarbar', 5);
          -> 7
  此函数是多字节安全的，并且只有在至少一个参数是二进制字符串时才区分大小写
  

2.行转列

• 利用mysql.help_topic

-- 行转列(利用mysql.help_topic)  
SELECT a.BANKNO,
       substring_index(substring_index(a.OASHORTNAME, ',', b.help_topic_id + 1), ',', - 1) OASHORTNAME
FROM g_bank a
    JOIN mysql.help_topic b
    ON b.help_topic_id < (length(a.OASHORTNAME) - length(REPLACE(a.OASHORTNAME, ',', '')) + 1)
ORDER BY a.BANKNO;

3.业务场景

业务系统对接过程中两边数据大行名称不一致，通过对方传过来的数据做银行简称模糊逻辑匹配，匹配本系统信息。
匹配规则如下：
1.建行收款支行–>找到简称建行–>对应大行编码02
2.建设银行收款支行–>找到简称建行–>对应大行编码02
3.建设收款支行–>不匹配

select g.BANKNO,OASHORTNAME
from (
         SELECT a.BANKNO,
                substring_index( substring_index( a.OASHORTNAME, ',', b.help_topic_id + 1 ), ',',- 1 ) OASHORTNAME
         FROM g_bank a
                  JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.OASHORTNAME ) - length( REPLACE ( a.OASHORTNAME, ',', '' ) ) + 1 )
         where a.OASHORTNAME is not null
     ) g
where locate(OASHORTNAME,'建行收款支行') > 0 and length(trim(OASHORTNAME)) > 0
group by g.BANKNO;