cyDoc表有如下字段:
cyID,UserID,Title,Conf,SendDate
cyDocBack有如下字段:
BackID,cyID,UserID,Content,DTime
UserList表有如下字段:
UserID,UserName
其中cyID,BackID,UserID是自增量字段,cyDoc表和cyDocBack表是一对多的关系。
现在要建立一个联合查询,包口以上三个表中的所有字段,但是由于cyDoc表和cyDocBack是一对多的关系,请问如何才能得到没有重复记录的查询结果!谢谢!
cyID,UserID,Title,Conf,SendDate
cyDocBack有如下字段:
BackID,cyID,UserID,Content,DTime
UserList表有如下字段:
UserID,UserName
其中cyID,BackID,UserID是自增量字段,cyDoc表和cyDocBack表是一对多的关系。
现在要建立一个联合查询,包口以上三个表中的所有字段,但是由于cyDoc表和cyDocBack是一对多的关系,请问如何才能得到没有重复记录的查询结果!谢谢!
cyID UserID Title Conf SendDate
21 36 asdfadsf 2004-2-12cyDocBack中的记录
BackID cyID UserID Content DTime
75 21 26
76 21 27
27 21 28UserList中的记录
UserID UserName
26 aa
27 bb
28 cc
36 dd
怎么写一个查询语句,包含以上三个表中的所有字段,但是又不能有重复的记录呢?谢谢!
SELECT dbo.cyDoc.cyID, dbo.cyDoc.UserID AS sendID, dbo.cyDoc.Title,
dbo.UserList.UserName, dbo.cyDocBack.DTime, dbo.cyDoc.UserID AS inceptID,
dbo.cyDocBack.BackID, dbo.cyDoc.Conf
FROM dbo.cyDoc LEFT OUTER JOIN
dbo.UserList ON dbo.cyDoc.UserID = dbo.UserList.UserID LEFT OUTER JOIN
dbo.cyDocBack ON dbo.cyDoc.cyID = dbo.cyDocBack.cyID
但是出来的结果记录有重复,我想要对应cyID的纪录唯一!
insert aaa select 21 ,36,'asdfadsf' ,0, 2004-2-12cyDocBack中的记录
create table bbb (BackID int, cyID int, UserID int, Content int, DTime datetime)
insert bbb select 75 , 21, 26,0,2004-2-12
union allselect 76, 21 , 27,0,2004-2-12
union all
select
27 , 21 , 28,0,2004-2-12UserList中的记录
create table ccc ( UserID int, UserName char(10))
insert ccc select 26, 'aa'
union
select 27 , 'bb'
union
select 28 , 'cc'
union
select 36 , 'dd'语句
SELECT dbo.aaa.UserID, dbo.aaa.Title, dbo.aaa.Conf, dbo.aaa.SendDate, dbo.bbb.BackID,
dbo.bbb.Content, dbo.bbb.DTime, dbo.ccc.UserName, dbo.bbb.UserID AS Expr1
FROM dbo.aaa INNER JOIN
dbo.bbb ON dbo.aaa.cyID = dbo.bbb.cyID INNER JOIN
dbo.ccc ON dbo.bbb.UserID = dbo.ccc.UserID
dbo.bbb.Content, dbo.bbb.DTime, dbo.ccc.UserName, dbo.aaa.cyID
FROM dbo.aaa INNER JOIN
dbo.bbb ON dbo.aaa.cyID = dbo.bbb.cyID INNER JOIN
dbo.ccc ON dbo.bbb.UserID = dbo.ccc.UserID
userid cyid
36 asdfadsf 0 1905-06-14 75 0 1905-06-14 aa 21
36 asdfadsf 0 1905-06-14 76 0 1905-06-14 bb 21
36 asdfadsf 0 1905-06-14 27 0 1905-06-14 cc 21
c.UserName, b.DTime,
b.BackID, a.Conf
FROM dbo.cyDoc a ,dbo.cyDocBack b ,dbo.UserList c WHERE a.UserID = c.UserID AND a.cyID = b.cyID--
SELECT dbo.cyDoc.cyID, dbo.cyDoc.UserID AS sendID, dbo.cyDoc.Title,
dbo.UserList.UserName, dbo.cyDocBack.DTime, dbo.cyDoc.UserID AS inceptID,
dbo.cyDocBack.BackID, dbo.cyDoc.Conf
FROM dbo.cyDoc LEFT OUTER JOIN
dbo.UserList ON dbo.cyDoc.UserID = dbo.UserList.UserID LEFT OUTER JOIN
dbo.cyDocBack ON dbo.cyDoc.cyID = dbo.cyDocBack.cyID
and dbo.cyDoc.UserID = dbo.cyDocBack.UserID
SELECT a.cyID, a.UserID sendID, a.Title,
c.UserName, b.DTime,
b.BackID, a.Conf
FROM dbo.cyDoc a ,dbo.cyDocBack b ,dbo.UserList c
WHERE a.UserID = c.UserID AND a.cyID = b.cyI --在此条件下的记录是不会有重的!
FROM dbo.cyDoc a ,dbo.cyDocBack b ,dbo.UserList c 是要求 a.cyID = b.UserID and b.UserID = c.UserID
还是 a.cyID = c.UserID and a.UserID = b.UserID
--(请问b表和c表的关系不用表现吗?)