복붙노트

[SQL] SQL은 여러 행에 값을 분할

SQL

SQL은 여러 행에 값을 분할

나는 테이블이 있습니다 :

id | name    
1  | a,b,c    
2  | b

나는이 같은 출력을합니다 :

id | name    
1  | a    
1  | b    
1  | c    
2  | b

해결법

  1. ==============================

    1.당신이 분할로 최대 필드 1에서 숫자를 포함하는 숫자 테이블을 만들 수 있다면, 당신은이 같은 솔루션을 사용할 수 있습니다 :

    당신이 분할로 최대 필드 1에서 숫자를 포함하는 숫자 테이블을 만들 수 있다면, 당신은이 같은 솔루션을 사용할 수 있습니다 :

    select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

    여기 바이올린 참조하십시오.

    테이블을 만들 수없는 경우 해결책이 될 수 있습니다 :

    select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

    예를 들어 바이올린은 여기에있다.

  2. ==============================

    2.나는 변경된 열 이름과 여기에서 참조를 가지고있다.

    나는 변경된 열 이름과 여기에서 참조를 가지고있다.

    DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
  DECLARE output VARCHAR(65000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
  DECLARE i INTEGER;

  SET i = 1;
  REPEAT
    INSERT INTO GoodTable (id, name)
      SELECT id, strSplit(name, ',', i) FROM BadTable
      WHERE strSplit(name, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;
  3. ==============================

    3.내 변형 : 저장 프로 시저 테이블 이름, 필드 이름 및 인수로 구분됩니다. http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/ 포스트에서 영감을

    내 변형 : 저장 프로 시저 테이블 이름, 필드 이름 및 인수로 구분됩니다. http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/ 포스트에서 영감을

    delimiter $$

DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
    id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
  BEGIN
    DECLARE id INT DEFAULT 0;
    DECLARE value VARCHAR(255);
    DECLARE occurrences INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value VARCHAR(255);
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM 
        tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
        id_column,' id, ', value_column,' value FROM ',tablename);
    PREPARE stmt FROM @expr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DROP TEMPORARY TABLE IF EXISTS tmp_table2;
    CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;

    OPEN cur;
      read_loop: LOOP
        FETCH cur INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurrences = (SELECT CHAR_LENGTH(value) -
                           CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
        SET i=1;
        WHILE i <= occurrences DO
          SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
              SUBSTRING_INDEX(value, delim, i), delim, -1)));
          INSERT INTO tmp_table2 VALUES (id, splitted_value);
          SET i = i + 1;
        END WHILE;
      END LOOP;

      SELECT * FROM tmp_table2;
    CLOSE cur;
    DROP TEMPORARY TABLE tmp_table1;
  END; $$

delimiter ;

    사용 예 (정상화) :

    CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');

CREATE TABLE interests (
  interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  interest VARCHAR(30) NOT NULL
) SELECT DISTINCT value interest FROM tmp_table2;

CREATE TABLE contact_interest (
  contact_id INT NOT NULL,
  interest_id INT NOT NULL,
  CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
  CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
) SELECT my_contacts.contact_id, interests.interest_id
    FROM my_contacts, tmp_table2, interests
    WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;
  4. ==============================

    4.

    CREATE PROCEDURE `getVal`()
BEGIN
        declare r_len integer;
        declare r_id integer;
        declare r_val varchar(20);
        declare i integer;
        DECLARE found_row int(10);
        DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
        create table x(id int,name varchar(20));
      open row;
            select FOUND_ROWS() into found_row ;
            read_loop: LOOP
                IF found_row = 0 THEN
                         LEAVE read_loop;
                END IF;
            set i = 1;  
            FETCH row INTO r_len,r_id,r_val;
            label1: LOOP        
                IF i <= r_len THEN
                  insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
                  SET i = i + 1;
                  ITERATE label1;
                END IF;
                LEAVE label1;
            END LOOP label1;
            set found_row = found_row - 1;
            END LOOP;
        close row;
        select * from x;
        drop table x;
