MySQL count Multiple Tables data instance detailed _mysql

I. Realization of DEMAND

Recently in the achievement system, the achievements of many dimensions are related to the number of users, such as the number of fans reached 50, awarded Fame title, the user point of praise to 100 times, awarded the point of praise giant title and so on.

Number of fans in the user_relation table

Point of praise in the user_praise table

According to previous practice, if you need to query the user's data, and because the data is in different tables, so often will be separated from a number of SQL queries, such as:

SELECT Count (*) Mysh from user_relation WHERE other_uid = 123456;

SELECT Count (*) DZSS from user_praise WHERE praise_uid = 123456;

The data in the query is then encapsulated in an object, return to the client or other use, if you need to count less than the table can be, but like the achievement system, often have a variety of achievements, we now involve 12 of tables, if you follow this query, then write 12 SQL query 12 times, It's a bit of a bummer.

Can I use an SQL statement to achieve this?

The answer is yes.

We can use operations like this to integrate data from multiple tables into a single table UNION .

You will first UNION overwrite the above statement with the following:

Select WRZM,MYSH,SGBH from (

 select COUNT (*) wrzm,0 mysh,0 SGBH from user_witness WHERE plan_uid = 123456

 UNION AL L

 Select 0 wrzm,count (*) mysh,0 SGBH from user_relation WHERE other_uid = 123456

 UNION all

 SELECT 0 wrzm,0 H,count (*) SGBH from plan_stage WHERE uid = 123456 and status = 1

) t;

According to the above query results are:

Mysql_count_results

We found the result to be a bit presentable, and if we could make a row of wrzm mysh sgbh three rows of data in the table, that would be fine. So it's natural for us to think of it SUM (of course I didn't think at first, after a friend reminded), so rewrite the above SQL:

Select SUM (WRZM) wrzm,sum (Mysh) mysh,sum (SGBH) SGBH from (

 select COUNT (*) wrzm,0 mysh,0 SGBH from User_witness WHERE Plan_uid = 123456

 UNION all

 SELECT 0 wrzm,count (*) mysh,0 SGBH from user_relation WHERE other_uid = 123456

 UNI On all

 SELECT 0 wrzm,0 mysh,count (*) SGBH from plan_stage WHERE uid = 123456 and status = 1

) t;

And the results are as follows:

Mysql_count_result_2

At this point, we get the results we want, and we implement the statistics for more than one table in a single SQL statement count .

Third, expand

As shown in figure, we can get only a user's data, but we need to count all of the user_info users, then it is very simple, we rewrite:

Select Uid,sum (WRZM) wrzm,sum (Mysh) mysh,sum (SGBH) SGBH from (

 select Plan_uid uid,count (*) wrzm,0 mysh,0 SGBH from us Er_witness
  GROUP by Plan_uid

 UNION all

 SELECT other_uid uid,0 wrzm,count (*) mysh,0 SGBH from user_relation<  C5/>group by Other_uid

 UNION all

 SELECT uid,0 wrzm,0 mysh,count (*) SGBH from plan_stage WHERE status = 1
  GROUP by uid

] t GROUP by UID;

The results of the query are:

Mysql_count_results_3

In this result, if we need to see a specific user, then at the end add
WHERE uid = 123456 can be, if you want to sort, then directly add ORDER BY can.

Summarize

The above is the entire content of this article, I hope that the content of this article for everyone in the use or learning MySQL can help, if you have questions you can message exchange.