SELECT COUNT(DISTINCT T.*) FROM (SELECT * FROM A UNION ALL SELECT * FROM B) T
如果两个表里还有一个TEXT字段,而且TEXT字段离得数据都一样。
--直接两个查询union就可以了,union直接去掉重复的数据, --union all则将所有的数据合并到一起 SQL> with a as( 2 select 1 id,'2010.4.5' dt from dual union all 3 select 1,'2010.4.6' from dual union all 4 select 1,'2010.4.7' from dual union all 5 select 2,'2010.4.6' from dual union all 6 select 2,'2010.4.7' from dual) 7 ,b as( 8 select 1 id,'2010.4.5' dt from dual union all 9 select 1,'2010.4.8' from dual union all 10 select 2,'2010.4.5' from dual union all 11 select 2,'2010.4.6' from dual union all 12 select 2,'2010.4.8' from dual) 13 select * from a 14 union 15 select * from b 16 / ID DT ---------- -------- 1 2010.4.5 1 2010.4.6 1 2010.4.7 1 2010.4.8 2 2010.4.5 2 2010.4.6 2 2010.4.7 2 2010.4.88 rows selected -- select * from a union all select * from b ID DT ---------- -------- 1 2010.4.5 1 2010.4.6 1 2010.4.7 2 2010.4.6 2 2010.4.7 1 2010.4.5 1 2010.4.8 2 2010.4.5 2 2010.4.6 2 2010.4.810 rows selected
select count(*) from ( select * from a union select * from b ) / COUNT(*) ---------- 8
SELECT COUNT(DISTINCT T.*) FROM (SELECT * FROM A UNION ALL SELECT * FROM B) T
如果两个表里还有一个TEXT字段,而且TEXT字段离得数据都一样。
--直接两个查询union就可以了,union直接去掉重复的数据,
--union all则将所有的数据合并到一起
SQL> with a as(
2 select 1 id,'2010.4.5' dt from dual union all
3 select 1,'2010.4.6' from dual union all
4 select 1,'2010.4.7' from dual union all
5 select 2,'2010.4.6' from dual union all
6 select 2,'2010.4.7' from dual)
7 ,b as(
8 select 1 id,'2010.4.5' dt from dual union all
9 select 1,'2010.4.8' from dual union all
10 select 2,'2010.4.5' from dual union all
11 select 2,'2010.4.6' from dual union all
12 select 2,'2010.4.8' from dual)
13 select * from a
14 union
15 select * from b
16 / ID DT
---------- --------
1 2010.4.5
1 2010.4.6
1 2010.4.7
1 2010.4.8
2 2010.4.5
2 2010.4.6
2 2010.4.7
2 2010.4.88 rows selected
--
select * from a
union all
select * from b
ID DT
---------- --------
1 2010.4.5
1 2010.4.6
1 2010.4.7
2 2010.4.6
2 2010.4.7
1 2010.4.5
1 2010.4.8
2 2010.4.5
2 2010.4.6
2 2010.4.810 rows selected
select count(*)
from (
select * from a
union
select * from b
)
/
COUNT(*)
----------
8