select * from [file] a
where path like '%delphi%'
or filename like '%delphi%'
and filename=( select min(filename)
from [file]
where (path like '%delphi%'
or filename like '%delphi%')
and path=a.path
)
where path like '%delphi%'
or filename like '%delphi%'
and filename=( select min(filename)
from [file]
where (path like '%delphi%'
or filename like '%delphi%')
and path=a.path
)
select * from [file] a
where (path like '%delphi%'
or filename like '%delphi%')
and filename=( select min(filename)
from [file]
where (path like '%delphi%'
or filename like '%delphi%')
and path=a.path
)
============= =========
borland/delphi6.0 rsda.roo (下一条path重复了)
borland/delphi7.0 delphi.r00 下一条path重复了)
doc/delphi/CD1 cd1.img
doc/delphi/CD2 cd2.img
根本没办法分组
或者用
select path,max(filename) as filename from file where path like '%delphi%' or filename like '%delphi' group by path order by path
来区分
如果文件名含有delphi的话,path可以重复(因为这时候我的目标是取出filename)
如果文件名不含有delphi但是path含有delphi的话
path就不能重复(我的目标是取出path)
比如这两条doc/delphi/CD1 cd1.img
doc/delphi/CD2 cd2.imgpath不一样,filename也不一样
但只需取出一条
因为delphi所在的路径是一样的(doc/delphi,不管cd1和cd2是不是一样的)
from [file]
where charindex('delphi',path)>0
or charindex('delphi',filename)>0
group by path
order by path
where filename like '%delphi%')
union all
select * from [file] a
where path like '%delphi%'
and filename not like '%delphi%'
and filename=( select min(filename)
from [file]
where path like '%delphi%'
and filename not like '%delphi%'
and path=a.path
)
select path,filename from [file]
where filename like '%delphi%')
union all
select path,min(filename) as filename from [file]
where path like '%delphi%'
and filename not like '%delphi%'
group by path
select * from(
select * from [file]
where charindex('delphi',filename)>0
union all
select path,min(filename) from [file]
where charindex('delphi',filename)=0
and charindex('delphi',path)>0
group by path
)a order by path
create table [file](path varchar(20),filename varchar(20))
insert [file] select 'borland/delphi6.0','rsda.r00'
union all select 'borland/delphi6.0','rsda.r01'
union all select 'borland/delphi7.0','delphi.r00'
union all select 'borland/delphi7.0','delphi.r01'
union all select 'doc/delphi/CD1' ,'cd1.img'
union all select 'doc/delphi/CD2' ,'cd2.img'
go--查询
select * from(
select * from [file]
where charindex('delphi',filename)>0
union all
select path,min(filename) from [file]
where charindex('delphi',filename)=0
and charindex('delphi',path)>0
group by path
)a order by pathgo--删除测试
drop table [file]/*--测试结果path filename
-------------------- --------------------
borland/delphi6.0 rsda.r00
borland/delphi7.0 delphi.r00
borland/delphi7.0 delphi.r01
doc/delphi/CD1 cd1.img
doc/delphi/CD2 cd2.img(所影响的行数为 5 行)
--*/
select * from [file]
where charindex('delphi',filename)>0
union all
select a.path,a.filename
from [file] a join(
select path=min(path),filename=min(filename)
from [file]
where charindex('delphi',filename)=0
and charindex('delphi',path)>0
group by left(path,charindex('delphi',path))
)b on a.path=b.path and a.filename=b.filename
and charindex('delphi',a.filename)=0
and charindex('delphi',a.path)>0
)a order by path
create table [file](path varchar(20),filename varchar(20))
insert [file] select 'borland/delphi6.0','rsda.r00'
union all select 'borland/delphi6.0','rsda.r01'
union all select 'borland/delphi7.0','delphi.r00'
union all select 'borland/delphi7.0','delphi.r01'
union all select 'doc/delphi/CD1' ,'cd1.img'
union all select 'doc/delphi/CD2' ,'cd2.img'
go--查询
select * from(
select * from [file]
where charindex('delphi',filename)>0
union all
select a.path,a.filename
from [file] a join(
select path=min(path),filename=min(filename)
from [file]
where charindex('delphi',filename)=0
and charindex('delphi',path)>0
group by left(path,charindex('delphi',path))
)b on a.path=b.path and a.filename=b.filename
and charindex('delphi',a.filename)=0
and charindex('delphi',a.path)>0
)a order by path
go--删除测试
drop table [file]/*--测试结果path filename
-------------------- --------------------
borland/delphi6.0 rsda.r00
borland/delphi7.0 delphi.r00
borland/delphi7.0 delphi.r01
doc/delphi/CD1 cd1.img(所影响的行数为 4 行)--*/
谢谢你们的解答
我运行了一下,有一个功能还没有实现
就是象下面的数据取出来的结果还是2条
doc/delphi/CD1 cd1.img
doc/delphi/CD2 cd2.img
不知道有没有办法过滤?
我测试一下:)
信息是“包括union运算符的查询表达式中的所有查询都必须在选择列表中包含同样数目的表达式”
不知道是什么意思
请zjcxc(邹建)兄指教:)