用一个简单子查询,应该可以满足你的要求吧。create table zhf
(
name char(4),
value number(4)
);
insert into zhf values('a', 1);
insert into zhf values('a', 2);
insert into zhf values('a', 3);
insert into zhf values('b', 1);
insert into zhf values('b', 2);
insert into zhf values('c', 1);
commit;select a.name, count_1, nvl(count_2, 0) as count_2
from (select name, count(*) as count_1 from zhf group by name) a,
(select name, count(*) as count_2 from zhf where value>1 group by name) b
where a.name = b.name(+);以下是结果NAME COUNT_1 COUNT_2
---- ---------- ----------
a 3 2
b 2 1
c 1 0
(
name char(4),
value number(4)
);
insert into zhf values('a', 1);
insert into zhf values('a', 2);
insert into zhf values('a', 3);
insert into zhf values('b', 1);
insert into zhf values('b', 2);
insert into zhf values('c', 1);
commit;select a.name, count_1, nvl(count_2, 0) as count_2
from (select name, count(*) as count_1 from zhf group by name) a,
(select name, count(*) as count_2 from zhf where value>1 group by name) b
where a.name = b.name(+);以下是结果NAME COUNT_1 COUNT_2
---- ---------- ----------
a 3 2
b 2 1
c 1 0
group by a,cn
select a,count(*),cn from(select a,(select count(*) from t n where m.a=n.a and n.b>1) cn from t m)
group by a,cn