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

newcjh2008 2008-03-14 03:50:05

有一个客户主表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

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

...全文

286 点赞收藏 26

写回复

26 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

liangCK 2008-03-14

那就结了吧.

回复赞

newcjh2008 2008-03-14

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

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

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

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

回复赞

newcjh2008 2008-03-14

哦，mygod～～这么多～～

回复赞

xiaoliaoyun 2008-03-14

如果是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 2008-03-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]
,[Remark]
FROM [tb_GroupBus]) T
WHERE T.RowID=1

回复赞

liangCK 2008-03-14

删除重复的太多了..



--按某一字段分组取最大(小)值所在行的数据

(爱新觉罗.毓华 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 2008-03-14

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

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

回复赞

newcjh2008 2008-03-14

梁老师的方法是可行的，

扩充下：

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 2008-03-14

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

回复赞

csshan 2008-03-14

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

回复赞

you_tube 2008-03-14

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

回复赞

dawugui 2008-03-14

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

回复赞

青锋-SS 2008-03-14

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

回复赞

liangCK 2008-03-14

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

回复赞

newcjh2008 2008-03-14

实际情况要再复杂些

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

回复赞

newcjh2008 2008-03-14

[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是一对多的关系)

回复赞

liangCK 2008-03-14

靠..又是老乌龟.

回复赞

liangCK 2008-03-14

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

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

回复赞

dawugui 2008-03-14

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

回复赞

newcjh2008 2008-03-14

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

回复赞

加载更多回复

一年级数学找规律1.ppt 一年级数学找规律1.ppt