Recently has been doing data statistics, in the process, encountered a good many situations are a table according to different conditions for multiple statistics, the previous practice is to count several times according to different conditions left join several times, although also can get the desired results, but the efficiency is too low, reflected in the page is very slow access.
Today in another encounter this situation, to a table according to different conditions to carry out seven or eight statistics, do not want in accordance with the previous ideas, in the online search, if you can use cases when the statement to statistics, the specific idea is as follows:
The first step: according to the common data, query out all the data, including the number of fields to be counted
The second step is to include the case in the Count function and then the mechanical write.
The third step: to see such a high efficiency, oneself in the side to steal the music.
Not much to say, on the code
--There is a table table1 field there id,name,sex,age--now requires statistics on the number of males, number of females, age 25, number of persons aged 26 select count (case when sex = ' gril ' then 1 end) Number of females, count (case if sex = ' gril ' then 1 end) Number of females, count ( case 1 end) Number of people aged 25, count (case if age = 1 end) 26 years old from table1 where 1 = 1
SQL makes multiple statistics on a table according to different conditions