select * from 表 a where account=(select min(account) from 表 where id=a.id)
--或: select a.* from 表 a join(select id,account=min(account) from 表 group by id) b on a.id=b.id and a.account=b.account
--下面是测试--测试数据 declare @表 table(ID int,Account varchar(6),Name varchar(2),Price int,Addr varchar(10)) insert into @表 select 1,'00001','x',9,'xx' union all select 1,'00002','y',9,'xx' union all select 2,'00001','x',9,'xx' union all select 2,'00002','x',9,'xx' union all select 2,'00003','uu',9,'xx' union all select 3,'00001','x',9,'xx' union all select 3,'00002','x',9,'xx'--方法1. select * from @表 a where account=(select min(account) from @表 where id=a.id)--方法2. select a.* from @表 a join( select id,account=min(account) from @表 group by id ) b on a.id=b.id and a.account=b.account/*--测试结果ID Account Name Price Addr ----------- ------- ---- ----------- ---------- 1 00001 x 9 xx 2 00001 x 9 xx 3 00001 x 9 xx(所影响的行数为 3 行)ID Account Name Price Addr ----------- ------- ---- ----------- ---------- 1 00001 x 9 xx 2 00001 x 9 xx 3 00001 x 9 xx(所影响的行数为 3 行) --*/
如果Acount数据要是没有规律是不好处理的 可采用如下select * from t aaa where aaa.id+aaa.count+aaa.Name+aaa.Price +aaa.Addr =(select top 1 id+acount+name+price+addr from t where aaa.id =id group by id)
select a.* from 表 a join(select id,account=min(account) from 表 group by id) b on a.id=b.id and a.account=b.account
declare @表 table(ID int,Account varchar(6),Name varchar(2),Price int,Addr varchar(10))
insert into @表
select 1,'00001','x',9,'xx'
union all select 1,'00002','y',9,'xx'
union all select 2,'00001','x',9,'xx'
union all select 2,'00002','x',9,'xx'
union all select 2,'00003','uu',9,'xx'
union all select 3,'00001','x',9,'xx'
union all select 3,'00002','x',9,'xx'--方法1.
select * from @表 a
where account=(select min(account) from @表 where id=a.id)--方法2.
select a.* from @表 a join(
select id,account=min(account) from @表 group by id
) b on a.id=b.id and a.account=b.account/*--测试结果ID Account Name Price Addr
----------- ------- ---- ----------- ----------
1 00001 x 9 xx
2 00001 x 9 xx
3 00001 x 9 xx(所影响的行数为 3 行)ID Account Name Price Addr
----------- ------- ---- ----------- ----------
1 00001 x 9 xx
2 00001 x 9 xx
3 00001 x 9 xx(所影响的行数为 3 行)
--*/
如果Acount数据要是没有规律是不好处理的
可采用如下select * from t aaa where aaa.id+aaa.count+aaa.Name+aaa.Price +aaa.Addr =(select top 1 id+acount+name+price+addr from t where aaa.id =id group by id)