●●●●●一句SQL的写法.这是无法完成的任务吗,看看有没有高手来实现?●●

[xiaobeibei]

表结构.
假设有一个表叫T1, 里面有3项.A,B,C.
A,B 是表的主键.

实现功能.
统计表中记录的个数.
条件:
如果A有重复的记录.就只统计在这些重复的记录中B为1的记录.
其他的记录按照正常个数统计.

如果用SQL来写的话.....该怎么实现? 先谢谢大家了.

[maohaisheng]

把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
)
);

[nashan]

写的漂亮!

[yuaiwu]

select a,b,count(*) from(
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

[nashan]

a,b是主键,本身具有唯一性,不需要group by,有待改进.

[ww_xiaohua]

TO: maohaisheng()
写的实在妙！

[Lastdrop]

因为A,B为主键，那么A重复的记录中最多只可能有一条记录的B为1，同时从楼主的题意看，A重复的记录中至少有一条记录的B为1，所以只要简单的使用下面的SQL就OK了。

select count(distinct A) from T1;