Reference:
Http://blog.sina.com.cn/s/blog_6c9d65a10101bkgk.html
Http://www.jb51.net/article/39302.htm
1, use distinct to weight (suitable for querying the total number of the whole table)
There are multiple schools + teachers to contribute, need to count the total number of authors
Select COUNT (author) as Total from files each author has a lot of contributions, there are duplicate records here. Select distinct author from files; it is possible that the names of teachers in both schools are the same, and only one error is counted. Select DISTINCT Author,sid a combined unique value from the files statistic (author + school ID), the result is the correct result, but how do you know how many people there are?
Select COUNT (Distinct AUTHOR,SID) as total from files
2, GROUP by group to go to the weight (suitable for groups based on criteria to query the total number of each group)
Select Author, count (distinct ID) from the files group by Sid
3. Record the sum of the numbers of the two tables, and the two tables are queried separately
SELECT SUM (c)
From
(SELECT COUNT (DISTINCT from_user_id, message_id) c
From Im_message
WHERE dr = 0 and Message_status = 2 and User_type = 1 and to_user_id = 2
UNION All
SELECT COUNT (DISTINCT group_id, message_id) c
From Im_messagerefgroup
WHERE dr = 0 and user_id = 2
)
As temp;
MySQL data deduplication and record totals