面试SQL题 得出表中含有文件的文件夹
表：
test
path isfolder
/music/a.sql F
/music/ T
/pic/ T
/pic/b.sql F
/mnt/ T
if object_id('test') is not null
drop table test
go
create table test
(
path nvarchar(20),
isfolder nvarchar(10)
)
go
insert into test
select '/music/a.sql','F' UNION ALL
select '/music/','T' UNION ALL
select '/pic/','T' UNION ALL
select '/pic/b.sql','F' UNION ALL
select '/mnt/','T'
go
--select len(path) -charindex('/',reverse(path)) from test where isfolder ='F'
--select reverse(right(reverse(path),len(path) -charindex('/',reverse(path)))),* from test
--where isfolder ='F'
go
select
substring(
reverse(right(reverse(path),len(path) -charindex('/',reverse(path)))),
2,
len(path) -charindex('/',reverse(path))
)
from test
where isfolder ='F'
select a.path, a.isfolder from
(select * from test where isfolder = 'T') a
inner join
(select * from test where isfolder = 'F') b
on b.path like a.path + '%'
