java - mysql 将行合并为列

Question

假设我有一个包含以下信息的表格(可能有 40 多个独特的度量)

表1

id    name    state    Measure       score
1     Joe     CA       work ethic    40
1     Joe     CA       cleanliness   80
1     Joe     CA       helpfulness   70
2     John    TX       work ethic    70
2     John    TX       helpfulness   50
3     Jack    AZ       helpfulness   50

我想将每个人的度量合并到单独的列中，并使 id 在一个新表中具有唯一性，如下所示:

表2

id    name    state    workEthicScore    cleanlinessScore    helpfulnessScore
1     Joe     CA       40                80                  70
2     John    TX       70                null                50
3     Jack    AZ       null              null                50

因此，理想情况下，我希望能够创建这个新表，而无需手动输入所有不同的度量值。我该如何使用 Java 和 MYSQL 来解决这个问题？我不想使用 mysql group_concat，因为我希望将它们作为单独的列而不是组合成单个列。

Answer 1

开始像这样构建查询:

  SELECT t.id
       , t.name 
       , t.state
       , 0           AS workEthicScore
       , 0           AS cleanlinessScore
       , 0           AS helpfulnessScore
    FROM mytable t
   GROUP
      BY t.id
       , t.name 
       , t.state

要获取返回的 score 值，您可以使用执行条件聚合的表达式，并将上面语句中的文字 0 替换为如下表达式:

       , MAX(IF(t.measure = 'work ethic', t.score,NULL)) AS workEthicScore

对 measure 的其他值重复相同的模式。

要使这样的查询正常工作，必须在 SQL 语句中指定列数、列名以及用于返回列值的表达式。这不能在语句内动态完成。

要动态获得这样的结果需要两个单独的语句。第二个语句必须采用如上所示的形式。您可以使用单独的语句来获取一些值来帮助您构建该语句，例如

  SELECT m.measure FROM mytable m GROUP BY m.measure 

或者，您可以做一些更奇特的事情，生成要包含在第二个语句的 SELECT 列表中的表达式和别名:

  SELECT CONCAT('      , MAX(IF(t.measure='''
           ,REPLACE(m.measure,'''','''''')
           ,''',t.score,NULL) AS `'
           ,m.measure
           ,'`'
         ) AS expr
    FROM mytable m
   GROUP BY m.measure

不可能在单个语句中使用动态列获得这样的结果。