SQL - 获取大多数投票的日期 [英] SQL - Get date of most votes cast

问题描述

SQL 新手，请原谅菜鸟问题

new to SQL, excuse the noob question

投票

vote_id     vote_date
1           2013-08-11
2           2013-08-12
3           2013-08-12
4           2013-08-12
5           2013-08-13
6           2013-08-14

我想选择最受欢迎的日期 (2013-08-12) 以及该日期的投票数 (3).

I would like to select the most popular date (2013-08-12) as well as how many votes were cast on that date(3).

推荐答案

这是获得指定结果的一种方法:

Here's one way to get the specified result:

SELECT v.vote_date
     , COUNT(1) AS votes_cast
  FROM votes v
 GROUP
    BY v.vote_date
 ORDER
    BY votes_cast DESC
     , v.vote_date DESC
 LIMIT 1

GROUP BY 子句允许将多组行聚合为一行.在此示例中，我们将具有相同 vote_date 值的行分组".COUNT 函数是一个聚合函数，它对组内的所有行进行运算，并返回该组的单个值.(COUNT 从零开始，对于表达式具有非空值的每一行，计数中都会添加一个.)您还可以使用 SUM(1) 代替 COUNT(1) 在此查询中.

The GROUP BY clause allows groups of rows to be aggregated into a single row. In this example, we are "grouping" rows that have the same value of vote_date. The COUNT function is an aggregate function, which operates on all rows within a group, and returns a single value for the group. (COUNT starts at zero, and for each row that has a non-null value for the expression, one is added to the count.) You could also use SUM(1) in place of COUNT(1) in this query.

ORDER BY 返回指定序列中的行.在此示例中，我们想要具有最高 COUNT 值的行.所以我们按降序对这个表达式上的行进行排序.如果有两个或多个vote_date 具有相同的最高"计数，我们将首先返回最新的vote_date.

The ORDER BY returns the rows in the specified sequence. In this example, we want the row that has the highest value for COUNT. So we sort the rows on this expression in descending order. In the case of two or more vote_date with the same "highest" count, we'll return the latest vote_date first.

LIMIT n 子句限制返回的行数，因此只返回 n 行.

The LIMIT n clause restricts the number of rows returned, so that only n rows are returned.

(这不是唯一一个会返回指定结果的 SQL 语句，它只是一个例子.)

(This is not the only SQL statement that will return the specified result, it's just one example.)

这篇关于SQL - 获取大多数投票的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！