MySQL/玛丽亚数据库 : create a pivot table view

Question

你好，有这个 MySQL (MariaDB) 表:

+----+-------+-------+---------+
| id | name  | value | user_id |
+----+-------+-------+---------+
|  1 | foo   | 40    |      10 |
|  2 | bar   | 15    |      10 |
|  3 | baz   | 390   |      10 |
|  4 | quux  | ENG   |      10 |
|  5 | waldo | 1     |      10 |
|  6 | foo   | 20    |      13 |
|  7 | bar   | 15    |      13 |
|  8 | waldo | 1     |      13 |
|  9 | baz   | 0     |      13 |
| 10 | quux  | ENG   |      13 |
| 11 | baz   | 420   |      15 |
| 12 | waldo | 1     |      15 |
| 13 | bar   | 1     |      15 |
| 14 | foo   | 5     |      15 |
| 15 | quux  | ENG   |      15 |
| 16 | waldo | 1     |      16 |
| 17 | quux  | ENG   |      16 |
| 18 | foo   | 5     |      16 |
| 19 | baz   | 0     |      16 |
| 20 | bar   | 15    |      16 |
+----+-------+-------+---------+

我需要得到这样的 View :

+---------+-----+-----+------------+-------+
| user_id | foo | bar | baz | quux | waldo |
+---------+-----+-----+------------+-------+
|      10 | 40  |  15 | 390 | ENG  |     1 |
|      13 | 20  |  15 |   0 | ENG  |     1 |
|      15 | 5   |   1 | 420 | ENG  |     1 |
|      16 | 5   |  15 |   0 | ENG  |     1 |
+---------+-----+-----+-----+------+-------+

我阅读了以下有关 MySQL 中的数据透视表的文章，但我需要一些帮助来创建正确的查询:

• https://riptutorial.com/mysql/example/10441/creating-a-pivot-query
• https://en.wikibooks.org/wiki/MySQL/Pivot_table
• https://codingsight.com/pivot-tables-in-mysql/
• https://modern-sql.com/use-case/pivot

你能帮帮我吗？

Answer 1

这是一个数据透视表

CREATE TABLE table1 (
  `id` INTEGER,
  `name` VARCHAR(5),
  `value` VARCHAR(3),
  `user_id` INTEGER
);

INSERT INTO table1
  (`id`, `name`, `value`, `user_id`)
VALUES
  ('1', 'foo', '40', '10'),
  ('2', 'bar', '15', '10'),
  ('3', 'baz', '390', '10'),
  ('4', 'quux', 'ENG', '10'),
  ('5', 'waldo', '1', '10'),
  ('6', 'foo', '20', '13'),
  ('7', 'bar', '15', '13'),
  ('8', 'waldo', '1', '13'),
  ('9', 'baz', '0', '13'),
  ('10', 'quux', 'ENG', '13'),
  ('11', 'baz', '420', '15'),
  ('12', 'waldo', '1', '15'),
  ('13', 'bar', '1', '15'),
  ('14', 'foo', '5', '15'),
  ('15', 'quux', 'ENG', '15'),
  ('16', 'waldo', '1', '16'),
  ('17', 'quux', 'ENG', '16'),
  ('18', 'foo', '5', '16'),
  ('19', 'baz', '0', '16'),
  ('20', 'bar', '15', '16');

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(s.name = "', `name`,'", `value`,"")) AS ',name)
              ) INTO @sql
FROM table1;


SET @sql = CONCAT('SELECT s.user_id,  ', @sql, ' 
                  FROM table1 s
                 GROUP BY s.user_id
                 ORDER BY s.user_id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;

user_id | bar | baz | foo | quux | waldo------: | :-- | :-- | :-- | :--- | :----     10 | 15  | 390 | 40  | ENG  | 1         13 | 15  | 0   | 20  | ENG  | 1         15 | 1   | 420 | 5   | ENG  | 1         16 | 15  | 0   | 5   | ENG  | 1    

db<> fiddle here