A problem related to an SQL query has a table groups, which contains many fields: idusercosttypeaddtimeuptimenote ..... there are 5 types in type, namely 001-005. The result is to query the total number of January-005 in the last 6 months, such as July 1, 1 to 31, select & nbsp; count (*) & nbsp; from & an SQL query related question for data with TYPE-
There is a table groups, which contains many fields
Id
User
Cost
Type
Addtime
Uptime
Note
.....
There are 5 types in type, namely 001-005,
The result is to query the total number of-in the last 6 months,
For example, you can query data from January 1, 31, and TYPE.
Select count (*) from groups where type = '001' and addtime <= '20180101' and addtime> = '20180101'
Select count (*) from groups where type = '002' and addtime <= '20180101' and addtime> = '20180101'
Select count (*) from groups where type = '003 'and addtime <= '20180101' and addtime> = '20180101'
Select count (*) from groups where type = '004 'and addtime <= '20180101' and addtime> = '20180101'
Select count (*) from groups where type = '005 'and addtime <= '20180101' and addtime> = '20180101'
You need to query 30 times for 6 months.
If many people log on to this system, it takes a lot of time to query such SQL statements. I have tried it. if all three people open this page, the response takes more than three seconds.
My idea is whether I can write the query results to a table. in this way, I directly query the data of the table during the query, and then I can set a time for the SQL data.
Automatically query results and update them to a table. In this way, it is faster to query data directly from a table than to query distinct using count. but how can this problem be achieved? what I use now is the most stupid
Solution: query them one by one.
------ Solution --------------------
Select type, left (addtime, 6) as addtime, count (*) as cnt from groups group by 1, 2
------ Solution --------------------
Even in does not need group by type, DATE_FORMAT (addtime,'m % ').
------ Solution --------------------
select count(*) from groups where addtime>='20130101' and addtime <= '20130630' group by MONTH(addtime),type
------ Solution --------------------
Use group by + 1
Study how to write
I cannot use the cache to reduce queries...
