如何用一条sql语句统计两张表的记录数之和？

windild

如题,请各位指教。

zgahj

select sum(cou) from (select count(*) from table_1 union select count(*) from table_2);

amiescort

楼上的兄弟在union时应该需要加别名把?

pydwh

[quote]原帖由 "zgahj"]select sum(cou) from (select count(*) from table_1 union select count(*) from table_2);[/quote 发表：

如果两张表的记录数相同用union是不对的,因为union会消除重复项,应该用union all代替.

1. SQL>; select * from testa;
   
2. 
   
3.        NUM
   
4. ----------
   
5.          1
   
6.          2
   
7. 
   
8. SQL>; select * from testb;
   
9. 
   
10.        NUM
    
11. ----------
    
12.          2
    
13.          3
    
14. 
    
15. SQL>; select sum(cou) from(select count(*) cou from testa union select count(*) c
    
16. ou from testb);
    
17. 
    
18.   SUM(COU)
    
19. ----------
    
20.          2
    
21. 
    
22. SQL>; select sum(cou) from(select count(*) cou from testa union all select count(
    
23. *) cou from testb);
    
24. 
    
25.   SUM(COU)
    
26. ----------
    
27.          4

复制代码

这样做也行,先union all得到两表的结果集再统计.

1. SQL>; select count(*) from (select 1 from testa union all select 1 from testb);
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.          4

复制代码

windild

强! 谢谢各位了!

toworm

select (select count(*) from a)+(select count(*) from b) from dual;

xulb

我也顶一下

西藏喇嘛

[quote]原帖由 "toworm"]select (select count(*) from a)+(select count(*) from b) from dual;[/quote 发表：

还是这种方法好！推荐！顶一下！

西藏喇嘛

[quote]原帖由 "toworm"]select (select count(*) from a)+(select count(*) from b) from dual;[/quote 发表：

还是这种方法好！推荐！顶一下！

西藏喇嘛

[quote]原帖由 "toworm"]select (select count(*) from a)+(select count(*) from b) from dual;[/quote 发表：

还是这种方法好！推荐！顶一下！