查询多个Like条件

[Cwst]

两张表Order和SearchCond
Order有一列City用来存储城市如北京
SearchCond有一列SCond字符型

现在Order表中有板有三笔记录
北京
北京市
京

SearchCond表中有两笔记录
京
北京

SELECT City FROM Order
WHERE City IN(SELECT SCond FROM SearchCond)

我使用上面的查询，只能查到两笔记录，北京市查不出来
上面的查询应该是不对的，可能是这样
SELECT City FROM Order WHERE City LIKE '%京%' OR City LIKE '%北京%'

以第二张表的记录来做为第一张表的Like条件，怎么写．后续还要考虑速度．
哪位高人帮忙一下，谢谢．

[dawugui]

select m.* from [Order] m , SearchCond n where chindex(n.SearchCond , m.City) > 0

[百年树人]

SELECT City 
FROM Order o,SearchCond s
WHERE where charindex(s.SCond,o.City)>0 

[dawugui]

select distinct m.* from [Order] m , SearchCond n where chindex(n.SearchCond , m.City) > 0

select distinct m.* from [Order] m , SearchCond n where m.City like '%' + n.SearchCond + '%'

[dobear_0922]

select City from Order O
where exists(select 1 from SearchCond S where O.City like '%'+S.SCond+'%')

[子陌红尘]

try:

select
    distinct O.City
from
    Order O,SearchCond S
where
    O.City like S.SCond+'%'

[dobear_0922]

--or
select City from Order O
where exists(select 1 from SearchCond S where charindex(S.SCond,O.City)>0

[dawugui]

create table [Order](City varchar(10))
create table SearchCond (SCond varchar(10))
insert into [Order] values('北京') 
insert into [Order] values('北京市') 
insert into [Order] values('京') 
insert into SearchCond values('京') 
insert into SearchCond values('北京')
go

select distinct m.* from [Order] m , SearchCond n where charindex(n.SCond , m.City) > 0

select distinct m.* from [Order] m , SearchCond n where m.City like '%' + n.SCond + '%'

--drop table  [Order] , SearchCond

/*
City       
---------- 
北京
北京市
京

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

[lgxyz]

SELECT DISTINCT City FROM AOrder,SEARCHCOND WHERE City LIKE '%'+SCond+'%'

[子陌红尘]

declare @Order table(City varchar(20))

declare @SearchCond table(SCond varchar(20))


insert into @Order select rtrim('北京  ')
insert into @Order select rtrim('北京市') 
insert into @Order select rtrim('京    ')

insert into @SearchCond select rtrim('京  ')
insert into @SearchCond select rtrim('北京') 



select
    distinct O.City
from
    @Order O,@SearchCond S
where
    O.City like S.SCond+'%'

/*
City                 
-------------------- 
北京
北京市
京
*/

[百年树人]

---测试数据---
if object_id('[Order]') is not null drop table [Order]
go
create table [Order]([City] varchar(6))
insert [Order]
select '北京' union all
select '北京市' union all
select '京'
if object_id('[SearchCond]') is not null drop table [SearchCond]
go
create table [SearchCond]([SCond] varchar(4))
insert [SearchCond]
select '京' union all
select '北京'
 
---查询---
SELECT distinct City 
FROM [Order] o,SearchCond s
WHERE charindex(s.SCond,o.City)>0

---结果---
City   
------ 
北京
北京市
京

（所影响的行数为 3 行）

[dawugui]

[Quote=引用 1 楼 dawugui 的回复:]
select m.* from [Order] m , SearchCond n where chindex(n.SearchCond , m.City) > 0
[/Quote]

[Quote=引用 3 楼 dawugui 的回复:]
SQL codeselect distinct m.* from [Order] m , SearchCond n where chindex(n.SearchCond , m.City) > 0

select distinct m.* from [Order] m , SearchCond n where m.City like '%' + n.SearchCond + '%'
[/Quote]

字段错了, chindex写错了,应该是charindex

7楼更正过来了,不好意思.