一个简单的查询求解

[guanlianan]

--表B_CallNo 数据--
ID	CtrlNo	CallNo	Lib
1	101	TP393	1
2	101	TP393	2
			
--表B_Barcode数据--
ID	Barcode	CtrlNo
1	A0001	101
2	A0002	101
3	A0003	101
4	A0004	101
		

用查询语句：
select  substring( CallNo , 1 ,2) as class,count(Barcode) as Total 
from B_Barcode a , B_CallNo b 
where a.CtrlNo = b.CtrlNo 
group by substring( CallNo , 1 ,2) 
order by Total desc

查询得结果：

class   Total
----    -----
 TP	  8

我的结果是要得到4,请问该怎样写查询语句？？因为表B_CallNo有重复的CtrlNo，导致查询的结果也有重复了。谢谢


------------以下是给大家的调试准备----------------

--表B_CallNo--
create table B_CallNo
(ID int primary key identity(1,1),
CtrlNo int,
CallNo varchar(50),
Lib int
)
insert into B_CallNo(CtrlNo, CallNo, Lib) values('101','TP393',1)
insert into B_CallNo(CtrlNo, CallNo, Lib) values('101','TP393',2)

--表B_Barcode--
create table B_Barcode
(ID int primary key identity(1,1),
Barcode varchar(50),
CtrlNo int
)
insert into B_Barcode(Bardcode , CtrlNo) values('A0001','101')
insert into B_Barcode(Bardcode , CtrlNo) values('A0002','101')
insert into B_Barcode(Bardcode , CtrlNo) values('A0003','101')
insert into B_Barcode(Bardcode , CtrlNo) values('A0004','101')

[wwwwb]

count(distinct Barcode)

[rucypli]

select substring( CallNo , 1 ,2) as class,count(distinct Barcode) as Total
from B_Barcode a , B_CallNo b
where a.CtrlNo = b.CtrlNo
group by substring( CallNo , 1 ,2)
order by Total desc

[guanlianan]

[Quote=引用 2 楼 的回复:]

select substring( CallNo , 1 ,2) as class,count(distinct Barcode) as Total
from B_Barcode a , B_CallNo b
where a.CtrlNo = b.CtrlNo
group by substring( CallNo , 1 ,2)
order by Total desc
[/Quote]
灰常感谢你，我自己用下面的语句完成了，也给大家参考一下，不过觉得你写的语句比较好理解一些：

select substring( CallNo , 1 ,2 ) as class , count(Barcode) as Total 
from (select distinct CtrlNo , CallNo from B_CallNo ) a , B_Barcode b 
where a.CtrlNo = b.CtrlNo 
group by substring( CallNo , 1 ,2) 
order by Total desc