表
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的数量合计
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的数量合计
from 表名 as T
where id in (select min(id) from 表名 where type=T.type group by type)
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=t1delete 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
from 表名 as T
where id=(select min(id) from 表名 where type=T.type group by type)