表名:db
aa bb cc
3024594 45.00 Y
3024594 28.00 Y
3024594 66.00 Y
3024595 77.00 Y
3024595 22.00 Y
3024595 99.00 Y
要求结果:选择相同aa字段分类后的第一条记录
3024594 45.00 Y
3024595 77.00 Y
解决方法:
create table db(aa varchar(20), bb decimal(10,2), cc char(1))
insert db select '3024594 ', 45.00, 'Y '
union all select '3024594 ', 28.00, 'Y '
union all select '3024594 ', 66.00, 'Y '
union all select '3024595 ', 77.00, 'Y '
union all select '3024595 ', 22.00, 'Y '
union all select '3024595 ', 99.00, 'Y '
select id=identity(int, 1, 1), * into #T from db
select aa, bb, cc from #T as tmp
where not exists(select 1 from #T where aa=tmp.aa and id <tmp.id)
--result
aa bb cc
-------------------- ------------ ----
3024594 45.00 Y
3024595 77.00 Y
(2 row(s) affected)