把tab分开,先统计A有重复记录的,再统计A没有重复的,相加select sum(sum1) from
(
select count(*) as sum1
from tab1 where B=1 and A in
(
select A
from tab1
group by A
having count(A)>1
)
union all
select count(*) as sum1
from tab1 where A in
(
select A
from tab1
group by A
having count(A)=1
)
);
(
select count(*) as sum1
from tab1 where B=1 and A in
(
select A
from tab1
group by A
having count(A)>1
)
union all
select count(*) as sum1
from tab1 where A in
(
select A
from tab1
group by A
having count(A)=1
)
);
select * from scott.tt t
minus
select * from scott.tt t where
a in
(select a cnt from scott.tt t
group by a
having count(*) >1
)
and b <> 1
)group by a,b
写的实在妙!