使用 COUNT 返回不同的结果 [英] Using COUNT returns different results

问题描述

我正在查询我数据库中的一个表；

I am querying a table in my database to;

1. 确定最新的 video_seek 值
2. 返回总行数

我不是 SQL 专家，我无法弄清楚为什么在 SQL 语句中使用 COUNT 与不使用 COUNT 的语句返回不同的结果.

I'm no expert with SQL and I cannot work out why using COUNT in a SQL statement returns different results compared to a statement that does not use COUNT.

查询:

SELECT video_seek, COUNT(*) AS total 
FROM videos 
WHERE video_id = 1 
  AND video_seek > 0 
  AND video_seek_end > 0 
ORDER BY video_seek DESC 
LIMIT 1

返回:

video_seek = 6012
total      = 199

<小时>

查询:

SELECT video_seek 
FROM videos 
WHERE video_id = 1 
  AND video_seek > 0 
  AND video_seek_end > 0 
ORDER BY video_seek DESC 
LIMIT 1

返回:

video_seek = 7098

<小时>

总数正确，但 video_seek 不正确，我不知道为什么结果会因是否使用 COUNT 而不同.

---

The total is correct but the video_seek is not and I do not know why the result is different depending on whether COUNT is used.

任何建议将不胜感激.

推荐答案

这是一个MySQL 特性.如果您使用任何其他 DBMS，您将收到缺少 GROUP BY 表达式"语法错误.

This is a MySQL peculiarity. If you were using any other DBMS you'd be getting a 'missing GROUP BY expression' syntax error.

COUNT() 是聚合函数，这意味着它将多行合并为一行.如果您没有 GROUP BY 子句，这意味着您想将所有匹配的行合并为一个，因此显示单个行中的值没有任何意义.正如我所说，其他数据库引擎只会警告您有关错误；相反，MySQL 只是从任意行(甚至不是随机行)中选取数据.

COUNT() is an aggregate function, what means that it combines several rows into one. If you don't have a GROUP BY clause that means you want to combine all matching rows into one, thus it doesn't make any sense to show values from an individual row. As I said, other database engines would just warn you about the error; MySQL, instead, simply picks data from an arbitrary row (not even a random row).

我对您的数据或业务逻辑一无所知，但是...

I know nothing about your data or business logic but...

SELECT MAX(video_seek) AS max_video_seek, COUNT(*) AS total
FROM videos
WHERE video_id = 1
AND video_seek > 0
AND video_seek_end > 0

(请注意 SQL 代码接受换行符.)

这篇关于使用 COUNT 返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！