--那就这样--查询 select distinct * from @t a where 姓名=(select min(姓名) from @t where 编号=a.编号)--或: select distinct * from @t a where 姓名=(select max(姓名) from @t where 编号=a.编号) order by 编号
select * from 表 a where 姓名 in (select min(姓名) from 表 group by 编号)
select * from 表 a where 姓名 in (select min(姓名) from 表 group by 编号) select * from 表 a where 姓名 in (select max(姓名) from 表 group by 编号) 上面的效率比这个高: select * from 表 a where 姓名=(select top 1 姓名 from 表 where 编号=a.编号)
realgz(realgz) 已经给出了答案 来拿分吧,
select * from table, (select mid(编号),姓名 from table group by 编号) temp1 where temp1.编号 = table.编号 and temp1.姓名 = table.姓名
select distinct * from tablename tem where 编号=(select min(编号) from tablename where 姓名=tem.姓名)
select * from tablename tem where 编号=(select min(编号) from tablename where 姓名=tem.姓名)
insert @table values(1,'张三')
insert @table values(1,'张3')
insert @table values(2,'李四')
insert @table values(3,'王五')
insert @table values(4,'刘二麻子')select * from @table
where PATINDEX('%3%', 姓名) = 0
select distinct * from @t a where 姓名=(select min(姓名) from @t where 编号=a.编号)--或:
select distinct * from @t a where 姓名=(select max(姓名) from @t where 编号=a.编号) order by 编号
select * from 表 a where 姓名 in (select max(姓名) from 表 group by 编号)
上面的效率比这个高:
select * from 表 a where 姓名=(select top 1 姓名 from 表 where 编号=a.编号)
来拿分吧,
(select mid(编号),姓名 from table group by 编号) temp1
where temp1.编号 = table.编号
and temp1.姓名 = table.姓名
FROM [TableName]