mysql将字段转换为列 [英] mysql transform fields to column

问题描述

我有下表

table 1                             table 2
id   q_id   content                 id    w_id     q_id    c_id    ranking
----------------------          ------------------------------------------------
95   2046   1=E                     123   22404    2046    100     1

96   2046   2=G                     124   22404    2046    101     2

97   2046   3=N                     125   22404    2046    102     2

98   2046   4=B                     126   22404    2046    103     2

99   2046   5=V                     127   22404    2046    104     3

100  2046   A1                      128   22404    2046    105     3

101  2046   A2

102  2046   A3

103  2046   A4

104  2046   A5

105  2046   A6

我需要从表行转换为列

原始结果:

 
c_id   content    E     G     N     B     V
----------------------------------------------
100    A1         1     0     0     0     0

101    A2         0     1     0     0     0

102    A3         0     1     0     0     0

103    A4         0     1     0     0     0

104    A5         0     0     1     0     0

105    A6         0     0     1     0     0

结果1的代码:

(SELECT c.id, c.content, a.E, a.G, a.N, a.B, a.V FROM table_1 t
INNER JOIN 
(SELECT t1.id,  
  Count(IF(t2.ranking=1,1,0)) AS E,
  Count(IF(t2.ranking=2,1,0)) AS G,
  Count(IF(t2.ranking=3,1,0)) AS N,
  Count(IF(t2.ranking=4,1,0))AS B,
  Count(IF(t2.ranking=5,1,0)) AS V
FROM table_1 t1, table_2 t2 
WHERE t1.question_id = 2046 AND t2.question_id = 2046 AND t2.choice_id = t1.id 
AND t2.ranking >= 0 AND t2.w_id IN (22404)
GROUP BY t1.id) a ON a.id = t1.id);

转到新结果:

content  A1    A2     A3    A4     A5     A6
-----------------------------------------------
1=E      1     0      0      0      0      0

2=G      0     1      1      1      0      0

3=N      0     0      0      0      1      1

4=B      0     0      0      0      0      0

5=V      0     0      0      0      0      0

我正在使用MySql，但不能使用数据透视.另外，我想我不会.表1中的"A"代表"A30".因此，它应该是动态的..... 谁能给我建议结果2?

I am using MySql and I can't use pivot. Also, I think I will not know the no. of "A" in table 1 which means it may up to "A30". Therefore it should be dynamic..... Can anyone give me advice for Result 2?

@bluefeet我想我还需要在两个地方添加停顿条件，因为左联接可能会联接table_1中的大量数据.因此它无法显示结果.

@bluefeet I think I need to add two more condition in where caluse since the left join may joined large amount of data in table_1. So it can't show the result.

select c.content,
sum(case when t1.content = 'A1' then 1 else 0 end) A1,
sum(case when t1.content = 'A2' then 1 else 0 end) A2,
sum(case when t1.content = 'A3' then 1 else 0 end) A3,
sum(case when t1.content = 'A4' then 1 else 0 end) A4,
sum(case when t1.content = 'A5' then 1 else 0 end) A5
from table_1 c
left join table_2 t2
on left(c.content, 1) = t2.ranking
left join table_1 t1
on t2.c_id = t1.id
where locate('=', c.content) > 0 and c.id IN (95,96,97,98,99) and w_id = 22404
group by  c.content;

结果将是这样

| CONTENT | A1 | A2 | A3 | A4 | A5 | A6 |
-----------------------------------------
|     1=E |  1 |  0 |  0 |  0 |  0 |  0 |
|     2=G |  0 |  1 |  1 |  1 |  0 |  0 |
|     3=N |  0 |  0 |  0 |  0 |  1 |  1 |

缺少两行(4 = B，5 = V)

two rows are missing (4=B , 5=V).

我该如何解决?

推荐答案

以下查询应为您提供结果，但是需要做一些假设:

The following query should give you the result, however it makes a few assumptions:

• 最后一个content列将始终存在等号=.这用于查找内容中的行.
• 带=符号的content列值具有关联的等级作为第一个字符.此字符用于将行与table_2
中的排名连接

• The final content column will always have an equal sign = present. This is being used to locate the rows that are in the content.
• The content column values with the = sign have the associated rank as the first character. This character is used to join the rows to the ranking in table_2

如果您拥有已知数量的值，则可以使用以下内容:

If you have a known number of values, then you can use the following:

select c.content,
  sum(case when t1.content = 'A1' then 1 else 0 end) A1,
  sum(case when t1.content = 'A2' then 1 else 0 end) A2,
  sum(case when t1.content = 'A3' then 1 else 0 end) A3,
  sum(case when t1.content = 'A4' then 1 else 0 end) A4,
  sum(case when t1.content = 'A5' then 1 else 0 end) A5
from table_1 c
left join table_2 t2
  on left(c.content, 1) = t2.ranking
left join table_1 t1
  on t2.c_id = t1.id
where locate('=', c.content) > 0
group by  c.content;

请参见带演示的SQL提琴.

如果要使用未知数量的A值，则可以使用准备好的语句来生成动态SQL:

If you are going to have an unknown number of A values, then you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN t1.content = ''',
      content,
      ''' THEN 1 else 0 END) AS `',
      content, '`'
    )
  ) INTO @sql
FROM Table_1
where locate('=', content)= 0;

SET @sql 
  = CONCAT('SELECT c.content, ', @sql, ' 
            from table_1 c
            left join table_2 t2
              on left(c.content, 1) = t2.ranking
            left join table_1 t1
              on t2.c_id = t1.id
            where locate(''='', c.content) > 0
            group by  c.content;');

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

请参见带演示的SQL提琴.两者都给出结果:

See SQL Fiddle with Demo. Both give the result:

| CONTENT | A1 | A2 | A3 | A4 | A5 | A6 |
-----------------------------------------
|     1=E |  1 |  0 |  0 |  0 |  0 |  0 |
|     2=G |  0 |  1 |  1 |  1 |  0 |  0 |
|     3=N |  0 |  0 |  0 |  0 |  1 |  1 |
|     4=B |  0 |  0 |  0 |  0 |  0 |  0 |
|     5=V |  0 |  0 |  0 |  0 |  0 |  0 |

这篇关于mysql将字段转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！