select a.*
from view_base_stu a join(
select base_id,stu_id=min(stu_id) frm view_base_stu
group by base_id
)b on a.base_id=b.base_id and a.stu_id=b.stu_id
from view_base_stu a join(
select base_id,stu_id=min(stu_id) frm view_base_stu
group by base_id
)b on a.base_id=b.base_id and a.stu_id=b.stu_id
from view_base_stu a join(
select base_id,stu_id=min(stu_id) from view_base_stu
group by base_id
)b on a.base_id=b.base_id and a.stu_id=b.stu_id
没有的话select identity(int,1,1) as iid,* into #tmp from view_base_stuselect * from #tmp a
where iid = (select min(iid) from #tmp where base_id = a.base_id)drop table #tmp
base1 |||| stu2 |||| 山东联通 ||| [email protected] | 李四 |||||| [email protected]
base1 |||| stu3 |||| 山东联通 ||| [email protected] | 王五 |||||| [email protected]
base1 |||| stu4 |||| 山东联通 ||| [email protected] | 赵明 |||||| [email protected]
base2 |||| null |||| 中国电信 ||| [email protected] | null |||||| null
base3 |||| null |||| 中软 ||||||| [email protected] | null |||||| null
base4 |||| null |||| 浪潮 ||||||| [email protected] | null |||||| null
这样清楚一些
select * from view_base_stu a
where stu_id=(select min(stu_id) from view_base_stu where a.base_id=base_id )
insert t1
select '10','100'
union all select '10','200'
union all select '20','300'create function get_a()
returns @re table(a varchar(10))
as
begin
insert @re
select distinct a from t1
return
endselect *,b=(select top 1 b from t1 where a=aa.a) from dbo.get_a() aadrop table t1
drop function get_a
select *
from trade_tel
where 主键 in(select min(主键) from trade_tel group by bas_name)
from view_base_stu a join(
select base_id,stu_id=min(stu_id) from view_base_stu
group by base_id
)b on a.base_id=b.base_id and a.stu_id=b.stu_id