Mysql使用Count时只返回一行

2021-12-30 00:00:00 limit count mysql

好吧，我刚刚遇到了一种我以前从未见过或没有注意到的奇怪行为.

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

我正在使用这个查询:

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

...阅读 3 个项目，但同时我想获得总行数.

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

...当我使用计数查询只返回一行，但如果我删除COUNT(*) AS pages -- 我得到了我想的 3 行.显然，我在这里遗漏了一些东西.

...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 是一个聚合运算符，它只返回一行(没有 group by 子句)

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

也许做两个单独的查询?让行返回数据和总行数是没有意义的，因为这些数据不属于一起.

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

或者这个:

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

嵌套表达式的差异取决于您的 SQL 方言.

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

相关文章