一条sql不行.declare @sql select @sql='select * from table2 where xmid in("+bookname+")"; //如果bookname 前后都有,号的话,还要先处理掉. exec (@sql)
select b.xmmc from table1 a left join table2 b on a.bookname = b.xmid
首先将table1中bookname拆分 可以使用一个函数CREATE FUNCTION [dbo].[Split] (@origStr varchar(8000), --待拆分的字符串 @Str varchar(100)) --拆分标记,如',' RETURNS @splittable table ( id varchar(100) NOT NULL, --编号ID item varchar(100) NOT NULL --拆分后的字符串 ) AS BEGIN DECLARE @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid int SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr='',@TEMPid=0
if(RIGHT(@origStr,1)<>@Str ) BEGIN SET @origStr = @origStr + @Str END WHILE((@postion<=@strlen) and (@postion !=0)) BEGIN IF(CHARINDEX(@Str,@origStr,@postion)!=0) BEGIN SET @sublen=CHARINDEX(@Str,@origStr,@postion)-@postion; END ELSE BEGIN SET @sublen=@strlen-@postion+1; END IF(@postion<=@strlen) BEGIN SET @TEMPid=@TEMPid+1; SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen); INSERT INTO @splittable(id, item) values(@TEMPid,@TEMPstr) IF(CHARINDEX(@Str,@origStr,@postion)!=0) BEGIN SET @postion=CHARINDEX(@Str,@origStr,@postion)+1 END ELSE BEGIN SET @postion=@postion+1 END END END RETURN END2、如2楼所述,select * from table2 where xmid in ().......lz,剩下的就不用说了吧
select @sql='select * from table2 where xmid in("+bookname+")"; //如果bookname 前后都有,号的话,还要先处理掉.
exec (@sql)
left join table2 b on a.bookname = b.xmid
可以使用一个函数CREATE FUNCTION [dbo].[Split]
(@origStr varchar(8000), --待拆分的字符串
@Str varchar(100)) --拆分标记,如','
RETURNS @splittable table
(
id varchar(100) NOT NULL, --编号ID
item varchar(100) NOT NULL --拆分后的字符串
)
AS
BEGIN
DECLARE @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid int
SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr='',@TEMPid=0
if(RIGHT(@origStr,1)<>@Str )
BEGIN
SET @origStr = @origStr + @Str
END
WHILE((@postion<=@strlen) and (@postion !=0))
BEGIN
IF(CHARINDEX(@Str,@origStr,@postion)!=0)
BEGIN
SET @sublen=CHARINDEX(@Str,@origStr,@postion)-@postion;
END
ELSE
BEGIN
SET @sublen=@strlen-@postion+1;
END
IF(@postion<=@strlen)
BEGIN
SET @TEMPid=@TEMPid+1;
SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);
INSERT INTO @splittable(id, item) values(@TEMPid,@TEMPstr)
IF(CHARINDEX(@Str,@origStr,@postion)!=0)
BEGIN
SET @postion=CHARINDEX(@Str,@origStr,@postion)+1
END
ELSE
BEGIN
SET @postion=@postion+1
END
END
END
RETURN
END2、如2楼所述,select * from table2 where xmid in ().......lz,剩下的就不用说了吧
bookname
,2,3,4,字典表
table2
xmmc xmid
书1 1
书2 2
书3 3
书4 4
SELECT table2.xmmc from table2,table1 where charIndex(table2.xmid,table1.bookname) > 0