MYSQL 根据特定列选择多列

Question

我想做一个 SELECT 请求，女巫设法根据平台值获得 2 列值(桌面和移动)。

这是一个示例表:

+----+---------+------+----------+-------+
| ID | PROJECT | NAME | PLATFORM | VALUE |
+----+---------+------+----------+-------+
|  1 |       1 | Foo  | desktop  |     1 |
|  2 |       1 | Foo  | mobile   |    42 |
|  3 |       1 | Bar  | desktop  |     3 |
|  4 |       1 | Bar  | mobile   |    10 |
|  5 |       2 | Foo  | desktop  |     2 |
|  6 |       2 | Bar  | mobile   |     9 |
+----+---------+------+----------+-------+

期望的输出:

+---------+------+---------+--------+
| PROJECT | NAME | DESKTOP | MOBILE |
+---------+------+---------+--------+
|       1 | Foo  | 1       | 42     |
|       1 | Bar  | 3       | 10     |
|       2 | Foo  | 2       | NULL   |
|       2 | Bar  | NULL    | 9      |
+---------+------+---------+--------+

我尝试过的:

SELECT project, name,
(CASE platform WHEN 'desktop' THEN value END) AS "desktop",
(CASE platform WHEN 'mobile' THEN value END) AS "mobile"
FROM test
GROUP BY name, project
ORDER BY project, value ASC

+---------+------+---------+--------+
| project | name | desktop | mobile |
+---------+------+---------+--------+
|       1 | Foo  | 1       | NULL   |
|       1 | Bar  | 3       | NULL   |
|       2 | Foo  | 2       | NULL   |
|       2 | Bar  | NULL    | 9      |
+---------+------+---------+--------+

Answer 1

试试这个:

SELECT project, NAME, MAX(desktop) AS desktop, MAX(mobile) AS mobile FROM (
    SELECT project, NAME,
    (CASE platform WHEN 'desktop' THEN VALUE END) AS "desktop",
    (CASE platform WHEN 'mobile' THEN VALUE END) AS "mobile"
    FROM test
    ) AS aa
GROUP BY aa.NAME, aa.project
ORDER BY aa.project

解释:

首先您对所有数据进行选择(aa)，根据平台内容扩展值(value)。

然后您将该选择用作分组数据的来源。

结果:

project name   desktop mobile
1       Foo       1       42
1       Bar       3       10
2       Foo       2       NULL
2       Bar       NULL    9