假如表GG:
rid aa a
2 sdfsdf 02,03,010204
表A:
a b
sdf 02
dsfs 03
sdfs 02
表F:
uid rid
1 2
1 3
select * from A where b in (select a from GG where rid in(select rid from F where uid=1))为什么取不到数据呢,而select a from GG where rid in(select rid from F where uid=1)是有数据的,难道类型为varchar的不能用in?????我把GG表的a字段改成:'02','03','010204'还是不行啊???
这样:select * from A where b in ('02','03','010204')
或这样:
select * from A where b in (02,03,010204)
为什么可以呢????最后测试了一下,我把A表的b改成
表A:
a b
sdf 2
dsfs 3
sdfs 2
而GG表不变,用这样:
select * from A where b in (02,03,010204)是查得出的,而用这样:
select * from A where b in ('02','03','010204')就查不出了,看来用varchar型得注意保存成'02','03','010204'这样的,好与表A的b字段类型匹配但为什么:把GG表的a字段改成:'02','03','010204'
A表是
a b
sdf 02
dsfs 03
sdfs 02写成:
select * from A where b in (select a from GG where rid in(select rid from F where uid=1))
就取不到??
而
select * from A where b in ('02','03','010204')这样取得到呢??我应该怎么做呢??请sql高手帮忙
rid aa a
2 sdfsdf 02,03,010204
表A:
a b
sdf 02
dsfs 03
sdfs 02
表F:
uid rid
1 2
1 3
select * from A where b in (select a from GG where rid in(select rid from F where uid=1))为什么取不到数据呢,而select a from GG where rid in(select rid from F where uid=1)是有数据的,难道类型为varchar的不能用in?????我把GG表的a字段改成:'02','03','010204'还是不行啊???
这样:select * from A where b in ('02','03','010204')
或这样:
select * from A where b in (02,03,010204)
为什么可以呢????最后测试了一下,我把A表的b改成
表A:
a b
sdf 2
dsfs 3
sdfs 2
而GG表不变,用这样:
select * from A where b in (02,03,010204)是查得出的,而用这样:
select * from A where b in ('02','03','010204')就查不出了,看来用varchar型得注意保存成'02','03','010204'这样的,好与表A的b字段类型匹配但为什么:把GG表的a字段改成:'02','03','010204'
A表是
a b
sdf 02
dsfs 03
sdfs 02写成:
select * from A where b in (select a from GG where rid in(select rid from F where uid=1))
就取不到??
而
select * from A where b in ('02','03','010204')这样取得到呢??我应该怎么做呢??请sql高手帮忙
你这样出来的结果是select * from A where b in ('02,03,010204')
肯定不行了
select * from A where b in (replace((select a from GG where rid in(select rid from F where uid=1)),',','''',''''))
换成这样试试
Inner Join GG On CharIndex(',' + A.b + ',', ',' + GG.a + ',') > 0
Inner Join F On GG.rid = F.rid
Where F.uid=1
或者用Like
Select A.* From A
Inner Join GG On ',' + GG.a + ',' Like '%,' + A.b + ',%'
Inner Join F On GG.rid = F.rid
Where F.uid=1
[dbo].[f_Getstr]()
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @S VARCHAR(4000)
SET @S=''
SELECT @S=@S+','+a from GG where rid in(select rid from F where uid=1)
RETURN STUFF(@S,1,1,'')
END
调用时候:select * from A where b in dbo.f_Getstr()
给select语句得到的临时数据集都加上别名
------------
這裡出問題,和你說的這個應該沒有多大關係。
这样应该行的,你的GG表的a字段是‘02,03,010204’这个值在一个字段里的,所以in不行;
假如表GG:
rid aa a
2 sdfsdf 02,03,010204改成
rid aa a
2 sdfsdf 02
2 sdf 03
2 www 010204这样就可以用in 了!
select * from A where b in replace((select a from GG where rid in(select rid from F where uid=1))
你这样出来的结果是select * from A where b in ('02,03,010204')
肯定不行了
select * from A where b in (replace((select a from GG where rid in(select rid from F where uid=1)),',','''',''''))
换成这样试试 ===========================
我把GG表的a已经改成'02','03','010204'这样来保存了啊在查询分析器里直接写成:
select * from A where b in ('02','03','010204')就有数据
而写成:
select * from A where b in replace((select a from GG where rid in(select rid from F where uid=1))
就没有呢??
select * from A where b in (select '02,03,010204' from dual);
这样应该行的,你的GG表的a字段是‘02,03,010204’这个值在一个字段里的,所以in不行;
假如表GG:
rid aa a
2 sdfsdf 02,03,010204改成
rid aa a
2 sdfsdf 02
2 sdf 03
2 www 010204这样就可以用in 了!
=======================
本来是不想用一个字段来保存的,最后想图方便所以就用了,没想到用in有问题,郁闷
job_2006(初学.net), 你這樣的表結構,這樣用in不行的,改用charindex或者like吧。
------------
嗯,谢谢!~
你查询出来的a,比如02,03,010204,是一个整体(字符串),所以rid匹配不出来