How to use ThinkPHP to obtain group

Using ThinkPHP to obtain the total number of group by statements may not be the expected result. The statements in tp are as follows:

$ Count = $ this-> xxx-> where ($ where)-> group ($ group)-> count ();

Generated SQL statement:

SELECT count (*) FROM 'XXX' WHERE ('biz _ date'> = '000000') AND ('biz _ date' <= '000000') group by biz_date

The total number is not the number of results set.

As shown in the following figure:

The result is the total number of groups grouped by the group by field.

The correct way to get the total number of results is:

// Total number of returned results
If ($ total ){
$ SQL = $ this-> xxx-> where ($ where)-> group ($ group)-> buildSql ();
$ SQL = 'SELECT count (*) as tp_sum from '. $ SQL. 'As TT ';
$ Result = $ this-> xxx-> query ($ SQL );
// Echo $ this-> xxx-> _ SQL (); exit;
Return $ result [0] ['TP _ sum'];
}

That is, the total number of group by statements is obtained through these SQL statements:

Select count (*) as tp_sum from (SELECT * FROM 'XXX' WHERE ('biz _ date'> = '2016 ') AND ('biz _ date' <= '000000') group by biz_date) as tt
So OK.

Group by instance

Instance 1

Data table:

Name subject score
Zhang San Chinese 80
James math 98
James English 65
Li Si language 70
Li Si math 80
Li Si English 90

Expected query results:

Name, Chinese, mathematics, and English
Zhang San 80 98 65
Li Si 70 80 90

Code

Create table testScore
(   
Tid int primary key identity (1, 1 ),
Tname varchar (30) null,
Ttype varchar (10) null,
Tscor int null
)   
Go
--- Insert data
Insert into testScore values ('Zhang San', 'China', 80)
Insert into testScore values ('Zhang San', 'mat', 98)
Insert into testScore values ('Zhang San', 'English ', 65)
Insert into testScore values ('li Si', 'China', 70)
Insert into testScore values ('li Si', 'mat', 80)
Insert into testScore values ('li Si', 'English ', 90)

Select tname as 'name ',
Max (case ttype when 'China' then tscor else 0 end) 'China ',
Max (case ttype when 'mate' then tscor else 0 end) 'mate ',
Max (case ttype when 'Then tscor else 0 end)''
From testScore
Group by tname