select a,(select count(*) from 表名 where a=temp.a)数量 FROM 表名 as temp
根据实际情况,我要这样写:SELECT *,((SELECT COUNT(a) FROM t1 WHERE a = A.a) AS total) FROM t1 A,t2 B WHERE A.a=B.bb AND A.aa='1'有问题吗?
select tp.a,(select count(*) from youTable where a=tp.a)as total from youTable as tp
Select 表.a,tmp.Total From 表,(Select Total=Count(a),a From 表 Group by a)tmp where 表.a=tmp.a
Select 表.a,tmp.Total From 表,(Select Total=Count(a),a From 表 Group by a)tmp where 表.a=tmp.a order by tmp.Total
---下面给出一个测试环境.我在SQL2000上运行证明成功。不知仁兄在什么环境下通不过。Create table ab(nno char(2),Mc char(30)) Insert into ab values('1','A1') Insert into ab values('1','A2') Insert into ab values('1','A3') Insert into ab values('1','A4') Insert into ab values('2','B1') Insert into ab values('2','B2') Insert into ab values('2','B3') Insert into ab values('3','C1') Insert into ab values('3','C2') Insert into ab values('4','D1') Select ab.NNo,tmp.Total From ab, (Select Total=Count(NNo),NNo From ab Group by NNo)tmp where ab.nno=tmp.nno order by tmp.Total
group by a
from youTable as tp
where 表.a=tmp.a
where 表.a=tmp.a order by tmp.Total
Insert into ab values('1','A1')
Insert into ab values('1','A2')
Insert into ab values('1','A3')
Insert into ab values('1','A4')
Insert into ab values('2','B1')
Insert into ab values('2','B2')
Insert into ab values('2','B3')
Insert into ab values('3','C1')
Insert into ab values('3','C2')
Insert into ab values('4','D1')
Select ab.NNo,tmp.Total From ab,
(Select Total=Count(NNo),NNo From ab Group by NNo)tmp
where ab.nno=tmp.nno order by tmp.Total