使用Count时，Mysql只返回一行

Well I've just hit a weird behaviour that I've never seen before, or haven't noticed.

好吧,我刚刚遇到了一个我从未见过的奇怪的行为,或者没有注意到。

I'm using this query:

我正在使用此查询:

  SELECT *, 
         COUNT(*) AS pages 
    FROM notis 
   WHERE cid = 20 
ORDER BY nid DESC 
   LIMIT 0, 3

...to read 3 items but while doing that I want to get the total rows.

...阅读3个项目,但在这样做时我想得到总行数。

Problem is...

...when I use count the query only returns one row, but if I remove COUNT(*) AS pages -- I get the 3 rows as I'm suppose to. Obviously, i'm missing something here.

...当我使用count时,查询只返回一行,但是如果我删除COUNT(*)AS页面 - 我得到了3行,正如我想的那样。显然,我在这里遗漏了一些东西。

3 个解决方案

#1

Yeah, the count is an aggregate operator, which makes only one row returned (without a group by clause)

是的,count是一个聚合运算符,只返回一行(没有group by子句)

Maybe make two separate queries? It doesn't make sense to have the row return the data and the total number of rows, because that data doesn't belong together.

也许两个单独的查询?让行返回数据和总行数没有意义,因为该数据不属于一起。

If you really really want it, you can do something like this:

如果你真的想要它,你可以这样做:

SELECT *, (select count(*) FROM notis WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

or this:

SELECT N.*, C.total from notis N join (select count(*) total FROM notis WHERE cid=20) C WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

With variances on the nested expression depending on your SQL dialect.

根据您的SQL方言,嵌套表达式的差异。

#2

Using an aggregate function without a GROUP BY will always return one row, no matter what. You must use a GROUP BY if you want to return more than one row.

使用没有GROUP BY的聚合函数将始终返回一行,无论如何。如果要返回多行,则必须使用GROUP BY。

Note that on most RDBMS, such a query would have failed because it makes no sense.

请注意,在大多数RDBMS上,这样的查询会失败,因为它没有任何意义。

#3

This is inefficient, but will work:

这是低效的,但会起作用:

SELECT 
   *,
   (SELECT COUNT(*) FROM notis WHERE cid=20) AS pages
FROM notis 
WHERE cid=20 
ORDER BY nid DESC 
LIMIT 0,3

#1