不使用子查询,请教一个sql查询。
原帖在这里,希望各位达人移步观看。
http://bbs.csdn.net/topics/390627604?page=1#post-395914924
在select语句中不能有子查询。 sql 数据库 查询 子查询
[解决办法]
改一点:
create table #project(id int,project_name varchar(100))
insert into #project
select 1,'项目1'
union all select 2,'项目2'
create table #item(id int,item_name varchar(100))
insert into #item
select 1,'Item1'
union all select 2,'Item2'
create table #project_item(id int,item_id int,project_id int)
insert into #project_item
select 1,1,1
union all select 2,2,1
union all select 3,2,2
create table #project_item_file(id int,item_id int,[file_name] varchar(50),parent_id int,is_folder int)
insert into #project_item_file
select 1,1,'xxx.doc',0,0
union all select 2,1,'zzz.xls',0,0
union all select 3,2,'fff.txt',4,0
union all select 4,2,'文件夹1',0,1
union all select 5,3,'rrr.txt',0,0
go
--select b.item_name,d.file_name,c.file_name,
select distinct fpath=b.item_name+case when isnull(d.file_name,'')='' then '' else '/'+d.file_name end+'/'+c.file_name
from #project_item a
left join #item b on a.item_id=b.id
left join #project_item_file c on a.item_id=c.item_id
left join #project_item_file d on c.parent_id=d.id
where c.is_folder=0
drop table #project,#item,#project_item,#project_item_file
/*
fpath
------------------
Item1/xxx.doc
Item1/zzz.xls
Item2/文件夹1/fff.txt
*/