有两个表ta,tb,ta中两个字段
(学号)ta_fa,姓名(ta_fb),
tb结构为
(学号)tb_fa,(宿舍)tb_fb;
现假设一个学生可以不住在宿舍,也可以同时住两个宿舍。
请问怎么查询所有学生的住宿情况(不住在宿舍时宿舍为空,住两个宿舍的只取一个宿舍号);
最好不借用临时表
(学号)ta_fa,姓名(ta_fb),
tb结构为
(学号)tb_fa,(宿舍)tb_fb;
现假设一个学生可以不住在宿舍,也可以同时住两个宿舍。
请问怎么查询所有学生的住宿情况(不住在宿舍时宿舍为空,住两个宿舍的只取一个宿舍号);
最好不借用临时表
(学号)ta_fa (姓名)ta_fb
001 张三
002 李四表2 tb
(学号)tb_fa (宿舍)tb_fb
002 101
002 102(一个人可以有两个宿舍,也可以没有)结果
学号 姓名 宿舍
001 张三
002 李四 101
(
ta_fa int,
ta_fb varchar(2)
)
insert into @ta
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'declare @tb table
(
tb_fa int,
tb_fb varchar(3)
)
insert into @tb
select 1,'101' union all
select 1,'102' union all
select 2,'103' union all
select 4,'104'select * from @ta
select * from @tbselect ta_fa,isnull((select min(tb_fb) from @tb where ta.ta_fa=tb_fa),'')
from @ta ta/*
ta_fa ta_fb
----------- -----
1 aa
2 bb
3 cc
4 dd
tb_fa tb_fb
----------- -----
1 101
1 102
2 103
4 104ta_fa
----------- ----
1 101
2 103
3
4 104
*/
create table tb(ta_fa varchar(10),ta_fb varchar(10))
go
insert into ta select '001','张三'
union all select '002','李四'insert into tb select '002','101'
union all select '002','102'
goselect distinct a.ta_fa as '学号',a.ta_fb as '姓名',isnull(min(b.ta_fb),'') as '宿舍'
from ta a left outer join tb b on a.ta_fa=b.ta_fa
group by a.ta_fa,a.ta_fb
order by a.ta_fadrop table ta,tb
create table tb(ta_fa varchar(10),ta_fb varchar(10))
go
insert into ta select '001','张三'
union all select '002','李四'insert into tb select '002','101'
union all select '002','102'
goselect ta_fa as '学号',ta_fb as '姓名',isnull((select min(ta_fb) from tb where a.ta_fa=ta_fa),'') as '宿舍'
from ta a
--drop table ta,tb
From @ta A Left join (select max(tb_fb) as tb_fb,tb_fa from @tb group by tb_fa) B
on A.ta_fa=B.tb_fa
if object_id('tb') is not null drop table tb
go
create table ta(ta_fa varchar(10),ta_fb varchar(10))
create table tb(tb_fa varchar(10),tb_fb varchar(10))
go
insert into ta select '001','张三'
union all select '002','李四'insert into tb select '002','101'
union all select '002','102'
goselect 学号=ta.ta_fa, 姓名=min(ta.ta_fb), 宿舍=min(tb.tb_fb)
from ta left join tb
on ta.ta_fa=tb.tb_fa
group by ta.ta_fa