子查询中的AND操作 [英] AND operation in SubQuery

问题描述

我正在向一个函数发送一个字符串(参数)，该函数向我返回了一个包含单词行的表.在这里，我想从包含所有这些单词的表中获取记录.我尝试将以下查询作为存储过程，但返回的状态类似于"OR".谁能帮我这个?

Hi,
I am sending a string(param) to a function and that function returns me a table containing rows of words. Here I want to fetch the records from table which containing all those words. i tried the below query as a stored procedure but it returns me like ''OR'' condition. Can anyone help me with this?

select DISTINCT (Field1+ '' '' +Field2) from table1 where exists(select DField from function1(@Param)where Field2 like ''%''+DField+''%''

[edit]

谢谢回复.这里是存储过程.

[edit]

Thanks for the reply. Here the stored procedure.

ALTER procedure [dbo].[ICDSEARCH]
@Description nvarchar(600)
as
begin
set nocount on;
 select DISTINCT (CODE+ ' ' +DESCRIPTION) from ICDCODE where exists(select DField from dbo.splitstring(@description)where DESCRIPTION like '%'+DField+'%')

END

这是表值函数

Here the Table Valued Function

ALTER FUNCTION  [dbo].[splitstring]
(
   @Param_des varchar(500)
)
RETURNS @d_Table TABLE(DField nvarchar(200))

AS
BEGIN  
   IF (LEN(@Param_des) = 0) 
      RETURN

   DECLARE @SpacePos smallint
   SET @SpacePos = CHARINDEX('' '', RTRIM(LTRIM(@Param_des)))	  

   IF @SpacePos = 0
       INSERT INTO @d_Table 
              VALUES(CONVERT(nvarchar ,RTRIM(LTRIM(@Param_des))))
   ELSE 
       BEGIN
           WHILE LEN(@Param_des)> 1
	   BEGIN
	     SET @SpacePos = CHARINDEX('' '', RTRIM(LTRIM(@Param_des)))
             INSERT INTO @d_Table 
                      VALUES(CONVERT(nvarchar,SUBSTRING(RTRIM(LTRIM(@Param_des)),1, @SpacePos - 1)))
	     SET @Param_des = SUBSTRING(RTRIM(LTRIM(@Param_des)), @SpacePos + 1 , LEN(RTRIM(LTRIM(@Param_des))))
	     SET @SpacePos = CHARINDEX('' '', RTRIM(LTRIM(@Param_des)))
	     IF @SpacePos = 0
	     BEGIN
                 INSERT INTO @d_Table VALUES(CONVERT(nvarchar ,RTRIM(LTRIM(@Param_des))))
                 BREAK
	     END
	   END
       END
       RETURN 
end

上面的存储过程将给我一个结果，其中的描述包含了我从函数中获得的表中的任何单词(因为我使用它的方式就像是充当"OR"一样).而不是我想要的输出提供给我所有单词都出现在其中的记录.(AND运算，而不是OR)

希望您能理解.

the stored procedure above will give me a result where description containing any of the words of the table that i got from the function(since i have used like it acts as ''OR''). instead of that i want the output that gives me the records in which all the words occurs.(AND Operation instead of OR)

Hope you got it.

推荐答案

您的问题尚不清楚，但这是一个猜测:

如果可以将全文搜索功能添加到数据库，则可以使用CONTAINS功能:

Your question isn''t clear but here''s a guess:

If you can add full-text search capabilities to the database you can use the CONTAINS functionality:

WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, ' Aluminum AND spindle');

http://msdn.microsoft.com/en-us/library/ms187787.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ms187787.aspx[^]

这篇关于子查询中的AND操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！