sourcecode

mysql join two table, 그리고 0이 아닌 값을 가진 일치하는 레코드의 필드 이름을 가져옵니다.

copyscript 2023. 9. 16. 09:48
반응형

mysql join two table, 그리고 0이 아닌 값을 가진 일치하는 레코드의 필드 이름을 가져옵니다.

아래와 같은 테이블이 두 개 있습니다.

mysql> show tables;
+-------------------+
| Tables_in_testdbs |
+-------------------+
| dts               |
| ref               |
+-------------------+
2 rows in set (0.00 sec)

각 테이블의 내용은 다음과 같습니다.

mysql> select * from ref;
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    2 |    2 |
|  4 |    3 |    1 |
|  5 |    3 |    2 |
|  6 |    3 |    3 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |
|  5 |    1 |    2 |      5 |    0 |    0 |    0 |    2 |    5 |
|  6 |    1 |    2 |      6 |    0 |    0 |    0 |    0 |    1 |
|  7 |    1 |    2 |      7 |    0 |    1 |    0 |    0 |    0 |
|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 |
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |
| 10 |    3 |    2 |      3 |    0 |    0 |    0 |    0 |    0 |
| 11 |    3 |    3 |      1 |    1 |    1 |    0 |    0 |    1 |
| 12 |    3 |    3 |      5 |    0 |    0 |    1 |    1 |    0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)

이것이 제가 두 테이블을 합치기 위해 시도한 것입니다.

mysql> select distinct
    ->        i.key1,
    ->        i.key2 
    -> from 
    ->        ref i, 
    ->        dts d 
    -> where 
    ->        i.key1=d.key1 and 
    ->        i.key2=d.key2 ;
+------+------+
| key1 | key2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    3 |    3 |
+------+------+
5 rows in set (0.00 sec)

저는 O/P이하를 예상하고 있는데, 어떻게 구해야 할지 정말 모르겠습니다.

key1       key2 fields_non_zero
1           1   pr1,pr3,pr4,pr5
1           2   pr2,pr4,pr5
2           2   pr1,pr2,pr3,pr4,pr5
3           2
3           3   pr1,pr2,pr3,pr4,pr5

예를 들어 아래 조건을 이용하여 확인하고 싶습니다.key1=1그리고.key2=1양쪽 테이블이 일치하는

  1. 테이블 두 개 결합
  2. dts 필드(pr1-pr5)에 0이 아닌 데이터가 있는지 확인하고 일치합니다.
  3. 쉼표가 있는 concat 필드 이름을 찾은 경우,
  4. 모든 필드가 0이 아닌 경우, 단지 concat 필드를 사용하고 같은 key1, key2에 대해 더 이상 결합을 중지한다고 가정합니다. 왜냐하면 모든 필드가 발견되면(실행 시간 저장) 다음 key1, key2로 이동하기 때문입니다.
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |              <- for ref table key1,key2 following rows matches 

| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |  nonzero_fields 
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |  = pr3,pr5
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |  =  
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |  = pr4
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |  = pr1,pr3,pr4,p45

  So distinct of below are

         = pr3,pr5
         = 
         = pr4
         = pr1,pr3,pr4,p45 

 key1   key2 fields_non_zero
 1       1   pr1,pr3,pr4,pr5

적어도 아래와 같이 주문이 없어도 상관없습니다.

 key1   key2 fields_non_zero
 1       1   pr3,pr5,pr4,pr1

표의 구조는 다음과 같습니다.

DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  `serial` int(11) DEFAULT '-99',
  `pr1` int(11) DEFAULT '-99',
  `pr2` int(11) DEFAULT '-99',
  `pr3` int(11) DEFAULT '-99',
  `pr4` int(11) DEFAULT '-99',
  `pr5` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;



DROP TABLE IF EXISTS `ref`;
CREATE TABLE `ref` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


LOCK TABLES `ref` WRITE;
INSERT INTO `ref` VALUES (1,1,1),(2,1,2),(3,2,2),(4,3,1),(5,3,2),(6,3,3);
UNLOCK TABLES;

데이터 피벗을 해제할 수 있습니다.dts테이블에 올려놓고 사용합니다.group_concat그 위에

SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM
    ref r
        INNER JOIN
    (
    SELECT
    d.key1,
    d.key2,
    t.pr,
    CASE t.pr
        WHEN 'pr1' THEN pr1
        WHEN 'pr2' THEN pr2
        WHEN 'pr3' THEN pr3
        WHEN 'pr4' THEN pr4
        WHEN 'pr5' THEN pr5
    END val
FROM
    dts d
        CROSS JOIN
    (
    SELECT 'pr1' pr UNION ALL 
    SELECT 'pr2' UNION ALL 
    SELECT 'pr3' UNION ALL 
    SELECT 'pr4' UNION ALL 
    SELECT 'pr5') t
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

제작물:

+------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+
5 rows in set (0.00 sec)

편집:

가입하지 않음ref표(reftable에는 key1, key2가 모두 포함되어 있으며 내부에서 이를 결합하고 있었을 뿐입니다):

SELECT 
    key1,
    key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    SELECT 
        d.key1,
            d.key2,
            t.pr,
            CASE t.pr
                WHEN 'pr1' THEN pr1
                WHEN 'pr2' THEN pr2
                WHEN 'pr3' THEN pr3
                WHEN 'pr4' THEN pr4
                WHEN 'pr5' THEN pr5
            END val
    FROM
        dts d
    CROSS JOIN (
        SELECT 'pr1' pr UNION ALL 
        SELECT 'pr2' UNION ALL 
        SELECT 'pr3' UNION ALL 
        SELECT 'pr4' UNION ALL 
        SELECT 'pr5'
    ) t
) r
GROUP BY key1 , key2;

동일한 출력을 생성합니다.

+------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+
5 rows in set (0.00 sec)

편집 2:

SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    select key1, key2
    from ref
    order by id
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r INNER JOIN (
    SELECT
    d.key1,
    d.key2,
    t.pr,
    CASE t.pr
        WHEN 'pr1' THEN pr1
        WHEN 'pr2' THEN pr2
        WHEN 'pr3' THEN pr3
        WHEN 'pr4' THEN pr4
        WHEN 'pr5' THEN pr5
    END val
FROM
    dts d
        CROSS JOIN
    (
    SELECT 'pr1' pr UNION ALL 
    SELECT 'pr2' UNION ALL 
    SELECT 'pr3' UNION ALL 
    SELECT 'pr4' UNION ALL 
    SELECT 'pr5') t
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

처음 1000개의 고유 키 쌍의 경우 위 쿼리에서 아래 SQL을 사용합니다.

(
    select key1, key2
    from ref
    group by key1, key2
    order by key1, key2
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r

언급URL : https://stackoverflow.com/questions/41856497/mysql-join-two-table-and-get-field-name-of-the-matched-record-which-has-nonzero

반응형

'sourcecode' 카테고리의 다른 글

Excel 파일을 열면 "워크북 내용 복구" 메시지 상자가 나타납니다.  (0) 2023.09.16
Cent OS 5에서 느린 cronjobs  (0) 2023.09.16
os.system() 호출에서 벗어나는 방법?  (0) 2023.09.11
ctrl+c를 사용하여 python 중지  (0) 2023.09.11
괄호와 비교했을 때 어떤 차이가 있습니까? WHERE (a, b)= (1,2)  (0) 2023.09.11

'sourcecode'의 다른글

  • 현재글mysql join two table, 그리고 0이 아닌 값을 가진 일치하는 레코드의 필드 이름을 가져옵니다.

관련글

댓글

티스토리툴바