求一SQL
表
id,   name,   type
1,aa,a1
2,bb,a1
3,cc,a1
4,dd,a2
5,ee,a3
结果
id,name,type,count
1,aa,a1,3
4,dd,a2,2

就是type相同的第一条，最后一列是此type的数量合计

------解决方案--------------------
select *,(select count(*) from 表名 where type=T.type) as [count]
from 表名 as T
where id in (select min(id) from 表名 where type=T.type group by type)
------解决方案--------------------
create table 表名(id int, name varchar(100), type varchar(100))

insert into 表名
select 1, 'aa ', 'a1 ' union all
select 2, 'bb ', 'a1 ' union all
select 3, 'cc ', 'a1 ' union all
select 4, 'dd ', 'a2 ' union all
select 5, 'ee ', 'a2 '

select *,(select count(*) from 表名 where type=T.type) as [count]
from 表名 as T
where id in (select min(id) from 表名 where type=T.type group by type)

drop table 表名
------解决方案--------------------
没有想好

随便写了个
declare @table table (id int, name char(2), type char(2))
insert into @table
select 1, 'aa ', 'a1 '
union all select 2, 'bb ', 'a1 '
union all select 3, 'cc ', 'a1 '
union all select 4, 'dd ', 'a2 '
union all select 5, '33 ', 'a2 '


create table #table (id int,name char(2),type char(2),cnt int)
insert into #table(id,name,type,cnt)
select id,name,type,cnt from @table,(select type as t1 ,count(type) as cnt from @table group by type ) as a
where type=t1

delete from #table where exists(select 1 from #table as t where #table.type=t.type and #table.id> t.id)

select * from #table
drop table #table
------解决方案--------------------
建议把id in修改成id=，这样效率能高一点

select *,(select count(*) from 表名 where type=T.type) as [count]
from 表名 as T
where id=(select min(id) from 表名 where type=T.type group by type)