sql查询问题，在线等 急！！！！！！！！

create table test(so_id int,so_name varchar(20),so_type int)

insert test(so_id,so_name,so_type) values(1,'国家发改委',1)

insert test(so_id,so_name,so_type) values(2,'价格监测中心',2)

insert test(so_id,so_name,so_type) values(3,'广州电台',3)

insert test(so_id,so_name,so_type) values(4,'广州商业电台',3)



select a.so_id,a.so_name,a.so_type,b.count from test a

left join

(select so_type,count(so_type)as count from test group by so_type) b 

on a.so_type=b.so_type

--> 测试数据: #T

if object_id('tempdb.dbo.#T') is not null drop table #T

create table #T (so_id int,so_name nvarchar(12),so_type int)

insert into #T

select 1,'国家发改委',1 union all

select 2,'价格监测中心',2 union all

select 3,'广州电台',3 union all

select 4,'广州商业电台',3



select so_id,so_name,so_type,

       (select count(*) from #T where so_type=t.so_type) so_count

from #T t



/*

so_id       so_name      so_type     so_count

----------- ------------ ----------- -----------

1           国家发改委        1           1

2           价格监测中心       2           1

3           广州电台         3           2

4           广州商业电台       3           2



(4 行受影响)

*/

select soid,so_name,so_type,

[count]=(select count(1) from tb where so_type=t.so_type)

from tbname t