ID SID name
1 S0001 张三 111 111 111
2 S0002 张三 NULL 111 222
3 S0003 李四 NULL NULL NULL
4 S0004 李四 NULL NULL 北京查找学生,每个名字只查出一条记录
如下:
1 S0001 张三 111 111 111
3 S0003 李四 NULL NULL NULL我自己用了两种办法,都不行
select a.* from student a
where a.SID=(select top 1 b.sid from student b where a.sid = b.sid);select top 1 * from student group by name;
1 S0001 张三 111 111 111
2 S0002 张三 NULL 111 222
3 S0003 李四 NULL NULL NULL
4 S0004 李四 NULL NULL 北京查找学生,每个名字只查出一条记录
如下:
1 S0001 张三 111 111 111
3 S0003 李四 NULL NULL NULL我自己用了两种办法,都不行
select a.* from student a
where a.SID=(select top 1 b.sid from student b where a.sid = b.sid);select top 1 * from student group by name;
where a.SID=(select top 1 b.sid from student b where a.name= b.name order by sid);-- or
select a.* from student a
where not exists (select 1 from student b where a.name= b.name and a.sid>b.sid);
cross apply (select * from student b where a.sid = b.sid) t
cross apply (select top 1 * from student b where a.sid = b.sid) t
row_number和cte,自己查下写法,不再写了