sql多表联合查询

现在有3个表 A、B、C
A表字段有:user_id,timestamp ...
B表字段有:user_id,timestamp ...
C表字段有:user_id,timestamp ...

A、B、C三个表的user_id字段有重复的值,现在我想要把A、B、C这三张表的所有user_id联合起来算出count(*),并且去除重复的值。

请问sql语句要怎么写?

4个回答

select count(1) from
(SELECT t1.user_id from t1
union
select t2.user_id from t2
UNION
select t3.user_id from t3) a

union本身就去除重复了,count(1)用于统计,使用常量能提高效率

SELECT COUNT(*) from (SELECT user_Id from usera UNION SELECT user_Id from userb UNION SELECT user_Id from userc) as ttt;

需要分组。e.g.
SELECT COUNT(1) FROM (SELECT 1 FROM A a, B b, C c WHERE a.user_id=b.user_id AND a.user_id=c.user_id AND b.user_id=c.user_id GROUP BY a.user_id);

select count(user_id) from (select user_id from A union all select user_id from B union all select user_id from C) group by user_id

顺带一提kjmmlzq19851226 的方法一定是错的,a表如果没有数据,b,c表都有数据,那它的方法是查不出数据的。

QiangGe2Dai
QiangGe2Dai 是滴,多谢指正,考虑不周啊O(∩_∩)O~
7 年多之前 回复
立即提问
相关内容推荐