表 A 字段A 值:一等奖,二等奖,三等奖
查询SQL Select XX from 表B Where 字段B in (Select 字段A from 表A)SQL是不是不能这样对比 刚才在网上查询了说能直接以顿号相隔的字符与字段相对比 可是执行不出来SQLselect
查询SQL Select XX from 表B Where 字段B in (Select 字段A from 表A)SQL是不是不能这样对比 刚才在网上查询了说能直接以顿号相隔的字符与字段相对比 可是执行不出来SQLselect
Select b.XX
from 表B b inner join 表A a on charindex(字段B,字段A)>1
查询不出来结果 我的SQL是不是有问题呢
drop table test
go
create table test
(
id int,
name varchar(10),
[key] varchar(20)
)
go
insert test
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go
select
id,
a.name,
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<len([key])
and type='p'
and substring(','+[key],number,1)=','
/*
id name key
-----------------------------
1 lisa li
1 lisa is
1 lisa sa
2 sophia ab
2 sophia cd
2 sophia ef
3 lori 12
3 lori 34
3 lori 23
*/
(
A nvarchar(20)
)
create table B
(
B nvarchar(20),
NA nvarchar(20)
)
insert into A values('一等奖')
insert into A values('二等奖')
insert into A values('三等奖')
goinsert into B values('一等奖','张三')
insert into B values('三等奖','李四')
insert into B values('四等奖','王五')
go
Select NA from B Where B in (Select A from A)