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

[newcjh2008]

有一个客户主表Guest，GuestID是唯一的， 要以guest为主表，外连接GuestCar 和 GuestRoom 等多个表，
Guest表与GuestCar，GuestRoom表都是一对多的关系

现在要做一个连接查询，最终的结果行数是以Guest表中行数为准，连接GuestCar/GuestRoom表时如果有记录则只需取第一行(或者随便取一行也可)，如果无关联记录则对应字段返回Null

Guest
-----------------
GuestID GuestName
1 Lee
2 Ma
3 Wang


GuestCar
---------------------
GuestID Car
1 AA
1 BB
3 EE

GuestRoom
------------------
GuestID Room
1 1802
2 1901
2 1902
3 1205
3 1209


希望得到的查询结果
GuestID Car Room
1 AA 1802
2 NULL 1901
3 EE 1205


请大家分析下，多谢～～～

[newcjh2008]

首先表示感谢！
单个连接是可以这样写，但这个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条记录

[dawugui]

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

[liangCK]

反正你也只是随便要一条.那就这样子吧

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 

[liangCK]

靠..又是老乌龟.

[newcjh2008]

[Quote=引用 2 楼 liangCK 的回复:]
SQL codeslect 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
[/Quote]

多个表连接，而且间隔多层表如何写更好呢？

例如 A是主表， B、C、D、E 与 A 连， B1 与 B 连(B对B1是一对多的关系)， C1 与 C 连(C对C1是一对多的关系)

[newcjh2008]

实际情况要再复杂些

例如GuestCar中还有一个CarType字段，连接的时候只要CarType=1的记录中的一条

[liangCK]

实际情况再复杂些..
那你干脆一开始就说实际情况..绕了这么一大圈..

[青锋-SS]

[Quote=引用 13 楼 liangCK 的回复:]
实际情况再复杂些..
那你干脆一开始就说实际情况..绕了这么一大圈..
[/Quote]

[dawugui]

[Quote=引用 13 楼 liangCK 的回复:]
实际情况再复杂些..
那你干脆一开始就说实际情况..绕了这么一大圈..
[/Quote]

[you_tube]

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

[csshan]

帮顶是美德，回帖别情绪化，解决问题是关键，分那么重要吗？

[newcjh2008]

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

[newcjh2008]

梁老师的方法是可行的，

扩充下：

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）更好的方法来消除这种 多表连接的 重复（其实严格来说不是重复，只是忽略附表的多记录，只取一行）

[newcjh2008]

[Quote=引用 17 楼 csshan 的回复:]
帮顶是美德，回帖别情绪化，解决问题是关键，分那么重要吗？
[/Quote]

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

[liangCK]

删除重复的太多了..

--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 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')
go

select * , px = identity(int,1,1) into tmp from tb

select 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')
go

select 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 行受影响)
*/

[newcjh2008]

以前还有一个笨的方法是先对 要连接的附表 做个视图，

例如下面的代码：
tb_GroupBus 是一个记录每个团拥有的bus情况，一个group可能有多辆bus，但是查询的时候只取一辆


SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY GroupBusID) AS 'RowID'
,[GroupBusID]
,[GroupID]
,[BusID]
,[ServiceStartDate]
,[ServiceEndDate]
,[Remark]
FROM [tb_GroupBus]) T
WHERE T.RowID=1

[xiaoliaoyun]

如果是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') 
go

create 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
go

create 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)y

drop table Guest
drop table GuestCar
drop table GuestRoom
drop function f_form_car
drop function f_form_room

[newcjh2008]

哦，mygod～～ 这么多～～

[newcjh2008]

首先感谢两位大侠给我开启了思路

那个使用函数的方法也很不错，是走出单一连接查询怪圈的一种新思路～～


经常在实际项目中碰到十几个表连接又连接的查询，很多层，一不小心还容易产生重复，控制不了查询结果的行数：

而且还遇到一个普遍的情况就是，有时候给定条件或参数的单个查询可以用很多方法，但组合查询起来一下子就难了起来，还得考虑效率～

[liangCK]

那就结了吧.