MySQL - 행에서 열로

programing

MySQL - 행에서 열로

copysource 2022. 12. 10. 14:12

MySQL - 행에서 열로

게시물을 검색하려고 했지만 SQL Server/Access 솔루션만 찾았습니다.MySQL(5.X) 솔루션이 필요합니다.

hostid, item name, item value 3은 hostid, itemname, item value입니다.
( 「」「」「。select * from history)는 다시 돌아온다.

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

데이터베이스를 쿼리하여 다음과 같은 것을 반환하려면 어떻게 해야 합니까?

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

이 문제를 해결하기 위해 취해야 할 절차에 대해 좀 더 길고 자세한 설명을 덧붙이겠습니다.너무 길면 죄송합니다.

먼저 당신이 준 베이스부터 시작해서 이 포스트의 나머지 부분에서 사용할 몇 가지 용어를 정의하겠습니다.기본 테이블은 다음과 같습니다.

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

이것이 우리의 목표인 예쁜 피벗 테이블입니다.

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

의 값history.hostid열은 피벗 테이블에서 y 값이 됩니다.의 값history.itemname열은 (명백한 이유로) x 값이 됩니다.

피벗 테이블 작성 문제를 해결해야 할 경우 3단계 프로세스(옵션인 4단계)를 사용하여 해결합니다.

관심 열(예: y 값 및 x 값)을 선택합니다.
추가 열을 사용하여 기준 테이블을 확장합니다. 각 x 값에 하나씩
확장 테이블 그룹화 및 집계 - 각 y 값에 대해 하나의 그룹
(선택사항) 집계 테이블을 예쁘게 만듭니다.

다음 단계를 고객의 문제에 적용하여 결과를 확인합니다.

1단계: 관심 열을 선택합니다.원하는 결과,hostid에 y값과itemname에 x 값을 나타냅니다.

2단계: 추가 열을 사용하여 기본 테이블을 확장합니다.일반적으로 x-값당 하나의 열이 필요합니다.x-값 열은itemname

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

행의 수는 변경하지 않고 열을 추가했을 뿐입니다., 「」의 해 주세요.NULL --s가 있는 입니다.itemname = "A" 、 [ column ]에 대해 값이 .A및및기 、 럼럼의값값값 。

스텝 3: 확장 테이블을 그룹화하고 집약합니다.우리는 할 필요가 있다group by hostid y값을 y값이 나오니까.

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

(여기서는 y값당 행이1개 있습니다.좋아, 거의 다 왔어!그 못생긴 놈들을 없애면 돼NULLs.

4단계: 예쁘게.null 값을 0으로 치환하면 결과 세트가 더 보기 쉬워집니다.

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

이것으로 MySQL을 사용하여 멋진 피벗 테이블을 만들었습니다.

이 절차를 적용할 때 고려해야 할 사항은 다음과 같습니다.

이치하였습니다.itemvalue에서는, 「」를 참조해 주세요.
추가 열에 사용할 "중립" 값.나는 사용했다NULL 「」일 도 있습니다.0 ★★★★★★★★★★★★★★★★★」""
그룹화할 때 사용할 집계 함수를 지정합니다.하였습니다.sumcount ★★★★★★★★★★★★★★★★★」max도 한다.max여러 줄에 걸쳐 있던 한 줄의 "거치대"를 만들 때 자주 사용됩니다.)
y에 여러 합니다.하는 데. 을 y값에 만 하면 . 추가 열을 플러그에 꽂기만 하면 됩니다.group by 잊지 select) ★★★★★★★★★★★)

알려진 제한 사항:

이 솔루션은 피벗 테이블에 n개의 열을 허용하지 않습니다. 기본 테이블을 확장할 때 각 피벗 열을 수동으로 추가해야 합니다.따라서 5 또는 10개의 x 값에 대해 이 솔루션이 적합합니다.100달러면 그렇게 좋진 않지만.쿼리를 생성하는 스토어드 프로시저를 사용하는 솔루션도 있지만 보기 흉하고 올바르게 작성하기가 어렵습니다.피벗 테이블에 열이 많이 있어야 할 때 이 문제를 해결하는 좋은 방법을 현재 잘 모르겠습니다.

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

피벗해야 할 항목이 많을 때 특히 유용한 다른 옵션은 mysql이 쿼리를 빌드하도록 하는 것입니다.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FILED가 동작하는 것을 확인하기 위해 몇 가지 값을 추가했습니다.

GROUP_CONCAT큰에 이 합니다.

SET SESSION group_concat_max_len = 1000000;

테스트:

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

  hostid    A     B     C      D
    1     10      3     0      0
    2     9       0    40      5
    3     14     67     0      8

문제 해결에 도움이 된 Matt Fenwick의 아이디어를 활용하여 (감사합니다) 한 가지 질문으로 요약하겠습니다.

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

아궁사기타의 답변을 서브쿼리에서 편집하여 참여합니다.이 두 가지 방법의 차이가 어느 정도인지는 잘 모르겠습니다만, 다른 참고가 될 뿐입니다.

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

서브쿼리를 사용하다

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

그러나 하위 쿼리가 행보다 많은 결과를 내는 경우 문제가 됩니다. 하위 쿼리에서 추가 집계 함수를 사용하십시오.

