如:
select name ,count(*) from t1 group by name union select name ,count(*) from t2 group by name结果可能是
name count
a 5
b 6
a 2
c 1我想实现这样的结果:
name count
a 7
b 6
c 1即按name相同将count求和,name字段值不重复,该怎么实现呢
select name ,count(*) from t1 group by name union select name ,count(*) from t2 group by name结果可能是
name count
a 5
b 6
a 2
c 1我想实现这样的结果:
name count
a 7
b 6
c 1即按name相同将count求和,name字段值不重复,该怎么实现呢
select name,sum(A) FROM
(
select name ,count(*) A from t1 group by name union select name ,count(*) A from t2 group by name
)
GROUP BY NAMe
SELECT NAME, SUM(C)
FROM (SELECT NAME, COUNT(*) C
FROM T1
GROUP BY NAME
UNION
SELECT NAME, COUNT(*) C FROM T2 GROUP BY NAME)
GROUP BY NAME;
SQL> SELECT *
2 FROM T1;TNAME TVALUE
---------- ----------
CHINA AA
CHINA BB
CHINA CC
USA CC
USA BB
CANADA AA6 rows selected
SQL> SELECT *
2 FROM T2;TNAME TVALUE
---------- ----------
ENGLAND AA
ENGLAND BB
ENGLAND CC
CHINA CC
CHINA BB
FRENCH AA6 rows selectedSQL>
SQL> SELECT DECODE(T1.TNAME, T2.TNAME, T1.TNAME, T1.TNAME || T2.TNAME) "TNAME",
2 COUNT(DISTINCT T1.TVALUE) + COUNT(DISTINCT T2.TVALUE) "COUNTS"
3 FROM T1
4 FULL OUTER JOIN T2 ON T1.TNAME = T2.TNAME
5 GROUP BY DECODE(T1.TNAME, T2.TNAME, T1.TNAME, T1.TNAME || T2.TNAME);TNAME COUNTS
-------------------- ----------
CANADA 1
CHINA 5
ENGLAND 3
FRENCH 1
USA 2SQL>
(
select name ,count(*) count from t1 group by name union select name ,count(*) from t2 group by name
)
group by name 或select name,count(*) from
(
select name from t1 union select name from t2
)
group by name