选择计数和数据 [英] SELECT COUNT and Data

问题描述

我有以下查询，该查询返回的数据限制为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 (

推荐答案

您需要使用

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

这篇关于选择计数和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！