加入说我有5张表,每张表里都有name这个字段我想得到这5张表里 name字段相同的分别有几条数据,并且分组,但我不能确定name是啥比如说得到的数据是zhangsan 1 4 5 3 1
lisi 2 3 6 0 0
name为zhangsan 第一张表里有一条,第二张有4条...
name为lisi 第一张表里有2条,第二张里有3条...
这种格式的name字段不能预设,只要是有相同的就分组
lisi 2 3 6 0 0
name为zhangsan 第一张表里有一条,第二张有4条...
name为lisi 第一张表里有2条,第二张里有3条...
这种格式的name字段不能预设,只要是有相同的就分组
--瞎写的
--瞎编的表数据
with t1 as (
select 'zhangsan' name ,'1 4 5 3 1' col from dual
union all select 'lisi','2 3 6 0 0' from dual
union all select 'lisi','1 3 6 0 0' from dual)
,t2 as (
select 'zhangsan' name ,'2 4 5 3 1' col from dual
union all select 'lisi','3 3 6 0 0' from dual
union all select 'lisi','4 3 6 0 0' from dual
union all select 'lisi','5 3 6 0 0' from dual)
,t3 as (
select 'zhangsan' name ,'2 4 5 3 1' col from dual
union all select 'zhangsan','3 3 6 0 0' from dual
union all select 'zhangsan','4 3 6 0 0' from dual
union all select 'lisi','6 3 6 0 0' from dual
union all select 'wokao','7 3 6 0 0' from dual)--查询
select name,sum(cnt) cnt
from (
select name,count(1) cnt from t1 group by name
union all
select name,count(1) cnt from t2 group by name
union all
select name,count(1) cnt from t3 group by name
) t
group by name
having count(name) > 1
谢谢大哥帮忙打了这么多代码,现在有个问题就是,这个name无法预知,要是T1表里有个NAME叫A,T2表里也有个NAME叫A的,就得用这A分组,得出的是T1里有几个A,T2里有几个A ,T3里有几个A,这种
-----------------------------------------------
NAME count(T1) COUNT(T2) COUNT(T3) COUNT(T4) COUNT(T5) COUNT(T1+T2+T3+T4+T5)
中国 5 1 2 3 0 11
美国 1 0 0 0 0 1
法国 0 0 1 0 0 1得到的结果类似于上面的这种数据,最关键的就是 这个NAME,不能预设,只要任何一张表里有一条NAME的数据,就要得到这所有表里分别NAME相同的数据
select a.name,count(a.name),count(b.name),count(c.name),count(d.name),count(e.name)
from a,b,c,d,e
where a.name=b.name and b.name= c.name and c.name=d.name and d.name=e.nameok,代码就这样。
with t1 as (
select 'zhangsan' name ,'1 4 5 3 1' col from dual
union all select 'lisi','2 3 6 0 0' from dual
union all select 'lisi','1 3 6 0 0' from dual)
,t2 as (
select 'zhangsan' name ,'2 4 5 3 1' col from dual
union all select 'lisi','3 3 6 0 0' from dual
union all select 'lisi','4 3 6 0 0' from dual
union all select 'lisi','5 3 6 0 0' from dual)
,t3 as (
select 'zhangsan' name ,'2 4 5 3 1' col from dual
union all select 'zhangsan','3 3 6 0 0' from dual
union all select 'zhangsan','4 3 6 0 0' from dual
union all select 'lisi','6 3 6 0 0' from dual
union all select 'wokao','7 3 6 0 0' from dual)
--查询
select a.name,count(a.name),count(b.name),count(c.name),count(a.name)+count(b.name)+count(c.name)
from t1 a,t2 b,t3 c
where a.name=b.name and b.name= c.name
group by a.name
tb1 as (select name,count(*) as t1_count from t1 group by name),
tb2 as (select name,count(*) as t2_count from t2 group by name),
tb3 as (select name,count(*) as t3_count from t3 group by name),
tb4 as (select name,count(*) as t4_count from t4 group by name),
tb5 as (select name,count(*) as t5_count from t5 group by name),
tb6 as (select t1.name from t1 union all
select t2.name from t2 union all
select t3.name from t3 union all
select t4.name from t4 union all
select t5.name from t5 ),
tb7 as (select tb6.name from tb6 group by tb6.name)
select tb7.name,tb1.t1_count,tb2.t2_count,tb3.t3_count,tb4.t4_count,tb5.t5_count
from tb1,tb2,tb3,tb4,tb5,tb7
where tb7.name=tb1.name(+) and
tb7.name=tb2.name(+) and
tb7.name=tb3.name(+) and
tb7.name=tb4.name(+) and
tb7.name=tb5.name(+);