我有两个表（设备）和（操作系统）描述为：
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;
但没有得到预期的结果。有人能帮我解决这个问题吗。任何帮助都非常感谢。
最佳答案
请尝试以下查询：
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;
关于mysql - 基于列表选择MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36609617/