END
  5. ==============================

    5.원래 질문은 일반적으로 MySQL과 SQL 위해이었다. 아래의 예는 MySQL의 새로운 버전이다. 불행하게도, 어떤 SQL 서버에서 일하는 것이 일반적인 쿼리 할 수 ​​없습니다. 일부 서버는 지원 CTE는 다른 사람들이 SUBSTRING_INDEX이 없어요 수행 아직 다른 내장되어 기능 분할을 위해 여러 행으로 문자열을.

    원래 질문은 일반적으로 MySQL과 SQL 위해이었다. 아래의 예는 MySQL의 새로운 버전이다. 불행하게도, 어떤 SQL 서버에서 일하는 것이 일반적인 쿼리 할 수 ​​없습니다. 일부 서버는 지원 CTE는 다른 사람들이 SUBSTRING_INDEX이 없어요 수행 아직 다른 내장되어 기능 분할을 위해 여러 행으로 문자열을.

    --- 대답은 --- 다음

    서버가 내장 된 기능을 제공하지 않는 경우 재귀 쿼리 편리합니다. 또한 병목이 될 수 있습니다.

    다음 쿼리는 작성 및 MySQL 버전 8.0.16에서 테스트되었다. 이 버전 5.7- 작동하지 않습니다. 이전 버전은 공통 테이블 식 (CTE) 따라서 재귀 쿼리를 지원하지 않습니다.

    with recursive
  input as (
        select 1 as id, 'a,b,c' as names
      union
        select 2, 'b'
    ),
  recurs as (
        select id, 1 as pos, names as remain, substring_index( names, ',', 1 ) as name
          from input
      union all
        select id, pos + 1, substring( remain, char_length( name ) + 2 ),
            substring_index( substring( remain, char_length( name ) + 2 ), ',', 1 )
          from recurs
          where char_length( remain ) > char_length( name )
    )
select id, name
  from recurs
  order by id, pos;
  6. ==============================

    6.여기 내 시도는 다음과 같습니다 첫 번째 선택 선물 분할에 CSV 필드. 재귀 CTE를 사용하여, 우리는 CSV 필드에 용어의 수에 제한됩니다 번호 목록을 만들 수 있습니다. 용어의 수는 제거 구분 기호 모두와 단지 csv로 필드의 길이의 차이 그 자체입니다. 그런 다음 용어는이 번호, SUBSTRING_INDEX 추출물과 결합.

    여기 내 시도는 다음과 같습니다 첫 번째 선택 선물 분할에 CSV 필드. 재귀 CTE를 사용하여, 우리는 CSV 필드에 용어의 수에 제한됩니다 번호 목록을 만들 수 있습니다. 용어의 수는 제거 구분 기호 모두와 단지 csv로 필드의 길이의 차이 그 자체입니다. 그런 다음 용어는이 번호, SUBSTRING_INDEX 추출물과 결합.

    with recursive
    T as ( select 'a,b,c,d,e,f' as items),
    N as ( select 1 as n union select n + 1 from N, T
        where n <= length(items) - length(replace(items, ',', '')))
    select distinct substring_index(substring_index(items, ',', n), ',', -1)
group_name from N, T
  7. ==============================

    7.이름 열은, 다음) (JSON_TABLE로 압축을 풀고 / 추출 할 수 있습니다 ( '[ "A", "B", "C"]처럼) JSON 배열 인 경우 :

    이름 열은, 다음) (JSON_TABLE로 압축을 풀고 / 추출 할 수 있습니다 ( '[ "A", "B", "C"]처럼) JSON 배열 인 경우 :

    select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

    결과:

    | id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

    DB 바이올린에보기

    간단한 CSV 형식으로 값을 저장하면, 먼저 JSON으로 변환해야합니다 :

    select t.id, j.name
from mytable t
join json_table(
  concat('[', replace(json_quote(t.name), ',', '","'), ']'),
  '$[*]' columns (name varchar(50) path '$')
) j;

    결과

    | id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

    DB 바이올린에보기

  8. ==============================

    8.여기 내 솔루션입니다

    여기 내 솔루션입니다

    -- Create the maximum number of words we want to pick (indexes in n)
with recursive n(i) as (
    select
        1 i
    union all
    select i+1 from n where i < 1000
)
select distinct
    s.id,
    s.oaddress,
    -- n.i,
    -- use the index to pick the nth word, the last words will always repeat. Remove the duplicates with distinct
    if(instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' ') > 0,
        reverse(substr(reverse(trim(substring_index(s.oaddress,' ',n.i))),1,
            instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' '))),
        trim(substring_index(s.oaddress,' ',n.i))) oth
from 
    app_schools s,
    n

    9. from https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 그것은 SQLite는 데이터베이스에 한 번에 여러 행을 삽입 할 수 있습니까?  (0) 2020.03.06
[SQL] (FROM ... SELECT ...)에 ... 값 삽입  (0) 2020.03.06
[SQL] 날짜 범위에서 일을 생성  (0) 2020.03.06
[SQL] 어떻게 준비 문은 SQL 주입 공격으로부터 보호 할 수 있습니까?  (0) 2020.03.06
[SQL] 각 그룹의 상위 1 행을 가져 오기  (0) 2020.03.06
,

티스토리툴바