An alternative method for mysql to obtain the total number of record rows of group by, mysqlgroup

An alternative method for mysql to obtain the total number of record rows of group by, mysqlgroup

Mysql can obtain the total number of record groups in a field within the group by statement, but cannot count the number of records in the group.

Mysql SQL _CALC_FOUND_ROWS writes the following statements in many paging programs:

The Code is as follows:

Select count (*) from 'table' WHERE ......;

Total number of records that meet the criteria

The Code is as follows:

SELECT * FROM 'table' WHERE... limit M, N;

To query the data to be displayed on the page, you can change the statement:

The Code is as follows:

SELECT SQL _CALC_FOUND_ROWS * FROM 'table' WHERE... limit M, N;

SELECT FOUND_ROWS ();

Therefore, it can be implemented with the SQL _CALC_FOUND_ROWS and FOUND_ROWS () functions provided by mysql.

The Code is as follows:

SELECT SQL _CALC_FOUND_ROWS t3.id, a, bunch, of, other, stuff FROM t1, t2, t3 WHERE (associate t1, t2, and t3 with each other) GROUPBY t3.id LIMIT 10, 20 SELECT FOUND_ROWS () as count;

Use the preceding two statements to complete the total number of records that meet the requirements of group.

Appendix:

Simple use of group:

The Code is as follows:

'Select column_id, count (*) as count FROM my_table group by column_id ';

Mysql group

SELECT
B,
COUNT (1) AS number of rows
FROM
Table
GROUP
B;

How to obtain the record row number in the MySQL query result set

Standard methods (8i or later) can be used in Oracle, or non-standard ROWNUM; ms SQL Server provides the ROW_NUMBER () function in version 2005; however, MySQL does not seem to have such built-in functions. Although LIMIT can easily filter the number and position of returned result sets, the row numbers of the filtered records cannot be selected. It is said that MySQL has long wanted to add this function, but I haven't found it yet. The solution is to use predefined user variables: set @ mycnt = 0; select (@ mycnt: = @ mycnt + 1) as ROWNUM, othercol from tblname order by othercol; in this way, the row number information is saved in the query result set ROWNUM. The purpose of this row number information is to sort the data according to certain rules and retrieve the sorted data of a row, we also want to know where this row of data is in the previous sorting. For example, the following code snippet: set @ mycnt = 0; select * from (select (@ mycnt: = @ mycnt + 1) as ROWNUM, othercol