再问SQL条件查询问题

[sundy26]

问题：
我在一个数据库表中保存某个目录下所有的文件和目录信息，比如目录名为Test ,下面还有子目录 subtest1,subtest2;Test 每个目录下都有一些文件，现在想知道如何通过SQL查询到某个目录下的子目录名称和当前目录所有文件？

有数据库表FileList

id filepath type
1 D:\test\ D
2 D:\test\a.txt f
2 D:\test\b.txt f
2 D:\test\c.txt f
2 D:\test\subtest1 D
2 D:\test\subtest2 D
2 D:\test\subtest1\dfe.txt f
2 D:\test\subtest2\fewf.txt f



期望输入 D:\test 时能得到结果为：

id filepath type
1 D:\test\ D
2 D:\test\a.txt f
2 D:\test\b.txt f
2 D:\test\c.txt f


期望输入 D:\test\subtest1\ 时能得到结果为：

id filepath type
2 D:\test\subtest1 D
2 D:\test\subtest1\dfe.txt f


请问sQL语句如何写？

[octwind]

select * from tb where filepath like 'D:\test\_%' and (len(filepath)-len(replace(filepath,'\','')))=2

[sundy26]

不对

[octwind]

if object_id('tb') is not null drop table tb
create table tb(id int,filepath varchar(30),type varchar(10))
insert into  tb
select 1,'D:\test\','D' union all
select 2,'D:\test\a.txt','f' union all
select 2,'D:\test\b.txt','f'  union all
select 2,'D:\test\c.txt','f' union all
select 2,'D:\test\subtest1','D' union all
select 2,'D:\test\subtest2','D' union all
select 2,'D:\test\subtest1\dfe.txt','f' union all
select 2,'D:\test\subtest2\fewf.txt','f' 

go
select * from tb where filepath like 'D:\test\_%' and (len(filepath)-len(replace(filepath,'\','')))=2 order by type

（所影响的行数为 8 行）

id          filepath                       type       
----------- ------------------------------ ---------- 
2           D:\test\subtest1               D
2           D:\test\subtest2               D
2           D:\test\a.txt                  f
2           D:\test\b.txt                  f
2           D:\test\c.txt                  f

（所影响的行数为 5 行）

[Liyingyue_FFS]

select * from table where filepath like 'D:\test\%'
select * from table where filepath like 'D:\test\subtest1\%'

应该是这个样子的吧

[Liyingyue_FFS]

[Quote=引用 1 楼 Liyingyue_FFS 的回复:]
select * from table where filepath like 'D:\test\%'
select * from table where filepath like 'D:\test\subtest1\%'

应该是这个样子的吧
[/Quote]

弄错了，实现不了

[areswang]

老大写过这样的一个函数

[rhq12345]

declare @filepath varchar(999)
set @filepath='D:\test\ '--待查询的目录
select * from FileList where filepath like @filepath+'%'

[Liyingyue_FFS]

[Quote=引用 4 楼 rhq12345 的回复:]
SQL code
declare @filepath varchar(999)
set @filepath='D:\test\ '--待查询的目录
select * from FileList where filepath like @filepath+'%'
[/Quote]

也不对哦，和我的意思一样

declare @filepath varchar(999)
set @filepath='D:\test\ '--待查询的目录
select * from FileList where filepath=@filepath  --查目录
union
select * from FileList where filepath like @filepath+'%' and type = 'f' --查目录下的文件

查目录的时候注意一下对 路径最后'\'的判断，最好是查两遍，一遍带'\'的，一遍不带'\'的

[utpcb]

哈我还没想到怎末办想一下！
想一下 =一下呵呵

[octwind]

但和结果有些出入

if object_id('tb') is not null drop table tb
create table tb(id int,filepath varchar(100),type varchar(100))
insert into  tb
select 1,'D:\test\','D' union all
select 2,'D:\test\a.txt','f' union all
select 2,'D:\test\b.txt','f'  union all
select 2,'D:\test\c.txt','f' union all
select 2,'D:\test\subtest1','D' union all
select 2,'D:\test\subtest2','D' union all
select 2,'D:\test\subtest1\dfe.txt','f' union all
select 2,'D:\test\subtest2\fewf.txt','f' 

go
select * from tb where filepath like 'D:\test\%' and (len(filepath)-len(replace(filepath,'\','')))=2

（所影响的行数为 8 行）

id          filepath                                                                                             type                                                                                                 
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
1           D:\test\                                                                                             D
2           D:\test\a.txt                                                                                        f
2           D:\test\b.txt                                                                                        f
2           D:\test\c.txt                                                                                        f
2           D:\test\subtest1                                                                                     D
2           D:\test\subtest2                                                                                     D

（所影响的行数为 6 行）

[lystey]

帮你顶

[lgxyz]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
CREATE TABLE TB(ID INT,FILEPATH VARCHAR(50),TYPE VARCHAR(2))
INSERT INTO TB SELECT 1,'D:\test\','D' 
UNION ALL SELECT 2,'D:\test\a.txt','f' 
UNION ALL SELECT 2,'D:\test\b.txt','f'
UNION ALL SELECT 2,'D:\test\c.txt','f'
UNION ALL SELECT 2,'D:\test\subtest1','D' 
UNION ALL SELECT 2,'D:\test\subtest2','D' 
UNION ALL SELECT 2,'D:\test\subtest1\dfe.txt','f' 
UNION ALL SELECT 2,'D:\test\subtest2\fewf.txt','f' 

DECLARE @A VARCHAR(50)
SET @A='D:\test\'
SELECT * FROM TB WHERE FILEPATH = @A AND TYPE='D'
UNION ALL 
SELECT * FROM TB WHERE FILEPATH LIKE @A+'%' AND (LEN(REPLACE(FILEPATH,'\',''))=LEN(FILEPATH)-2) AND TYPE='F'

/*
ID          FILEPATH                                           TYPE 
----------- -------------------------------------------------- ---- 
1           D:\test\                                           D
2           D:\test\a.txt                                      f
2           D:\test\b.txt                                      f
2           D:\test\c.txt                                      f

（所影响的行数为 4 行）
*/

[Garnett_KG]

declare @t table (id int,filepath varchar(100),type varchar(2))
insert into @t 
select 
1,      'D:\test\',              'D'
union all select 
2,      'D:\test\a.txt',          'f'
union all select 
2,      'D:\test\b.txt',          'f'
union all select 
2,      'D:\test\c.txt',          'f'
union all select 
2,      'D:\test\subtest1',      'D'
union all select 
2,      'D:\test\subtest2',      'D'
union all select 
2,      'D:\test\subtest1\dfe.txt' ,     'f'
union all select 
2,      'D:\test\subtest2\fewf.txt',      'f'
declare @s varchar(100)

set @s='D:\test\subtest2'
select * 
from @t  
where (type='D' AND (filepath =@s OR filepath=@s+'\'))
	or 
	(
	type='f'
	and 
	charindex('\',replace(filepath,@s+'\',''))=0
	)

[utpcb]

对了 这个才是对的


IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
CREATE TABLE TB(ID INT,FILEPATH VARCHAR(50),TYPE VARCHAR(2))
INSERT INTO TB SELECT 1,'D:\test\','D'
UNION ALL SELECT 2,'D:\test\a.txt','f'
UNION ALL SELECT 2,'D:\test\b.txt','f'
UNION ALL SELECT 2,'D:\test\c.txt','f'
UNION ALL SELECT 2,'D:\test\subtest1','D'
UNION ALL SELECT 2,'D:\test\subtest2','D'
UNION ALL SELECT 2,'D:\test\subtest1\dfe.txt','f'
UNION ALL SELECT 2,'D:\test\subtest2\fewf.txt','f'

DECLARE @A VARCHAR(50)
SET @A='D:\test\'
SELECT * FROM TB WHERE FILEPATH = @A AND TYPE='D'
UNION ALL
SELECT * FROM TB WHERE FILEPATH LIKE @A+'%' AND (LEN(REPLACE(FILEPATH,'\',''))=LEN(FILEPATH)-2) AND TYPE='F'

/*
ID FILEPATH TYPE
----------- -------------------------------------------------- ----
1 D:\test\ D
2 D:\test\a.txt f
2 D:\test\b.txt f
2 D:\test\c.txt f

（所影响的行数为 4 行）
*/

[chuifengde]

DECLARE @a table(id int,      filepath       varchar(100),       type  varchar(20))
INSERT @a SELECT 1      ,'D:\test\'              ,'D' 
UNION ALL SELECT 2      ,'D:\test\a.txt'          ,'f' 
UNION ALL SELECT 2      ,'D:\test\b.txt'          ,'f' 
UNION ALL SELECT 2      ,'D:\test\c.txt'          ,'f' 
UNION ALL SELECT 2      ,'D:\test\subtest1'      ,'D' 
UNION ALL SELECT 2      ,'D:\test\subtest2'      ,'D' 
UNION ALL SELECT 2      ,'D:\test\subtest1\dfe.txt'      ,'f' 
UNION ALL SELECT 2      ,'D:\test\subtest2\fewf.txt'      ,'f' 

DECLARE @t varchar(100)
SET @t='D:\test'
SET @t=CASE WHEN RIGHT(@t,1)='\' THEN @t ELSE @t+'\' END 
SELECT * FROM @a 
WHERE 
charindex('\',replace(
	CASE WHEN RIGHT(filepath,1)='\' THEN filepath ELSE 
		CASE WHEN charindex('.',filepath)>0 THEN filepath
		ELSE filepath+'\' END END ,@t,''))=0
--result
/*id          filepath                       type                 
----------- ------------------------------ -------------------- 
          1 D:\test\                       D
          2 D:\test\a.txt                  f
          2 D:\test\b.txt                  f
          2 D:\test\c.txt                  f

（所影响的行数为 4 行）
*/

[LIHY70]

select * from tb where (type='D' and filepath=@filepath) or (type='f' and len(filepath)-len(replace(filepath,'\',''))=len(@filepath)-len(replace(@filepath,'\','')))

[sundy26]

谢谢大家，不过不好意思，一着急写错了：

我想要的是：

期望输入 D:\test 时能得到结果应该为：

id filepath type
1 D:\test\subtest1 D
2 D:\test\subtest2 D
3 D:\test\a.txt f
2 D:\test\b.txt f
2 D:\test\c.txt f

期望输入 D:\test\subtest1\ 时能得到结果为：

id filepath type
2 D:\test\subtest1\dfe.txt f

实际上就是想输入一个目录名，把当前目录下的文件和所有目录列出来。