mysql - 将行转为列 - 面临的问题。

Question

我有以下数据集(请注意，我的数据库/模式有些复杂，因此在此处包含所有详细信息是不可行的)

这是我从基表中提取的数据的简化版本，并创建了一个 View 。

workout_activity_record_id  attribute_metric_id attribute_metric_value  workout_exercise_set_created_on234                                17              10                          2012-02-06 00:00:00234                                18              30                          2012-02-06 00:00:00234                                17              20                          2012-03-03 00:00:00234                                18              12.9                        2012-03-03 00:00:00234                                17              20                          2012-04-02 00:00:00234                                18              40                          2012-04-02 00:00:00... (So on for further workout_activity_record_ids)

我需要计算(查找数据以便更好地理解)

Grand Total = (10 * 30) + (20 * 12.9) + (20 * 40)

按月分组。

此处有助于相乘的基本方法是将行转置为列。所以上面的结构将变成 -

workout_activity_record_id     ex17  ex18       workout_exercise_set_created_on     234                            10     30        2012-02-06 00:00:00234                            20     12.9      2012-03-03 00:00:00234                            20     40        2012-04-02 00:00:00.....  (And so on for remaining workout_activity_record_ids)

为此，我浏览了几篇 SO 帖子，在尝试了各种可行/不可行 (:D) 选项后，我提出了以下查询。

SELECT       CASE attribute_metric_id           WHEN '17' THEN attribute_metric_value END AS 'ex17',       CASE attribute_metric_id WHEN '18' THEN attribute_metric_value END AS 'ex18',        workout_exercise_set_created_onFROM exercise_attributeWHERE workout_activity_record_id =234

我得到的实际输出是

workout_activity_record_id  ex17    ex18    workout_exercise_set_created_on 234                          10      NULL   2012-02-06 00:00:00234                          NULL    30     2012-02-06 00:00:00234                          20      NULL   2012-03-03 00:00:00234                          NULL    12.9   2012-03-03 00:00:00234                          20      NULL   2012-04-06 00:00:00234                          NULL    40     2012-04-02 00:00:00

任何人都可以阐明这一点吗？我将不胜感激。谢谢!

Answer 1

这会为您计算总计，按年和月分组:

SELECT YEAR(workout_exercise_set_created_on) AS YEAR,
       MONTH(workout_exercise_set_created_on) AS MONTH,
       sum(SubTotal) AS GrandTotal
FROM
  (SELECT workout_exercise_set_created_on,
          max(CASE WHEN attribute_metric_id = 17 THEN attribute_metric_value END) * max(CASE WHEN attribute_metric_id = 18 THEN attribute_metric_value END) AS SubTotal
   FROM MyTable
   GROUP BY workout_exercise_set_created_on) a
GROUP BY YEAR(workout_exercise_set_created_on),
         MONTH(workout_exercise_set_created_on)

SQL Fiddle Example

输出

YEAR    MONTH   GRANDTOTAL
2012    2        300
2012    3        258
2012    4        800