select * from 账户
where 储蓄所编号 in (select 编号 from 储蓄所 group by 所属城市 having count(编号)=min(count(编号)))
这样写会有问题,那样应该怎么写?
最佳答案
//希望kutpbpb的回答能对你有所帮助!
//首先说明一下嵌套聚合函数是不支持的!
//下面的SQL查询在SQL Server 2005中可以正确返回运行结果;
//customer代表账户表,Bank代表储蓄所表;
//Cbno代表customer表中的储蓄所编号,Bno代表储蓄所编号;
//City代表储蓄所所属城市,其中bk为返回结果的临时表。
正确代码:
select *
from customer as C,Bank as B
where C.Cbno=B.Bno and B.City in
( select City
from Bank
group by City
having count(Bno)=(select min(Nm)
from ( select City,count(Bno)
from Bank
group by City
) as bk(Cy,Nm)
)
);