mysql 返回count,当使用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.

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.