表与表外连接，1：N的关系，如何只取外连接表的第一行

Question

有一个客户主表Guest，GuestID是唯一的， 要以guest为主表，外连接GuestCar 和 GuestRoom 等多个表，
Guest表与GuestCar，GuestRoom表都是一对多的关系现在要做一个连接查询，最终的结果行数是以Guest表中行数为准，连接GuestCar/GuestRoom表时如果有记录则只需取第一行(或者随便取一行也可)，如果无关联记录则对应字段返回NullGuest
-----------------
GuestID GuestName
1  Lee
2  Ma
3  Wang
GuestCar
---------------------
GuestID Car
1 AA
1 BB
3 EEGuestRoom
------------------
GuestID Room
1 1802
2 1901
2 1902
3 1205
3 1209
希望得到的查询结果
GuestID Car Room
1 AA 1802
2 NULL 1901
3 EE 1205
请大家分析下，多谢～～～

Answer 1

select guestid,(select top 1 car from guestcar where guestid=guest.guestid) as car,(select top 1 room from guestroom where guestid=guest.guestid) as room  from guset

Answer 2

slect guestid
     ,car=(select top 1 from guestcar where guestid=a.guestid order by car)
     ,room=(select top 1 from guestroom where guestid=a.guestid order by room)
from guest

Answer 3

Guest left join GuestCar on 条件 left join GuestRoom　条件

Answer 4

首先表示感谢！
单个连接是可以这样写，但这个Guest主表要连接10来个表，有的是1：1的关系，有的是1：N的关系
有没有更好的写法？以前的写法是(省略其它表的连接)：select g.GuestID, gc.Car, gr.Room
FROM Guest g LEFT OUTER JOIN 
GuestCar gc ON g.GuestID=gc.GuestID LEFT OUTER JOIN
GuestRoom gr ON g.GuestID=gr.GuestID导致的结果是：可能会因GuestCar或GuestRoom中有多条记录而出现
假设Guest有3条记录，某个GuestID在GuestCar中有5条记录
则最终查询出现7行记录，其中5行是一个GuestID的，而希望的结果是每个Guest只要1条记录

Answer 5

selevt t1.* , t2.Car , t3.Room from Guest t1
left join
(select GuestID , min(Car) car from GuestCar group by GuestID) t2
on t1.GuestID = t2.GuestID
left join
(select GuestID , min(Room) Room from GuestRoom group by GuestID) t3
on t1.GuestID = t3.GuestID

Answer 6

反正你也只是随便要一条.那就这样子吧select g.GuestID, gc.Car, gr.Room 
FROM Guest g 
   LEFT OUTER JOIN 
 (select GuestID,Max(Car) Car from GuestCar group by GuestID) gc
    ON g.GuestID=gc.GuestID 
   LEFT OUTER JOIN 
 (select GuestID,max(Room) Room from GuestRoom group by GuestID) gr
    ON g.GuestID=gr.GuestID 

Answer 7

多个表连接，而且间隔多层表如何写更好呢？例如 A是主表， B、C、D、E 与 A 连， B1 与 B 连(B对B1是一对多的关系)， C1 与 C 连(C对C1是一对多的关系)

Answer 8

实际情况要再复杂些例如GuestCar中还有一个CarType字段，连接的时候只要CarType=1的记录中的一条

Answer 9

slect guestid
     ,car
     ,room
from guest
left join GuestCar on Guest.Guestid = GuestCar.Guestid
left join GuestRoom on Guest.Guestid = GuestRoom.Guestid

Answer 10

对不住梁老师和dawugui老师了，都怪俺，是怕一开始说不明白，所以先简单说三个表

Answer 11

梁老师的方法是可行的，扩充下：select g.GuestID, gc.Car, gr.Room 
FROM Guest g 
   LEFT OUTER JOIN 
 (select GuestID,Max(Car) Car from GuestCar WHERE CarType=1 group by GuestID) gc
    ON g.GuestID=gc.GuestID 
   LEFT OUTER JOIN 
 (select GuestID,max(Room) Room from GuestRoom group by GuestID) gr
    ON g.GuestID=gr.GuestID 
不过Car是个字符型不定长的字段，不知道有没有比（Max ... Group by）更好的方法来消除这种 多表连接的 重复（其实严格来说不是重复，只是忽略附表的多记录，只取一行）

Answer 12

大家都误会了，梁老师和dawugui老师决不是在意得分的人，他们以前也帮助过我的，很多很多其它人也得到了及时的帮助，他们是值得尊敬的！！

Answer 13

删除重复的太多了..--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect m.name,m.val,m.memo from
(
  select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
  select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/

Answer 14

以前还有一个笨的方法是先对 要连接的附表 做个视图，例如下面的代码：
tb_GroupBus 是一个记录每个团拥有的bus情况，一个group可能有多辆bus，但是查询的时候只取一辆
SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY GroupBusID) AS 'RowID'
,[GroupBusID]
,[GroupID]
,[BusID]
,[ServiceStartDate]
,[ServiceEndDate]
,[Re]
FROM [tb_GroupBus]) T
WHERE T.RowID=1

Answer 15

如果是SQL 2005,也可以用下面这种写法:
随机取第一条记录select *
from Guest a
outer apply(select top 1 Car from GuestCar b where a.GuestID = b.GuestID)x
outer apply(select top 1 Room from GuestRoom c where a.GuestID = c.GuestID)y不过这样会有数据丢失,最好使用函数,把多条记录合并成一个字符串create table Guest (GuestID int, GuestName varchar(10))
insert into Guest values(1,  'Lee') 
insert into Guest values(2,  'Ma') 
insert into Guest values(3,  'Wang') 
go
create table GuestCar(GuestID int ,Car varchar(10))
insert into GuestCar values(1, 'AA') 
insert into GuestCar values(1, 'BB') 
insert into GuestCar values(3, 'EE') 
go
create table GuestRoom(GuestID int, Room varchar(10))
insert into GuestRoom values(1, '1802') 
insert into GuestRoom values(2, '1901') 
insert into GuestRoom values(2, '1902') 
insert into GuestRoom values(3, '1205') 
insert into GuestRoom values(3, '1209') 
gocreate function f_form_car(@GuestID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
    set @str = ''
    select @str = @str + ',' + Car  from GuestCar where GuestID = @GuestID
    select @str=substring(@str,2,8000)
    return @str
end
gocreate function f_form_room(@GuestID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
    set @str = ''
    select @str = @str + ',' + Room  from GuestRoom where GuestID = @GuestID
    select @str=substring(@str,2,8000)
    return @str
end
go
select *, dbo.f_form_car (GuestID)as Car,dbo.f_form_room (GuestID) as Room from Guest
select *
from Guest a
outer apply(select top 1 Car from GuestCar b where a.GuestID = b.GuestID)x
outer apply(select top 1 Room from GuestRoom c where a.GuestID = c.GuestID)ydrop table Guest
drop table GuestCar
drop table GuestRoom
drop function f_form_car
drop function f_form_room

Answer 16

哦，mygod～～ 这么多～～