当使用Count时，Mysql只返回一行 [英] Mysql returns only one row when using Count

问题描述

我正在使用这个查询：

p>

  SELECT *，
 COUNT（*）AS页面
 FROM notis 
 WHERE cid = 20 
 ORDER BY nid DESC 
 LIMIT 0，3 
  

...阅读3但是我想要得到总行数。

问题是...

。当我使用count查询只返回一行，但如果我删除
COUNT（*）AS页 - 我得到3行，因为我应该。显然，我错过了这里的东西。

解决方案

是的，计数是一个聚合运算符，没有group by子句）

也许做两个单独的查询？没有必要让行返回数据和总行数，因为这些数据不属于同一行。

如果你真的想要它，你可以这样做：

  SELECT *，（select count（*）FROM notis WHERE cid = 20） 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方言。

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.

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.

解决方案

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

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.

这篇关于当使用Count时，Mysql只返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！