使用多个表计算不同值的出现次数

Question

I have 3 tables in a database that have similar values and the same table structure. I am trying to get the number of occurrences of each value by unique user.

DB Structure
View on SQLFiddle

TABLE_1

user | value | id 

TABLE_2

user | value | id 

TABLE_3

user | value | id 

I can run the following MySQL command to retrieve the desired results on 1 table at a time.

SELECT value,COUNT(*) as count FROM TABLE_1 GROUP BY value ORDER BY count DESC;

I need to run this command across the three tables at once in order to retrieve the unique occurrences of "value" among a list of "users" that contains numerous duplicates.

Answer 1

Given your comments, since you want to remove duplicates, use UNION to combine the data from the tables together:

SELECT value, COUNT(*) as count 
FROM (
  SELECT user, value, id
  FROM TABLE_1
  UNION
  SELECT user, value, id
  FROM TABLE_2
  UNION
  SELECT user, value, id
  FROM TABLE_3 ) t
GROUP BY value 
ORDER BY count DESC;

• Updated Fiddle