sql in 操作

[woaivs]

表tableOne
字段 A 类型bigint

有如下需求
declare @strTest varchar(4000)
set @strTest = '1,2,3,5,7'

需要这样的查询结果 select * from tableOne where A in (strTest)


需要一个in的查询
请问sql语句或存储过程怎么写？谢谢！

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：tableOne
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tableOne') 
AND type in (N'U')) 
DROP TABLE tableOne
GO

---->建表
create table tableOne([A] varchar(9))
insert tableOne
select '1,2,3,5,7' union all
select '2,6'
GO


declare @strTest varchar(4000) 
set @strTest = '1,2,3,5,7'
SELECT *
FROM tableOne
where CHARINDEX(','+[A],','+@strTest+',')>0
--> 删除表格
--DROP TABLE tableOne

[woaivs]

[Quote=引用 1 楼 wxf163 的回复:]
SQL code

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：tableOne
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tableOne')
AND type in (N'U'))
DR……
[/Quote]


tableOne是已经建好 存在的表。字段A是bigInt类型

[abuying]

declare @strTest varchar(4000)  
set @strTest = '1,2,3,5,7'

declare @sql varchar(4000)

set @sql=' select * from tableOne where A in ('+strTest')'

exec(@sql)

[王向飞]

有区别吗？

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tableOne') 
AND type in (N'U')) 
DROP TABLE tableOne
GO

---->建表
create table tableOne([A] bigInt)
insert tableOne
select '1' union all
select '2' union all
select '8'
GO


declare @strTest varchar(4000) 
set @strTest = '1,2,3,5,7'
SELECT *
FROM tableOne
where CHARINDEX(','+ltrim([A]),','+@strTest+',')>0

[cs_lb]

--定义变量
declare @strTest varchar(4000)
declare @tempsql varchar(4000) 
--赋值
set @strTest = '1,2,3,5,7'
set @tempsql=' select * from tableOne where A in ('+strTest')'
--执行
exec(@tempsql)

[jcx396158820]

已有答案

[fengyun142415]

'+@strTest+'