我想做一个SELECT请求,根据PLATFORM值,设法获得2列VALUE(DESKTOP& MOBILE).
这是一个示例表：
+----+---------+------+----------+-------+
| 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 |
+---------+------+---------+--------+
最佳答案
试试这个：
SELECT project,NAME,MAX(desktop) AS desktop,MAX(mobile) AS mobile FROM (
SELECT project,(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)所有数据,根据平台内容扩展价值.
然后使用该选择作为分组数据的原点.
结果：
project name desktop mobile
1 Foo 1 42
1 Bar 3 10
2 Foo 2 NULL
2 Bar NULL 9