mysql查询，列出每个用户在多个表中出现的次数

lf5gs5x2 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(366)

我在重新设计我的问题以便更清楚。我有几个（确切地说是5个）表，其中一个用户只能出现一次。我想列出每个用户出现的次数。
例子：


**employess table**

email | cn | more columns.. user@gr.com | name | etc

**intranet table**

email | data1 | more columns.. user@gr.com | something | etc
user1@gr.com | something | etc
user2@gr.com | something | etc

**sap table**

email | data1 | more columns.. 
user@gr.com | something | etc
user1@gr.com | something | etc
user2@gr.com | something | etc

**solman table**

email | data1 | more columns.. 
user@gr.com | something | etc
user1@gr.com | something | etc
user2@gr.com | something | etc

**orfeo table**

email | data1 | more columns.. 
user@gr.com | something | etc
user3@gr.com | something | etc

**saul table**

email | data1 | more columns.. 
user@gr.com | something | etc
user3@gr.com | something | etc

**EXPECTED RESULT**

   email      | Name        | Total
user@gr.com   | name user   | 5  (this is the only user in all the 5 tables)   
user1@gr.com  | name user1  | 3
user2@gr.com  | name user2  | 3  
user3@gr.com  | name user2  | 2

我尝试了左连接，但它只返回一个记录。不是名单。。。我做了什么´我做错了？

mysql Join List ranking

来源：https://stackoverflow.com/questions/52998676/mysql-query-to-list-how-many-times-each-user-appears-in-several-tables

1条答案

按热度按时间

xlpyo6sf1#

这种类型的查询需要使用 UNION ALL :

SELECT b.email, b.name, count(*)
from 
(
SELECT email from table1
UNION ALL
SELECT email from table2
UNION ALL
SELECT email from table3
UNION ALL
SELECT email from table4
UNION ALL
SELECT email from table5
) as a inner join employess b on a.email = b.email
GROUP BY b.email, b.name

赞(0）回复(0）举报 2021-06-18

我来回答

mysql查询，列出每个用户在多个表中出现的次数

1条答案

相关问题

热门标签

最新问答