多表查询问题 left,rigth join 问题

wxlwxlwxlwxl 2008-03-07 03:04:05
是这样两个表 tfile(fileid,filename,filepath,subjectid),tsubject(subjectid,subjectname)
假定 tfile表数据为
fileid filename filepath subjectid
1 file1 img/file1.jpg 1
2 file2 img/file2.jpg 1
3 file3 img/file3.jpg 1

tsubject表数据为
subjectid subjectname
1 photo
2 life

我要实现这种效果

fileid filename filepath subjectid subjectname
3 file3 img/file3.jpg 1 photo
null null null 2 life

我现在只是实现了
fileid filename filepath subjectid subjectname
3 file3 img/file3.jpg 1 photo

我是这样写的
select fileid,filepath,filename,subjectid,subjectname from tfile a right join tsubject b on a.subjectid =b.subjectid where a.fileid in
( select max(fileid) from tfile c inner join tsubject d on c.subjectid =d.subjectid group by c.subjectid )

这个问题怎么解决啊?

...全文
94 点赞 收藏 13
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wxlwxlwxlwxl 2008-03-07
解决了,多谢各位
我把我那个稍微改了下也没问题了
:)
回复
wxlwxlwxlwxl 2008-03-07
多谢各位,我先试下。
回复
flyidealism 2008-03-07
---建立测试数据
create table tfile(fileid int,filename varchar(100),filepath varchar(50),subjectid int)

insert into tfile
select 1 ,'file1','img/file1.jpg',1
union all
select 2,'file2','img/file2.jpg',1
union all
select 3,'file3','img/file3.jpg',1


create table tsubject(subjectid int,subjectname varchar(50))
insert into tsubject
select 1,'photo'
union all
select 2,'life'

--SQL代码实现
select a.fileid,a.filename,a.filepath,b.subjectid,b.subjectname
from
(select * from tfile
where fileid=3) a right join tsubject b
on a.subjectid=b.subjectid
--结果如你要求
回复
nzperfect 2008-03-07
create table tfile(fileid int,filename varchar(20),filepath varchar(30),subjectid int)
insert into tfile
select 1 ,'file1', 'img/file1.jpg', 1 union all
select 2 ,'file2', 'img/file2.jpg', 1 union all
select 3 ,'file3', 'img/file3.jpg', 1

create table tsubject(subjectid int ,subjectname varchar(20))
insert into tsubject
select 1 ,'photo' union all
select 2 ,'life'


select b.fileid,b.filename,b.filepath,
a.subjectid,a.subjectname
from tsubject as a
left join
(
select fileid,filename,filepath,subjectid from tfile as c where fileid=(select max(fileid) from tfile where subjectid=c.subjectid)
)as b on a.subjectid=b.subjectid

--
3 file3 img/file3.jpg 1 photo
NULL NULL NULL 2 life
回复
pt1314917 2008-03-07
在SQL语句后面加上“我要带颜色显示”即可,如:

select * from 表名 我要带颜色显示
回复
山之魂2 2008-03-07
怎么把SQL 里面的文字颜色带出来啊?
回复
pt1314917 2008-03-07
小枪,你那连接不对吧?应该换右连接。
回复
山之魂2 2008-03-07
select b.fileid,b.filename, b.filepath,a.*
from tfile b
right join tsubject a
on a.subjectid = b.subjectid
回复
-狙击手- 2008-03-07
select b.fileid,
b.filename,
b.filepath,
a.*
from (select *
from tsubject t
where not exists(select 1 from tsubject where subjectid = t.subjectid and fileid> t.fileid)) a
left join tfile b on a.subjectid = b.subjectid
回复
kk19840210 2008-03-07
create table tfile(fileid int,[filename] varchar(10),filepath varchar(20),subjectid int)


create table tsubject(subjectid int ,subjectname varchar(10))

insert into tfile values(1,'file1','img/file1.jpg',1)
insert into tfile values(2,'file2','img/file2.jpg',1)
insert into tfile values(3,'file3','img/file3.jpg',1)

insert into tsubject values(1,'photo')
insert into tsubject values(2,'life')


select fileid,filepath,[filename],c.subjectid,subjectname from tsubject c left join
(select * from tfile a where not exists(select 1 from tfile where fileid>a.fileid))d
on c.subjectid=d.subjectid



fileid filepath filename subjectid subjectname
----------- -------------------- ---------- ----------- -----------
3 img/file3.jpg file3 1 photo
NULL NULL NULL 2 life

(2 行受影响)
回复
-狙击手- 2008-03-07
select b.fileid,
b.filename,
b.filepath,
a.*
from select *
from tsubject t
where not exists(select 1 from tsubject where subjectid = t.subjectid and fileid> t.fileid) a
left join tfile b on a.subjectid = b.subjectid
回复
pt1314917 2008-03-07

select c.fileid,c.filename,c.filepath,b.* from
(select * from tfile a where not exists(select 1 from tfile where subjectid=a.subjectid and fileid>a.fileid))c right join tsubject b
on c.subjectid = b.subjectid

回复
-狙击手- 2008-03-07
我要实现这种效果 

fileid filename filepath subjectid subjectname
3 file3 img/file3.jpg 1 photo
null null null 2 life


---

select b.fileid,
b.filename,
b.filepath,
a.*
from tsubject a
left join tfile b on a.subjectid = b.subjectid
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

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