08:49:54 SQL> select * from t1; FILEID D ---------- - 1 a 2 b 3 c已用时间: 00: 00: 00.00 08:50:08 SQL> select * from t2; FILEID D STEP ---------- - ---------- 1 a 1 1 b 2 1 a 3 2 a 1 2 c 2已用时间: 00: 00: 00.00 08:50:11 SQL> select dep,count(distinct fileid) fielcount from (select fileid,de p from t1 union select fileid,dep from t2) group by dep;D FIELCOUNT - ---------- a 2 b 2 c 2已用时间: 00: 00: 00.00
很感谢大家的回复,现在接着进一步提问: 表1: fileid dep filezt 1 a 2 2 b 1 3 c 1 …… 表2: fileid dep step 1 a 1 1 b 2 1 c 3 2 a 1 2 c 2 这次想要统计的结果是: dep filecount fileztcount a 2 1 b 2 1 c 2 0 这里filecount的计算同上,fileztcount统计的是在表1中fileid对应的filezt为2的数量,比如说对于a,filecount里的2指的是fileid为1和2的两个,而1和2对应的filezt分别为2和1,fileztcount只统计filezt等于2的值,所以最后得到的结果是1,同理,c对应的是fileid 为2和3,而这两个对应的filezt均为1所以最后得到的fileztcount为0 这能否通过一句sql得到呢?拜托大家帮忙了, 另外,我的MSN是:[email protected] 欢迎技术交流
select dep,count(*) filecount ,sum(ezt) fileztcount from (select table2.dep,decode(table1.filezt,2,1,0) ezt from table2,table1 where table2.fileid=table1.fileid(+) ) t group by dep;
11:23:06 SQL> select a.dep,a.fielcount,b.fielzcount from 11:23:16 2 ( 11:23:16 3 select 11:23:16 4 dep,count(distinct fileid) fielcount 11:23:16 5 from 11:23:16 6 (select fileid,dep from t1 union select fileid,dep from t2) gro up by dep) a, 11:23:16 7 ( 11:23:16 8 select 11:23:16 9 dep,sum(step) fielzcount 11:23:16 10 from 11:23:16 11 (select decode(filezt,2,1,0) step,dep from t1 union select deco de(step,2,1,0),dep from t2) group by dep) b 11:23:16 12 where a.dep=b.dep;D FIELCOUNT FIELZCOUNT - ---------- ---------- a 2 1 b 2 1 c 2 1
select dep,count(distinct fileid) from table2 group by dep;
---------- -
1 a
2 b
3 c已用时间: 00: 00: 00.00
08:50:08 SQL> select * from t2; FILEID D STEP
---------- - ----------
1 a 1
1 b 2
1 a 3
2 a 1
2 c 2已用时间: 00: 00: 00.00
08:50:11 SQL> select dep,count(distinct fileid) fielcount from (select fileid,de
p from t1 union select fileid,dep from t2) group by dep;D FIELCOUNT
- ----------
a 2
b 2
c 2已用时间: 00: 00: 00.00
表1: fileid dep filezt
1 a 2
2 b 1
3 c 1
……
表2: fileid dep step
1 a 1
1 b 2
1 c 3
2 a 1
2 c 2
这次想要统计的结果是: dep filecount fileztcount
a 2 1
b 2 1
c 2 0
这里filecount的计算同上,fileztcount统计的是在表1中fileid对应的filezt为2的数量,比如说对于a,filecount里的2指的是fileid为1和2的两个,而1和2对应的filezt分别为2和1,fileztcount只统计filezt等于2的值,所以最后得到的结果是1,同理,c对应的是fileid 为2和3,而这两个对应的filezt均为1所以最后得到的fileztcount为0
这能否通过一句sql得到呢?拜托大家帮忙了,
另外,我的MSN是:[email protected] 欢迎技术交流
(select table2.dep,decode(table1.filezt,2,1,0) ezt
from table2,table1
where table2.fileid=table1.fileid(+)
) t
group by dep;
11:23:16 2 (
11:23:16 3 select
11:23:16 4 dep,count(distinct fileid) fielcount
11:23:16 5 from
11:23:16 6 (select fileid,dep from t1 union select fileid,dep from t2) gro
up by dep) a,
11:23:16 7 (
11:23:16 8 select
11:23:16 9 dep,sum(step) fielzcount
11:23:16 10 from
11:23:16 11 (select decode(filezt,2,1,0) step,dep from t1 union select deco
de(step,2,1,0),dep from t2) group by dep) b
11:23:16 12 where a.dep=b.dep;D FIELCOUNT FIELZCOUNT
- ---------- ----------
a 2 1
b 2 1
c 2 1
from table2
group by dep;