如何在SQL中按相等的列值排序和分组？

Question

我的桌子是：

   device_name | device_category
---------------------------------
standard_spot  |    light
mobile_spot    |    light
tracker        |    light
smoking_machine|    light
microphone1    |    sound
microphone2    |    sound
display1_hmdi  |    projector
display1_vga   |    projector
display1_sound |    projector

我需要一个查询，该查询提供device_names和根据device_categories出现的device_category排序的device_categories。我的尝试是这个查询，它提供了每个类别的出现情况：

SELECT COUNT(*) AS num_dcats FROM `devices` GROUP BY device_category ORDER BY num_dcats DESC

如下所示：

device_category | num_dcats
---------------------------
light           |  4
projector       |  3
sound           |  2

Answer 1

你可以：

select d.*
from devices d
join (
  select device_category, count(*) as cnt from devices group by device_category
) c on c.device_category = d.device_category
order by c.cnt desc

Answer 2

使用窗口函数：

select d.*, count(*) over (partition by device_category) as num_cats
from devices d
order by num_cats;