MariaDB를 사용할 수 있다면 매우 쉬운 솔루션이 있습니다.

MariaDB-10.02 이후 CONNECT라는 새로운 스토리지 엔진이 추가되어 다른 쿼리 또는 테이블의 결과를 원하는 대로 피벗 테이블로 변환할 수 있습니다.당신은 문서를 볼 수 있습니다.

먼저 연결 스토리지 엔진을 설치합니다.

은 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」itemname각 항목의 데이터는 다음 위치에 있습니다.itemvalue다음 쿼리를 사용하여 결과 피벗 테이블을 얻을 수 있습니다.

create table pivot_table
engine=connect table_type=pivot tabname=history
option_list='PivotCol=itemname,FncCol=itemvalue';

이제 원하는 것을 선택할 수 있게 되었습니다.pivot_table:

select * from pivot_table

자세한 내용은 이쪽

솔루션:

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

제출된 사례에서 예상된 결과를 얻을 수 있습니다.

나는 그것을 로 만듭니다.Group By hostId값이 있는 첫 번째 행만 표시됩니다.
예를 들어 다음과 같습니다.

A   B  C
1  10
2      3

간단한 쿼리를 사용하여 행을 열로 변환하는 보고서를 거의 동적으로 만드는 방법을 알아냈습니다.여기에서 온라인으로 보고 테스트할 수 있습니다.

쿼리 열의 수는 고정되어 있지만 값은 동적이며 행 값을 기반으로 합니다.작성할 수 있습니다.따라서 테이블헤더를 작성하는 쿼리와 값을 표시하는 쿼리를 각각 사용합니다.

SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;

SELECT
     hostid
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
FROM history order by 1;

요약도 할 수 있습니다.

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

RexTester 결과:

http://rextester.com/ZSWKS28923

이 보고서의 실제 사용 예시는 열로 보트/버스의 출발 도착 시간을 시각적인 일정으로 보여준다.시각화를 혼동하지 않고 마지막 콜에서 사용되지 않은 추가 컬럼이 표시됩니다.** 온라인 및 프리젠테이션 티켓 판매 시스템

이것은 당신이 찾고 있는 정확한 답은 아니지만, 그것은 제 프로젝트에서 제가 필요로 했던 해결책이었고, 이것이 누군가에게 도움이 되기를 바랍니다.1 ~ n 행의 항목이 쉼표로 구분되어 나열됩니다.Group_Concat은 MySQL에서 이를 가능하게 합니다.

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

이 공동묘지에는 두 개의 공통 이름이 있으므로 이름은 하나의 ID로 연결된 다른 행에 나열되지만 두 개의 이름 ID로 쿼리가 생성됩니다.

묘지ID Curipty_Name Latitude
1 Appleton, Sulper Springs 35.4276242832293

몇 개 요.LEFT JOIN 이 하십시오. 이 코드를 사용하십시오.

SELECT t.hostid,
       COALESCE(t1.itemvalue, 0) A,
       COALESCE(t2.itemvalue, 0) B,
       COALESCE(t3.itemvalue, 0) C 
FROM history t 
LEFT JOIN history t1 
    ON t1.hostid = t.hostid 
    AND t1.itemname = 'A' 
LEFT JOIN history t2 
    ON t2.hostid = t.hostid 
    AND t2.itemname = 'B' 
LEFT JOIN history t3 
    ON t3.hostid = t.hostid 
    AND t3.itemname = 'C' 
GROUP BY t.hostid

이런 말 해서 미안하고 내가 너의 문제를 정확히 해결하지 못하고 있는 것 같아, 하지만 PostgreSQL은 MySQL보다 10년 이상 오래되었으며 MySQL에 비해 매우 고급이며 이를 쉽게 달성할 수 있는 방법은 여러 가지가 있습니다.Postgre 설치SQL 및 이 쿼리 실행

CREATE EXTENSION tablefunc;

그럼 으악!다음은 광범위한 문서입니다.포스트그레SQL: 문서: 9.1: tablefunc 또는 this 쿼리

CREATE EXTENSION hstore;

그럼 또 와아악!포스트그레SQL: 문서: 9.0: hstore

언급URL : https://stackoverflow.com/questions/1241178/mysql-rows-to-columns

저작자표시

'programing' 카테고리의 다른 글

pytest 실행 중에 생성된 일반 인쇄 출력을 보려면 어떻게 해야 합니까? (0)	2022.12.10
MySQL 테이블 생성일을 확인하려면 어떻게 해야 합니까? (0)	2022.12.10
PHP url 매개 변수가 있는지 확인합니다. (0)	2022.10.31
Xcode 4+의 Python? (0)	2022.10.31
원격 서버에서 로컬 컴퓨터로 mysql 데이터베이스 복사 (0)	2022.10.31

현재글MySQL - 행에서 열로

각종 프로그래밍 정보를 다루는 블로그입니다.

Spring, JavaScript, jQuery, C++, Java, c#, spring3,

Today :
Yesterday :

copysource

MySQL - 행에서 열로

MySQL - 행에서 열로

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2024/10 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

MySQL - 행에서 열로

MySQL - 행에서 열로

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바