主页 / dba / 问题 / 27659
Accepted
Khairu Aqsara
Asked: 2012-10-26 20:54:37 +0800 CST

MySQL单表静态和动态pivot

  • 772

我有一个看起来像这样的表：

+----------------------------------------+
|Name                | kode      | jum   |
+----------------------------------------+
| aman               |kode1      | 2     |
| aman               |kode2      | 1     |
| jhon               |kode1      | 4     |
| amir               |kode2      | 4     |
+--------------------+-----------+-------+

如何使用 MySQL 制作这样的视图？

                    kode1    kode2     count
aman                  2         1        3  
jhon                  0         4        4
amir                  0         4        4
mysql pivot

3 个回答

  1. 如果您有已知数量的列，那么您可以使用类似于其他答案的静态版本。但是如果你有一个未知的数字，那么你可以使用类似于这样的准备好的语句：

    SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;


SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable 
                  GROUP BY name');

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

    请参阅带有演示的 SQL Fiddle

    结果是一样的：

    | NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |
    • 11
  2. Best Answer

    这是“枢轴”的一种形式，您应该使用该搜索词来查找其他选项。

    你可以尝试这样的事情：

    select
     name
   , sum(case when kode = 'kode1' then jum else 0 end) as 'kode1'
   , sum(case when kode = 'kode2' then jum else 0 end) as 'kode2'
   , sum(jum) as count
from foo
group by name;

    （假设您的示例视图有一个错误jhon）

    例子：

    mysql> select * from foo;
+------+-------+------+
| name | kode  | jum  |
+------+-------+------+
| aman | kode1 |    2 |
| aman | kode2 |    1 |
| jhon | kode1 |    4 |
| amir | kode2 |    4 |
+------+-------+------+
4 rows in set (0.00 sec)


mysql> select
    ->      name
    ->    , sum(case when kode = 'kode1' then jum else 0 end) as 'kode1'
    ->    , sum(case when kode = 'kode2' then jum else 0 end) as 'kode2'
    ->    , sum(jum) as count
    -> from foo
    -> group by name;
+------+-------+-------+-------+
| name | kode1 | kode2 | count |
+------+-------+-------+-------+
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |
+------+-------+-------+-------+
3 rows in set (0.00 sec)
    • 8

  3. 要将行转换为列，您需要使用CASE语句。要获得个人的计数，kode您需要使用SUM这样的功能：

    SELECT NAME,
       SUM(CASE kode WHEN 'kode1' THEN jum ELSE 0 END) AS kode1
      ,SUM(CASE kode WHEN 'kode2' THEN jum ELSE 0 END) AS kode2
      ,SUM(jum) AS `Count`
FROM Table1
GROUP BY Name

    看到这个 SQLFiddle

    • 8

相关问题