Recently using database migration adaptation , from MySQL Library migration to dream database , When row to column ,MySQL Transform to dream sql There's something wrong with grammar , Special record .

       stay MySQL There are Group_Concat(expr)  , How to convert in Damong and Shentong database , Are there any functions with the same or similar functions ? Please see the record below .

      MySQL in  Group_Concat(expr)  Function support Internal custom connection separator , When you use it, you only need add to  SEPARATOR ';'  that will do ( Said the use of Semicolons Connect ). Default comma Connect

       Daydream database (DM) Or the magic database (oscar) Use  WM_Concat(expr) , Default comma ( English comma ) Connect , If you use a custom connector , Need to use replace(str,exp1,exp2) function , Example :replace(name,',',';')  Use Semicolons Replace the original Comma character .

  MySQL It is commonly used to transfer columns in Chinese banks function :Group_Concat(expr):

     Example - surface A :select name ,age,sex ,favorite from student      --- Operation merge example   favorite Field   【favorite field value :1、 Basketball ,2、 football ,3、 Basketball ,4、 swimming 】

    MYSQL: select Group_Concat(favorite) from student where name='zhangsan';       ---- final result favorite The fields in are marked with 【,】 Connect Result example : Basketball , football , Basketball , swimming

     duplicate removal :select Group_Concat(DISTINCT  facorite) from student where name='zhangsan';   ---- final result favorite The fields in are marked with 【,】 Connect   Result example : Basketball , football , swimming

     After removing the weight, we can 【;】 Connect :select Group_Concat(DISTINCT  facorite SEPARATOR ';')  from student where name='zhangsan'; ---- final result favorite The fields in are marked with 【;】 Connect   Result example : Basketball ; football ; swimming

  DM Bank of China to column / And MYSQL It's the same role in function :WM_Concat(expr):

     Example ( With MySQL in sql Example ):select name ,age,sex ,favorite from student      --- Operation merge example   favorite Field   【favorite field value :1、 Basketball ,2、 football ,3、 Basketball ,4、 swimming 】

    DM: select WM_Concat(favorite) from student where name='zhangsan';       ---- final result favorite The fields in are marked with 【,】 Connect   Result example : Basketball , football , Basketball , swimming

     duplicate removal :select WM_Concat(DISTINCT  facorite) from student where name='zhangsan';   ---- final result favorite The fields in are marked with 【,】 Connect   Result example : Basketball , football , swimming

     After removing the weight, we can 【;】 Connect :select  replace(WM_Concat(DISTINCT  facorite),‘,’,‘,’  )from student where name='zhangsan'; ---- final result favorite The fields in are marked with 【;】 Connect   Result example : Basketball ; football ; swimming

   notes : Supernatural power Database row to column / And MySQL Merge string functions in You can also use  WM_Concat(expr):  At present, there is no Shentong database , But it's fast , Use and update .

  

   The above is the record of this time , Continue to update later . Reprint please indicate the source , thank ! If there is any irrationality , Please give me more advice .

   If you find this article helpful , Welcome to click “ Collection ” Button !(/: smile ) Welcome to reprint , Reprint please indicate the source .

MySQL、DM Row to column and field de duplication (Group_Concat()) More articles about

  1. About MySQL The simple application of row to column ( Two )---group function

    MySQL Row column of . Column turned . Connection string  concat.concat_ws.group_concat Function usage, usage :CONCAT(str1,str2,…) The return result is the string generated by the connection parameter . If there is any ...

  2. mysql Row to column and column to row

    mysql Row to column and column to row are common operations of data query , Show the data in a better way , Let's talk about how to realize it in detail . Transfer line column In the case of row to column , It is to convert a row data record into a column data display , Generally speaking, it is to summarize data according to a certain column ...

  3. Mysql Data processing / Transfer line column / Column turned / Division / Splicing / Data replication summary

    mysql Data processing records ( The use of Workbench) Generate random number Comma or semicolon concatenated strings are split into multiple lines Multiple lines of data are converted into comma concatenated strings take A Table data added to B surface One . Generate random number Generate 18 position :(19 Just add a little bit ...

  4. mysql Dynamic row rollover

    Test data DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` ) NOT NULL AUTO_INCREMENT, `class` ) ...

  5. mysql To achieve row to column function

    Realize the function of transforming from Figure 1 to figure 2 : Figure 1 : Figure 2 : Create table statement : CREATE TABLE `t_user_score` ( `id` ) NOT NULL AUTO_INCREMENT COMMENT ' Lord ...

  6. About MySQL The simple application of row to column

    sql Script -- Create table Student list CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT ' Student number ', `stunm` VARCHA ...

  7. mySQL Row column of

    because MYSQL There's no  PIVOT      It is now recorded that :   The original form :   mysql sentence : SELECT MAX(CASE WHEN corol=' red ' THEN NUM else 0 END) A ...

  8. mysql Dynamic row rollover

    Table results :create table user( id int , username ), create_time datetime, type int ) insert into user (`id`, ...

  9. Partial replacement mysql The field of a column in a table

    UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` ...

  10. mysql Row column of PIVOT Use

    sentence :SELECT DataDate , PropertyText , DataValue FROM RPT_ReportProperty p WITH ( NOLOCK ) JOIN RPT_Rep ...

Random recommendation

  1. Asp.net WebApi + EF Unit test architecture DbContext One stop to the end

    In fact, about webapi and Ef service I've written about it before , You can refer to : Asp.net WebAPI unit testing unit testing mock EF in DbContext and DbSet Inc ...

  2. spring boot Database connection pool configuration

    HikariCP Connection pool configuration : http://stackoverflow.com/questions/29650501/hikaricp-starts-when-mvn-spring-bootrun- ...

  3. NET Basic courses --JIT How the compiler works 1

    1..Net Run time call JIT compiler , It's used to put C# Generated by the compiler IL Instructions are compiled into machine code . This task is done step by step during the running of the application .JIT You don't compile the entire application at the beginning of the program , In its place ,CLR It's a function ...

  4. Enterprise class IM We should help our employees improve their performance , Avoid irrelevant information interference

    Many office workers must be familiar with the following scenes : As soon as you go to work in the morning QQ, A news integration window will pop up , Just click here and have a look , It doesn't matter , You will be one after another with a link to browse down . When I come back to work , I found that I had wasted a lot of time on the gossip ...

  5. php Generate .txt file

    $content =array('color'=> array('blue','red','green'),'size'=> array('small','medium','large') ...

  6. manjaro Installation and setup

    Because of my notebook ( The Savior of Lenovo ) Last night, the system was updated without doing anything “ Snatch ” It doesn't work if you press the screen So I thought if I could not use it win System , We all know linux Than win Stable ,so.... I found it manjaro. Here are ...

  7. zabbix A complete collection of error reporting and troubleshooting

    zabbix Report errors https://www.cnblogs.com/losbyday/category/876878.html The author sums it up very well 1. Start up zabbix-agent  When the system log output PID ...

  8. ant Common built-in task from https://www.cnblogs.com/baicj/archive/2015/12/21/5063608.html

    ant for example : <target name="callProjectB"> <echo message="In projectA calling proj ...

  9. Python PEP 8 Coding standard Chinese version

    Link to the original text :http://legacy.python.org/dev/peps/pep-0008/ Forward link :https://blog.csdn.net/ratsniper/article/detail ...

  10. Version control tools git

    The company asked for git, It's not as good as svn Well done , It's still command-line , Take a note of . The server is linux It can be installed directly on the computer . I'm a virtual machine centos6.9 edition .yum install -y git The version number is git -- ...