SELECT * FROM @A WHERE charindex(ltrim(ID_A) ,(SELECT ID_AAA FROM @B WHERE ID_B=1))>0
SELECT * FROM @A,@B WHERE CHARINDEX(','+LTRIM(ID_A)+',', ','+ID_AAA+',')>0
因为你SELECT ID_AAA FROM @B WHERE ID_B=1 查询出来的是一个字符串。出错首先可以定义一个临时表把 SELECT ID_AAA FROM @B WHERE ID_B=1 的结果存储起来即可 DECLARE @A TABLE(ID_A NUMERIC(9,0),ID_C VARCHAR(10)) DECLARE @B TABLE(ID_B NUMERIC(9,0),ID_AAA VARCHAR(100)) Declare @temp table(F1 varchar(100)) Declare @SourceSql varchar(100) INSERT INTO @A SELECT 1,'C1' UNION ALL SELECT 2,'C2' UNION ALL SELECT 3,'C2' UNION ALL SELECT 4,'C2' UNION ALL SELECT 5,'C2' UNION ALL SELECT 6,'C2' select * from @A INSERT INTO @B SELECT 1,'1,2' UNION ALL SELECT 2,'2,3,4' UNION ALL SELECT 3,'2,4,6' set @SourceSql=(SELECT ID_AAA FROM @B WHERE ID_B=1)declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(',',@SourceSql) while @i>=1 begin if len(left(@SourceSql,@i-1))>0 begin insert @temp values(left(@SourceSql,@i-1)) end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(',',@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) select * from @temp SELECT * FROM @A WHERE ID_A IN (SELECT F1 FROM @temp)
哪裡報錯就修改哪裡呀 試試. 樓上幾位的效率比這個高. SELECT * FROM @A WHERE ID_A IN (SELECT cast(ID_AAA as NUMERIC(9,0)) FROM @B WHERE ID_B=1)
--这样呢。 SELECT * FROM @A WHERE charindex(','+ltrim(ID_A)+',' ,','+ltrim((SELECT ID_AAA FROM @B WHERE ID_B=1))+',')>0
--在试试这个SELECT * FROM @A,@B WHERE charindex(','+rtrim(ID_A)+',', ','+ID_AAA+',')>0 and ID_B=1
WHERE charindex(ltrim(ID_A) ,(SELECT ID_AAA FROM @B WHERE ID_B=1))>0
SELECT * FROM @A,@B
WHERE CHARINDEX(','+LTRIM(ID_A)+',', ','+ID_AAA+',')>0
DECLARE @A TABLE(ID_A NUMERIC(9,0),ID_C VARCHAR(10))
DECLARE @B TABLE(ID_B NUMERIC(9,0),ID_AAA VARCHAR(100))
Declare @temp table(F1 varchar(100))
Declare @SourceSql varchar(100)
INSERT INTO @A
SELECT 1,'C1'
UNION ALL
SELECT 2,'C2'
UNION ALL
SELECT 3,'C2'
UNION ALL
SELECT 4,'C2'
UNION ALL
SELECT 5,'C2'
UNION ALL
SELECT 6,'C2'
select * from @A
INSERT INTO @B
SELECT 1,'1,2'
UNION ALL
SELECT 2,'2,3,4'
UNION ALL
SELECT 3,'2,4,6'
set @SourceSql=(SELECT ID_AAA FROM @B WHERE ID_B=1)declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(',',@SourceSql)
while @i>=1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(',',@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
select * from @temp
SELECT * FROM @A
WHERE ID_A IN (SELECT F1 FROM @temp)
哪裡報錯就修改哪裡呀
試試. 樓上幾位的效率比這個高.
SELECT * FROM @A
WHERE ID_A IN (SELECT cast(ID_AAA as NUMERIC(9,0)) FROM @B WHERE ID_B=1)
SELECT * FROM @A
WHERE charindex(','+ltrim(ID_A)+',' ,','+ltrim((SELECT ID_AAA FROM @B WHERE ID_B=1))+',')>0
WHERE charindex(','+rtrim(ID_A)+',', ','+ID_AAA+',')>0 and ID_B=1
WHERE charindex(','+rtrim(ID_A)+',', ','+ID_AAA+',')>0 and ID_B=1这样就妥了 拿分
ltrim -- > cast(id_aaa as varchar(100))