一整群人的命令

Question

我有一张这样的桌子：

Brand | Type | Units
Sony | TV | 5
Toshiba | TV | 4
Sony | Radio | 1
Toshiba | Radio | 10

我想要这样：

Type | Brand | Units
Radio | Toshiba | 10
Radio | Sony | 1
TV | Sony | 5
TV | Toshiba | 4

也就是说，按类型的，品牌的类型计数(10+1 vs 5+4)，然后根据单位的计数(10 vs 1和5 vs 4)来订购。

你认为实现这一目标的最佳途径是什么？

我尝试了以下方法，在每个组中添加一个小计：

select Type, Brand, sum(Units) from mytable
group by Type, Brand, sum(Units) WITH ROLLUP

Type | Brand | sum(Units)
Radio | Toshiba | 10
Radio | Sony | 1
Radio | null | 11
TV | Sony | 5
TV | Toshiba | 4
TV | null | 9
null | null | 20

但我看不出有什么办法按这些小计分类。

Answer 1

您可以将查询作为子查询引用，并根据所需列执行排序。

select type, brand, total
from (
select Type, Brand, sum(Units) as total
from mytable
group by Type, Brand, sum(Units) WITH ROLLUP) t
order by type, total desc

Answer 2

您希望首先根据类型的总数为其总计进行排序。在MySQL中，可以使用显式聚合和join来完成这一任务。

select tb.*
from (select Type, Brand, sum(Units) as sumunits
      from mytable
      group by Type, Brand
     ) tb join
     (select Type, sum(Units) as sumunits
      from mytable
      group by Type
     ) t
     on tb.type = t.type
order by t.sumunits desc, tb.sumunits desc;