计数和排序 [英] Counting and ordering
本文介绍了计数和排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
mysql> describe taps;
+------------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+-------------------+-------+
| tag | int(11) | NO | | NULL | |
| station | int(11) | NO | | NULL | |
| time_Stamp | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+-----------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)
如何获得每个标签访问的站点数量并按访问次数排序 - 我们只计算对每个站点的一次访问,即使有几次?
How do I get the number of staions visited by each tag and sort by the most visits - where we only count one visit to each staion, even if there were several?
随机数据:
mysql> select * from taps;
+-----+---------+---------------------+
| tag | station | time_Stamp |
+-----+---------+---------------------+
| 50 | 12 | 2016-05-10 20:02:41 |
| 33 | 7 | 2016-05-10 20:02:42 |
| 34 | 13 | 2016-05-10 20:02:42 |
| 31 | 3 | 2016-05-10 20:02:42 |
| 9 | 10 | 2016-05-10 20:02:42 |
| 2 | 11 | 2016-05-10 20:02:42 |
| 33 | 13 | 2016-05-10 20:02:42 |
| 46 | 12 | 2016-05-10 20:02:42 |
| 45 | 1 | 2016-05-10 20:02:42 |
| 34 | 7 | 2016-05-10 20:02:42 |
| 4 | 10 | 2016-05-10 20:08:18 |
| 17 | 5 | 2016-05-10 20:08:19 |
| 46 | 7 | 2016-05-10 20:08:19 |
| 16 | 5 | 2016-05-10 20:08:19 |
| 33 | 3 | 2016-05-10 20:08:19 |
| 31 | 4 | 2016-05-10 20:08:19 |
| 1 | 13 | 2016-05-10 20:08:19 |
| 35 | 2 | 2016-05-10 20:08:19 |
| 9 | 12 | 2016-05-10 20:08:19 |
| 26 | 7 | 2016-05-10 20:08:19 |
+-----+---------+---------------------+
20 rows in set (0.00 sec)
推荐答案
使用 COUNT
函数和 GROUP BY
查找每个标签的访问次数代码>.
Use COUNT
funtion and GROUP BY
to find the number of visits for each tag
.
并使用ORDER BY
子句按访问次数降序对结果进行排序.
And use ORDER BY
clause to sort the result in descending of the number of visits.
查询
SELECT tag, COUNT(DISTINCT station) AS `no of visits`
FROM taps
GROUP BY tag
ORDER BY COUNT(DISTINCT station) DESC;
这篇关于计数和排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文