--这个意思? create table t(a int) insert into t select 1 insert into t select 2 insert into t select 3 insert into t select 4 insert into t select 5 insert into t select 6 insert into t select 7 insert into t select 8declare @str varchar(1000) set @str='1,2,3,6,7' exec('select * from t where a in('+@str+')')drop table t
--这样? create table t(a int) insert into t select 1 insert into t select 2 insert into t select 3 insert into t select 4 insert into t select 5 insert into t select 6 insert into t select 7 insert into t select 8create table a(id int,b varchar(10)) insert into a select 1,'1,2,3,4' insert into a select 2,'7,8' insert into a select 3,'6' insert into a select 4,'4,7'declare @str varchar(1000) select @str=b from a where id=2 exec('select * from t where a in('+@str+')')drop table t,a
DECLARE @t TABLE(ID int, UserFav varchar(8000)) INSERT @t SELECT 1, '1,3,5,7' -- 分拆处理 SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id) FROM @t A, # B WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','
DECLARE @t TABLE(ID int, UserFav varchar(8000)) INSERT @t SELECT 1, '1,3,5,7' -- 分拆处理 SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id) FROM @t A, # B WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','
DECLARE @t TABLE(ID int, UserFav varchar(8000)) INSERT @t SELECT 1, '1,3,5,7' -- 分拆处理 SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id) FROM @t A, # B WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','
标识列!
字段UserFav里有数据1,3,5,7等数据
1,3,5,7代表歌曲的ID.
想从这个字段里分别把1,3,5,7读取出来.
create table t(a int)
insert into t select 1
insert into t select 2
insert into t select 3
insert into t select 4
insert into t select 5
insert into t select 6
insert into t select 7
insert into t select 8declare @str varchar(1000)
set @str='1,2,3,6,7'
exec('select * from t where a in('+@str+')')drop table t
create table t(a int)
insert into t select 1
insert into t select 2
insert into t select 3
insert into t select 4
insert into t select 5
insert into t select 6
insert into t select 7
insert into t select 8create table a(id int,b varchar(10))
insert into a select 1,'1,2,3,4'
insert into a select 2,'7,8'
insert into a select 3,'6'
insert into a select 4,'4,7'declare @str varchar(1000)
select @str=b from a where id=2
exec('select * from t where a in('+@str+')')drop table t,a
INSERT @t SELECT 1, '1,3,5,7'
-- 分拆处理
SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a
SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id)
FROM @t A, # B
WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','
INSERT @t SELECT 1, '1,3,5,7'
-- 分拆处理
SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a
SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id)
FROM @t A, # B
WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','
INSERT @t SELECT 1, '1,3,5,7'
-- 分拆处理
SELECT TOP 8000 id = IDENTITY(int,1,1) INTO # FROM syscolumns a
SELECT A.ID, UserFav = SUBSTRING(A.UserFav, B.id, CHARINDEX(',', A.UserFav + ',', B.id) - B.id)
FROM @t A, # B
WHERE SUBSTRING(',' + A.UserFav, B.id, 1) = ','