22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
CREATE TABLE a (id INT)
CREATE TABLE b (abc NVARCHAR(50))
INSERT INTO a VALUES(3427)
INSERT INTO b VALUES('3427,3432')
--方法1
SELECT * FROM a CROSS APPLY b
WHERE ','+b.abc+',' LIKE '%,'+cast(a.id AS VARCHAR(50))+',%'
/*
id abc
3427 3427,3432
*/
--方法2
--1. 加函数
GO
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL DROP FUNCTION [dbo].[Fun_Split]
GO
-- =============================================
-- Author : yenange
-- Create date: 2014-03-04
-- Description: 切分字符串
-- Example : SELECT * FROM [dbo].[Fun_Split]('a,b,d,c',',')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
(
@str NVARCHAR(MAX),
@separator NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (order by (select 0)) AS rowNum, B.id
FROM (
SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
) A
OUTER APPLY(
SELECT id = N.v.value('.', 'nvarchar(max)')
FROM A.[value].nodes('/v') N(v)
) B
WHERE ISNULL(B.id,'')!=''
)
GO
SELECT * FROM a WHERE a.id IN (
SELECT fs.id FROM b CROSS APPLY [dbo].[Fun_Split](b.abc,',') AS fs
)
/*
id
3427
*/
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[name] nvarchar(23))
Insert #A
select 1,N'测试1' union all
select 2,N'测试2' union all
select 3427,N'测试3' union all
select 3432,N'测试4' union all
select 3355,N'测试5'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([abc] nvarchar(29))
Insert #B
select N'3427,3432' union all
select N'11111'
Go
--测试数据结束
--楼主的语句有问题,按语句的意思是id为'3427,3432'和'11111'的数据,但是看楼主描述是id为3427,3432值,所以不能把B表数据全部读取
DECLARE @sql NVARCHAR(max)='
SELECT * from #A where id in ('+(SELECT TOP 1 abc FROM #B)+')'
EXEC(@sql)