求教一个SQL

[yjyq61]

加入说我有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字段不能预设，只要是有相同的就分组

[yjyq61]

引用 2 楼 claro 的回复:

--瞎写的
--瞎编的表数据
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相同的数据

[大明湖畔_帅锅]

看着 算比较简单 最好上数据

[claro]

--瞎写的
--瞎编的表数据
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

[哈特比尔波]

这还不简单啊！用a,b,c,d,e来分别代表这几个表，无论你几个表在什么数据库都可以，如果数据库相同就什么都不加，如果数据库不同就在表名前加数据库名和点号就可以了。最近这几天被公司的异常整安逸了。一直在看这些代码。 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.name ok,代码就这样。

[claro]

引用 3 楼 yjyq61 的回复:

----------------------------------------------- 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

参考5F的即可。

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

[搞怪的索引]

引用 7 楼 jascjasc 的回复:

with 
  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(+);

NB

[jascjasc]

with 
  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(+);

[s63403048]

引用 7 楼 jascjasc 的回复:

with 
  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(+);

学习了.