MySQL 数据透视表头刮板 [英] MySQL Pivot Table Head Scratcher

问题描述

我被旋转桌子难住了，挫败感越来越高.我意识到答案可能就在我面前，但我找不到.我试过 case 语句、group_concat、子查询都无济于事.

I'm stumped by pivoting a table and the frustration level is rising. I realize the answer is probably in front of me but I can't find it. I've tried case statements, group_concat, subqueries all to no avail.

如何获取当前表...

super_id    cat_id  qa  qb
   1           1    5   5
   1           2    2   5
   1           3    3   4
   2           4    5   3
   2           5    3   4
   2           6    4   2

看起来像这样...

       1     2 <--- super_id
qa    3.33  4.00 <--- avg
qb    4.67  3.00

谢谢！

推荐答案

正如已经提到的，MySQL 不是为自己构建的，如果可能的话，您可能应该在代码中进行透视，但除此之外，下面是一些示例 SQL那应该做你想做的.假设您的实际表在某种意义上是可变的(例如您有多少个 q[x] 列，或者您有多少个 super_id)，您可以将其包装在一个存储过程中以动态生成和执行必要的 SQL.

As already mentioned, MySQL isn't built to pivot on its own and you should probably pivot in code if possible, but barring that, below is some example SQL that should do what you want. Assuming your actual table is variable in some sense (like how many q[x] columns you have, or how many super_id's you have), you can wrap this up in a stored procedure to dynamically generate and execute the necessary SQL.

假设一个名为 testTable 的初始表:

Assuming an initial table named testTable:

mysql> select * from testTable;
+----------+--------+------+------+
| super_id | cat_id | qa   | qb   |
+----------+--------+------+------+
|        1 |      1 |    5 |    5 | 
|        1 |      2 |    2 |    5 | 
|        1 |      3 |    3 |    4 | 
|        2 |      4 |    5 |    3 | 
|        2 |      5 |    3 |    4 | 
|        2 |      6 |    4 |    2 | 
+----------+--------+------+------+
6 rows in set (0.00 sec)

使用以下 SQL:

SELECT
    'qa' q,
    SUM( CASE WHEN super_id = 1 THEN qa ELSE 0 END )/SUM( CASE WHEN super_id = 1 THEN 1 ELSE 0 END ) super_id_1,
    SUM( CASE WHEN super_id = 2 THEN qa ELSE 0 END )/SUM( CASE WHEN super_id = 2 THEN 1 ELSE 0 END ) super_id_2
FROM
    testTable
UNION ALL
SELECT
    'qb' q,
    SUM( CASE WHEN super_id = 1 THEN qb ELSE 0 END )/SUM( CASE WHEN super_id = 1 THEN 1 ELSE 0 END ) super_id_1,
    SUM( CASE WHEN super_id = 2 THEN qb ELSE 0 END )/SUM( CASE WHEN super_id = 2 THEN 1 ELSE 0 END ) super_id_2
FROM
    testTable
;

要达到这个结果:

+----+------------+------------+
| q  | super_id_1 | super_id_2 |
+----+------------+------------+
| qa |     3.3333 |     4.0000 | 
| qb |     4.6667 |     3.0000 | 
+----+------------+------------+
2 rows in set (0.00 sec)

(格式化)

这篇关于MySQL 数据透视表头刮板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！