mysql - 基于列表选择 MySQL

Question

我有两个表 (devices) 和 (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;