34,838
社区成员




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