MySQL/MariaDB:创建数据透视表视图 [英] MySQL/MariaDB: create a pivot table view
本文介绍了MySQL/MariaDB:创建数据透视表视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有这张 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 |
+----+-------+-------+---------+
我需要获得这样的视图:
+---------+-----+-----+------------+-------+
| 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中数据透视表的文章，但在创建正确的查询时需要一些帮助:
I read the following articles about Pivot Tables in MySQL but I need some help in creating the correct query:
- 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
你能帮我吗?
推荐答案
我设法创建了正确的查询:
I managed in creating the correct query:
SELECT
user_id,
MAX(CASE WHEN name = "foo" THEN value END) "foo",
MAX(CASE WHEN name = "bar" THEN value END) "bar",
MAX(CASE WHEN name = "baz" THEN value END) "baz",
MAX(CASE WHEN name = "quux" THEN value END) "quux",
MAX(CASE WHEN name = "waldo" THEN value END) "waldo"
FROM table
GROUP BY user_id
ORDER BY user_id ASC
这篇关于MySQL/MariaDB:创建数据透视表视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
