Mysql stored procedures grouping statistics-CodePudding

Now I am grouping according to vid equals 4 query ANSWERAD query for
Select count (answerad), answerad
The from it_plan
Where vid=4
Group by answerad

The result is
Backbone network, 15
Backbone network, access network, 3
Backbone network, access network, the client, 28

Now demand is the query results for
Backbone network, 46
Access networks, 31
Client, 28

O great god teach

CodePudding user response:

 select (select count (*) from it_plan where vid=Dr. Id and answerad like concat (a.a nswerad, '%')), answerad 
The from it_plan as a 
Where Dr. Id=4 
Group by answerad;

CodePudding user response:

To create an auxiliary table, similar to:

 CREATE TABLE Temp_Num (xh INT PRIMARY KEY); - create digital auxiliary table 


The SET @ I=0; 

INSERT INTO Temp_Num (xh) - write Numbers auxiliary table 
SELECT @ I:=@ I + 1 
The FROM information_schema. COLLATIONS

 SELECT t.n, SUM (num) AS num 
The FROM 
(
The SELECT SUBSTRING_INDEX (SUBSTRING_INDEX (LEFT (s.n, CHAR_LENGTH (s.n) - 1), ', ', l.x h), ', ', 1) AS n, 
S.n um 

The FROM 
(
SELECT 'backbone network,' n, 15 num UNION ALL 
SELECT 'backbone network, access network, and 3 UNION ALL 
SELECT 'backbone network, access network, client, and 28 
) s 

The JOIN Temp_Num l 

ON ((l.x h & lt;=((LENGTH (s.n) - 1 - LENGTH (the REPLACE (s.n, ', ', '))) + 1))) 
) t 
GROUP BY t.n 
The ORDER BY the SUM (num) desc

CodePudding user response:

The above code to run one at a time, run to build table first, and then to insert the data, finally in running the query:

CodePudding user response:

Select sum (case when answer_AD like '% % of backbone networks' then one else 0) s1,
The sum (case when answer_AD like '% % access networks' then one else 0) s2,
The sum (case when answer_AD like '% % configuration fault then 1 else 0) s3 from it_plain where vid=4;