主题词1:ztc1
主题词2:ztc2
主题词3:ztc3数据库:id name zhutici1 zhutici2 zhutici31:用第一个主题词和数据库里的:zhutici1,zhutici2,zhutici3 字段去like,如果任何一个字段含有:ztc1就select 出来.然后再并上,第二,三个主题词像上面的like.查出来之后去掉重复数据.(根据name去掉,也就是说如果name有重复的就去掉)相当于:select id,distinct(name) from table where ...(当然这样不对.)
非常感谢..
主题词2:ztc2
主题词3:ztc3数据库:id name zhutici1 zhutici2 zhutici31:用第一个主题词和数据库里的:zhutici1,zhutici2,zhutici3 字段去like,如果任何一个字段含有:ztc1就select 出来.然后再并上,第二,三个主题词像上面的like.查出来之后去掉重复数据.(根据name去掉,也就是说如果name有重复的就去掉)相当于:select id,distinct(name) from table where ...(当然这样不对.)
非常感谢..
where charindex('ztc1',[zhutici1])>0 or charindex('ztc1',[zhutici2])>0 or charindex('ztc1',[zhutici3])>0
union
select name from ObjectTable
where charindex('ztc2',[zhutici1])>0 or charindex('ztc2',[zhutici2])>0 or charindex('ztc2',[zhutici3])>0
union
select name from ObjectTable
where charindex('ztc2',[zhutici1])>0 or charindex('ztc2',[zhutici2])>0 or charindex('ztc2',[zhutici3])>0
select * from tbl
where zhutici1 like'[ztc1,ztc2,ztc3]' or zhutici2 like'[ztc1,ztc2,ztc3]' or
zhutici3 like'[ztc1,ztc2,ztc3]'
上面我写的就是说
在zhutici1 zhutici2 zhutici3 中任何一列包含ztc1或ztc2或ztc3全部找出来
这样的结果对吗
select name from ObjectTable(表名) --ObjectTable(id,name,zhutici1,zhutici2,zhutici3) 注释
where zhutici1 like'[ztc1,ztc2,ztc3]' or zhutici2 like'[ztc1,ztc2,ztc3]' or
zhutici3 like'[ztc1,ztc2,ztc3]'
group by name
select id,distinct(name) 既然distinct(name)就不能在取出 id了。
这里个表要要有一个自增长的主键。
我的表是这样的
表名 :lsh2 name zhutici1 zhutici2 zhutici3 id
l ztc1 ztc2 ztc3 1
l 2222 ztc1 NULL 2
n 3333 NULL ztc3 3
k 2 2 2 4
m ztc2 5 5 5
m 2 2 2 6
m 4 ztc3 NULL 7
select id,name from dbo.lsh2 a where id in(
select top 1 b.id from dbo.lsh2 b where a.name = b.name and b.name in (
select distinct(name) from dbo.lsh2
where zhutici1 like'%ztc1%'
or zhutici1 like '%ztc2%'
or zhutici1 like '%ztc3%'
or zhutici2 like'%ztc1%'
or zhutici2 like '%ztc2%'
or zhutici2 like '%ztc3%'
or zhutici3 like'%ztc1%'
or zhutici3 like '%ztc2%'
or zhutici3 like '%ztc3%' ))
结果:
id name
1 l
3 n
5 m 如果楼主的表id不是主键,就请自己加一个主键。
这样符合要去吗?
--UNION可以吧,记忆中,不信试一下select name from ObjectTable --ObjectTable(id,name,zhutici1,zhutici2,zhutici3)
where charindex( 'ztc1 ',[zhutici1]) >0 or charindex( 'ztc1 ',[zhutici2]) >0 or charindex( 'ztc1 ',[zhutici3]) >0
union
select name from ObjectTable
where charindex( 'ztc2 ',[zhutici1]) >0 or charindex( 'ztc2 ',[zhutici2]) >0 or charindex( 'ztc2 ',[zhutici3]) >0
union
select name from ObjectTable
where charindex( 'ztc3 ',[zhutici1]) >0 or charindex( 'ztc3 ',[zhutici2]) >0 or charindex( 'ztc3 ',[zhutici3]) >0
select ID,Name,Zhutici1,zhutici2,zhutici3 from table1 where
Zhutici1 like '%ztc1%' or zhutici2 like '%ztc1%' or zhutici3 like '%ztc1%'
or
Zhutici1 like '%ztc2%' or zhutici2 like '%ztc2%' or zhutici3 like '%ztc2%'
or
Zhutici1 like '%ztc3%' or zhutici2 like '%ztc3%' or zhutici3 like '%ztc3%' ) as T group by Name
select * from A where id in (select id from
(select name,min(id) id from a
where zhutici1 like '%ztc1%' or zhutici2 like '%ztc1%' or zhutici3 like '%ztc1%'
or zhutici1 like '%ztc2%' or zhutici2 like '%ztc2%' or zhutici3 like '%ztc2%'
or zhutici1 like '%ztc3%' or zhutici2 like '%ztc3%' or zhutici3 like '%ztc3%'
group by name) b)
(
select * from tb where zhutici1 like '%ztc1%' or zhutici2 like '%ztc1%' or zhutici3 like '%ztc1%'
union
select * from tb where zhutici1 like '%ztc2%' or zhutici2 like '%ztc2%' or zhutici3 like '%ztc2%'
union
select * from tb where zhutici1 like '%ztc3%' or zhutici2 like '%ztc3%' or zhutici3 like '%ztc3%'
) a,
(
select name , min(id) id from -- 此处亦可用max
(
select * from tb where zhutici1 like '%ztc1%' or zhutici2 like '%ztc1%' or zhutici3 like '%ztc1%'
union
select * from tb where zhutici1 like '%ztc2%' or zhutici2 like '%ztc2%' or zhutici3 like '%ztc2%'
union
select * from tb where zhutici1 like '%ztc3%' or zhutici2 like '%ztc3%' or zhutici3 like '%ztc3%'
) b
group by name
) c
where a.name = c.name and a.id = c.id
select a.* from
(
select * from tb where charindex('ztc1',zhutici1) > 0 or charindex('ztc1',zhutici2) > 0 or charindex('ztc1',zhutici3)
union
select * from tb where charindex('ztc2',zhutici1) > 0 or charindex('ztc2',zhutici2) > 0 or charindex('ztc2',zhutici3)
union
select * from tb where charindex('ztc3',zhutici1) > 0 or charindex('ztc3',zhutici2) > 0 or charindex('ztc3',zhutici3)
) a,
(
select name , min(id) id from -- 此处亦可用max
(
select * from tb where charindex('ztc1',zhutici1) > 0 or charindex('ztc1',zhutici2) > 0 or charindex('ztc1',zhutici3)
union
select * from tb where charindex('ztc2',zhutici1) > 0 or charindex('ztc2',zhutici2) > 0 or charindex('ztc2',zhutici3)
union
select * from tb where charindex('ztc3',zhutici1) > 0 or charindex('ztc3',zhutici2) > 0 or charindex('ztc3',zhutici3)
) b
group by name
) c
where a.name = c.name and a.id = c.id
不过如果要显示所有FIELD,这不就行了select a.* from
tb a,
(
select name , min(id) id from -- 此处亦可用max
(
select [name],id from tb where charindex('ztc1',zhutici1) > 0 or charindex('ztc1',zhutici2) > 0 or charindex('ztc1',zhutici3)>0
union
select [name],id from tb where charindex('ztc2',zhutici1) > 0 or charindex('ztc2',zhutici2) > 0 or charindex('ztc2',zhutici3)>0
union
select [name],id from tb where charindex('ztc3',zhutici1) > 0 or charindex('ztc3',zhutici2) > 0 or charindex('ztc3',zhutici3)>0
) b
group by name
) c
where a.id=c.id and a.name=c.name
(
select id,name,zhutici1 from tb
union all
select id,name,zhutici2 from tb
union all
select id,name,zhutici3 from tb
) t
where zhutici1 like '%ztc1%' or zhutici1 like '%ztc2%' or zhutici1 like '%ztc3%'
group by id,name
(
select id,name,zhutici1 from tb
union all
select id,name,zhutici2 from tb
union all
select id,name,zhutici3 from tb
) t
where zhutici1 like '%ztc1% ' or zhutici1 like '%ztc2% ' or zhutici1 like '%ztc3% '
group by name 或者................select min(id),name from
(
select id,name,zhutici1 from tb
union all
select id,name,zhutici2 from tb
union all
select id,name,zhutici3 from tb
) t
where zhutici1 like '%ztc1% ' or zhutici1 like '%ztc2% ' or zhutici1 like '%ztc3% '
group by name 不知道楼主是不是这意思 - .-