表与表外连接,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条记录
回复
加载更多回复
相关推荐
基于java的企业人事管理系统设计--软件工程课程设计(含源码与论文设计).rar 1 引言 4 1.1 课程设计目标 4 1.2 编程工具(编程环境)介绍 4 1.3 实施时间及主要实施步骤 4 2 需求分析 5 3 系统总体设计 6 4 数据库设计 6 5 主要功能模块的设计与实现 10 5.1 功能模块1详细设计(综合查询员工信息)..........................................................10 5.1.1 详细设计.......................................................................................................10 5.1.2 算法流程........................................................................................................15 5.1.3 界面设计及测试结果.....................................................................................15 6 调试分析 15 7 用户手册 16 8 测试结果 17 8.1 员工信息的添加...................................................................................................17 8.2 员工信息的修改...................................................................................................17 8.3 员工信息的删除...................................................................................................18 8.4 员工信息的综合查询...........................................................................................18 8.5 员工信息按性别统计的结果................................................................................19 8.6 员工信息按状态统计的结果...............................................................................19 8.7 员工信息按职称统计的结果................................................................................20 9 结论 20 10 参考文献 20
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-14 03:50
社区公告
暂无公告