There is a table, the content is as follows:



The ID is the primary key, in the hope that query results is ID after sorting, the same name and type of continuous data of ID, realize the statistical results are as follows:



To ask god what should the implementation, thank you,

CodePudding user response:

The set @ v=null;
The set @ n=0;

Select the name, type, the sum (count) as the count,
If (@ v!=concat (name, type), @ n:=@ the n + 1, @ as t, n)
@ v:=concat (name, type)
From the table
Group by t

CodePudding user response:

reference 1st floor csdn_castiel response:

set @ v=null;
The set @ n=0;

Select the name, type, the sum (count) as the count,
If (@ v!=concat (name, type), @ n:=@ the n + 1, @ as t, n)
@ v:=concat (name, type)
From the table
Group by t

Thank you, this function has been basically achieved, but I still have a question want to ask, before issue a place not describe clearly, database type value may be empty, this time will be a problem, for example:


Hope the type for the empty lines at this time together, but the execution result and the front line:


And if a little don't understand, why group, increasing the value of t is a multiple of 2, ignoring group, increasing the value of t is reasonably

CodePudding user response:

You can be in the select ID, Name, Type, count the from tb_test ORDER BY ID to do some changes here
Change the type is null to a value

Select ID, Name, the if (isnull (Type), 1, Type), the count from tb_test ORDER BY ID

CodePudding user response:

The increasing power of 2 problems
In the select does not add the count, sum, Max function, such as increasing or + 1
Should be a mysql when performing such functions for the condition judgment again n + 1