选择计数和数据

本文介绍了选择计数和数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下查询，该查询返回的数据限制为5.

I have the following query which returns data with a limit of 5.

SELECT a.* FROM ( SELECT category, description, price, date_added, datetime_created FROM vc_expense e1 WHERE trip_id=:trip_id AND description LIKE :search UNION ALL SELECT category, description, NULL, NULL, NULL FROM vc_expense_default e2 WHERE description LIKE :search ) AS a GROUP BY description, price ORDER BY CASE WHEN price IS NOT NULL THEN 1 WHEN description LIKE :search_start THEN 2 WHEN description LIKE :search THEN 3 ELSE 4 END, datetime_created DESC, date_added DESC LIMIT 5

一切正常，但是我还想包括在 LIMIT之前还返回了多少项目.像这样的东西会很好:

Everything works as intended, but I want to also include how many items was also returned before the LIMIT. Something like this would be good:

{ count: 32, data: [items, limited to 5] }

我没有运气尝试过以下方法:

I have tried the following with no luck:

SELECT COUNT(a.*) AS count, a.* AS data FROM (

推荐答案

您需要使用SQL_CALC_FOUND_ROWS调用function_found-rows"rel =" nofollow noreferrer> FOUND_ROWS().在不调用SQL_CALC_FOUND_ROWS的情况下，FOUND_ROWS()只会返回5(您的限制).

You need to use FOUND_ROWS() with SQL_CALC_FOUND_ROWS called during the Select statement. Without SQL_CALC_FOUND_ROWS being called, FOUND_ROWS() will simply return 5 (your Limit).

使用以下内容:

SELECT SQL_CALC_FOUND_ROWS a.* FROM ( SELECT category, description, price, date_added, datetime_created FROM vc_expense e1 WHERE trip_id=:trip_id AND description LIKE :search UNION ALL SELECT category, description, NULL, NULL, NULL FROM vc_expense_default e2 WHERE description LIKE :search ) AS a GROUP BY description, price ORDER BY CASE WHEN price IS NOT NULL THEN 1 WHEN description LIKE :search_start THEN 2 WHEN description LIKE :search THEN 3 ELSE 4 END, datetime_created DESC, date_added DESC LIMIT 5

触发该查询后，您需要触发另一个查询，以获取总行数.

After firing this query, you need to fire another query, to get the total number of rows.

SELECT FOUND_ROWS() AS overall_count_without_limit;

来自文档:

SELECT语句可以包含LIMIT子句以限制数量 服务器返回给客户端的行数.在某些情况下， 希望知道该语句将返回多少行 没有LIMIT，但没有再次运行该语句.获得 此行计数，请在SELECT中包含一个SQL_CALC_FOUND_ROWS选项 语句，然后再调用FOUND_ROWS()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include an SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

第二个SELECT返回一个数字，该数字指示第一个SELECT的行数 如果写入时没有LIMIT，SELECT将返回 条款.

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

性能:

如果您使用的是SELECT SQL_CALC_FOUND_ROWS，MySQL必须计算 完整的结果集中有很多行.但是，这比 在没有LIMIT的情况下再次运行查询，因为结果集不需要 发送给客户.

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

附加说明:

• 您应该避免使用SELECT *.阅读:为什么SELECT *被认为有害?
• 此外，无论何时使用GROUP BY，您的SELECT列表都应仅包含那些在功能上取决于GROUP BY子句中的列和/或汇总的列/表达式的列.阅读:与only_full_group_by相关的错误在MySql中执行查询时
• 在较新版本的MySQL(版本> = 5.7.5 )中，此查询将引发错误

• You should avoid using SELECT *. Read: Why is SELECT * considered harmful?
• Also, whenever you are using GROUP BY, your SELECT list should contain only those columns which are either functionally dependent on columns in the GROUP BY clause, and/or Aggregated columns/expressions. Read: Error related to only_full_group_by when executing a query in MySql
• In newer versions of MySQL (version >= 5.7.5), this query will throw error