Detailed description of data instances in multiple tables in mySQL count, mysqlcount

Detailed description of data instances in multiple tables in mySQL count, mysqlcount

I. Implementation requirements

Recently, in the Achievement system, there are many dimensions related to the number of achievements. For example, the number of user fans reaches 50, the number of user likes reaches 100, and the number of likes reaches.

The number of fans isuser_relation Table

The number of likes isuser_praise Table

According to the previous practice, if you need to query the user's data, and because the data is in different tables, multiple SQL queries are often separated, for example:

SELECT count(*) mysh FROM user_relation WHERE other_uid =123456;

SELECT count(*) dzss FROM user_praise WHERE praise_uid = 123456;

Then, encapsulate the queried data in an object and return it to the client or another user. If the number of tables to be calculated is small, it is acceptable, but in the Achievement System, there are often a variety of achievements. We now involve 12 tables. If we follow this query, We need to write 12 SQL queries 12 times, which is a bit unpleasant.

2. Can I use an SQL statement?

The answer is yes.

We can useUNION Operation.

First, useUNION Rewrite the preceding statement:

SELECT wrzm,mysh,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 UNION ALL SELECT 0 wrzm,0 mysh,count(*) sgbh FROM plan_stage WHERE uid = 123456 AND status = 1) t;

According to the above query results:

Mysql_count_results

We found that this result is already decent.wrzm ,mysh ,sgbh The three rows of data are changed to one row. So we can naturally think of usingSUM (Of course, I didn't expect it at the beginning, as a friend reminded me), so I changed 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 UNION ALL SELECT 0 wrzm,0 mysh,count(*) sgbh FROM plan_stage WHERE uid = 123456 AND status = 1) t;

The result is as follows:

Mysql_count_result_2

At this point, we have obtained the expected results and implemented multiple tables in one SQL statement.count Statistics.

Iii. Expansion

We can only obtain user data, but what we need to calculate isuser_info It is also very simple for all users in. We will rewrite it again:

SELECT uid,sum(wrzm) wrzm,sum(mysh) mysh,sum(sgbh) sgbh FROM ( SELECT plan_uid uid,count(*) wrzm,0 mysh,0 sgbh FROM user_witness  GROUP BY plan_uid UNION ALL SELECT other_uid uid,0 wrzm,count(*) mysh,0 sgbh FROM user_relation  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 query result is:

Mysql_count_results_3

In this result, if we need to view a specific user, add
WHERE uid = 123456 You can simply addORDER BYYou can.

Summary

The above is all the content of this article. I hope the content of this article will be helpful for you when using or learning mysql. If you have any questions, please leave a message.