有表A和表B
A中有一个字段bid值,是B表中id的集合,以逗号分隔 类似于(1,2,3,4,)
我要查询A中一条的记录的对应B的值sql如下
select * from B where id in (select bid from A where id = 5)但是select bid from A where id = 5这句返回的结果集可能会是很多的,有用in可能会影响效率。而用exists又无法解决问题,所以我想split,但是我查了一下,网上写的都是自定义的split函数。
有没有什么好办法可以不自定义函数,又可以实现功能?
A中有一个字段bid值,是B表中id的集合,以逗号分隔 类似于(1,2,3,4,)
我要查询A中一条的记录的对应B的值sql如下
select * from B where id in (select bid from A where id = 5)但是select bid from A where id = 5这句返回的结果集可能会是很多的,有用in可能会影响效率。而用exists又无法解决问题,所以我想split,但是我查了一下,网上写的都是自定义的split函数。
有没有什么好办法可以不自定义函数,又可以实现功能?
EXISTS (select 1 from A where CHARINDEX(','+LTIRM(ID)+',',','+BID+',') >0)
from A,B
where CHARINDEX(','+LTIRM(ID)+',',','+BID+',')>0
EXISTS (select 1 from A where CHARINDEX(','+LTRIM(ID)+',',','+BID+',') >0)
from A,B
where CHARINDEX(','+LTIRM(ID)+',',','+BID+',')>0 and a.id=5
from A,B
where CHARINDEX(','+LTIRM(ID)+',',','+BID+',')>0
select * from b left join on charindex(rtrim(b.id)+',', a.bid)>0 where a.id=5
--or
select * from b left join on charindex(rtrim(b.id)+',', a.bid)>0 where a.id is not null
--少了left join a
select * from b left join a on charindex(rtrim(b.id)+',', a.bid)>0 where a.id=5
--or
select * from b left join a on charindex(rtrim(b.id)+',', a.bid)>0 where a.id is not null
你是什么数据库,ssms用
charindex或者patindex 都可以
感谢楼上的2个兄弟,不过貌似不行啊。,。。
[/Quote]呵呵 我看行
--建表
if object_id('ta') is not null drop table ta
create table ta
(
[aid]int identity(1,1)
,abid varchar(50)
)
goif object_id('tb') is not null drop table tb
create table tb
(
bid int identity(1,1)
,bmame varchar(20)
)
go--插入
insert into ta
select '1,4,5,' union all
select '5,6,' union all
select '4,9,'
go
insert into tb
select 'aaaa' union all
select 'hjyu' union all
select 'yyyy'union all
select 'eeeee' union all
select 'wwwww'union all
select 'jhhhhhh' union all
select 'iiihgu'union all
select 'uuuuu' union all
select 'tttt'
go--查询
SELECT DISTINCT TB.* FROM TA,TB
WHERE CHARINDEX(LTRIM(BID)+',',','+ABID+',')>0 AND AID=2/*结果
bid bname
--------------------------
5 wwwww
6 jhhhhhh*/
[
SELECT DISTINCT TB.* FROM TA,TB
WHERE CHARINDEX(LTRIM(BID)+',',ABID+',')>0 AND AID=2
多了‘,’现在删掉