MySQL对行进行计数和排序 [英] MySQL Count and sort rows

问题描述

在mysql中对数据列进行计数和排序并将结果输出为html列表的最佳方法是什么?

What is the best way to count and sort a column of data in mysql and output the results as a html list?

示例数据:

**Type**
Train
Car
Car
Car
Bus
Bus

输出应首先按最大计数项排序:

Output should be sorted with largest count item first:

 - car(3)
 - bus(2)
 - train (1)

此外，在大桌子上做这种不好的做法吗?

Also, is this bad practice to do on large table?

推荐答案

尝试以下查询:

select type, count(*) from vehicles group by type order by count(*) desc

关于:

此外，在大桌子上做这种不好的做法吗?

Also, is this bad practice to do on large table?

这取决于您的表引擎. MyISAM使用诸如count(*)之类的聚合函数非常快.但是，InnoDB必须扫描表并每次计数.因此，我不建议count(*)创建一个大的InnoDB表.相反，您可以在元表中存储一个"count"变量，并仅在插入/更新时对其进行更新.

It depends on your table engine. MyISAM is very fast with aggregate functions like count(*). However, InnoDB has to scan the table and count every time. So I would not recommend count(*)ing a large InnoDB table. Instead you could store a "count" variable in a meta table and update it on inserts/updates only.

这篇关于MySQL对行进行计数和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！