基于列表MySQL进行选择

Question

我有两个表(设备)和(os)，描述如下：

os:
+-----------+---------------+-------------+
| device_os | os_name       | device_type |
+-----------+---------------+-------------+
| devos_10  | Funtoo Linux  | devtype_5   |
| devos_101 | Windows 10    | devtype_4   |
| devos_102 | Windows Vista | devtype_4   |
| devos_103 | Mac OS X 10.4 | devtype_6   |
......

devices
+------------+-----------+-------------+-------------+
|  device_id | device_os | device_type |   country   |
+------------+-----------+-------------+-------------+
| id_804397  | devos_10  | devtype_5   | country_146 |
| id_1274047 | devos_103 | devtype_6   | country_16  |
| id_4416554 | devos_102 | devtype_4   | country_14  |
......

当我执行以下查询时：

SELECT a.device_os, (count(*)/42028)*100 AS value FROM devices a GROUP BY a.device_os ORDER BY value DESC;

我得到了一张桌子，像这样：

+-----------+---------+
| device_os | value   |
+-----------+---------+
| devos_10  | 41.8578 |
| devos_102 | 40.0638 |
| devos_103 | 18.2926 |
......

但我想要的结果如下：

+---------------+---------+
| os_name       | value   |
+---------------+---------+
| Funtoo Linux  | 41.8578 |
| Windows Vista | 40.0638 |
| Mac OS X 10.4 | 18.2926 |
....

我试过这样做：

SELECT c.os_name, a.device_os, (count(*)/42028)*100 AS value FROM devices a, os c WHERE a.device_os = c.os_name GROUP BY a.device_os ORDER BY value DESC;

但是没有得到预期的结果。有没有人能帮我解决这个问题。任何帮助都是非常感谢的。

Answer 1

尝试执行以下查询：

SELECT o.os_name, (count(a.*)/42028)*100 AS value 
FROM devices a join os o on a.device_os = o.device_os 
GROUP BY a.device_os ORDER BY value DESC;