有问题的地方

Question

形式提供了几个表

我已经以MainRecord

Record ID  Details  Textual Information  
    1      AAAAAAA  ... some text referring to Oxford St Giles...  
    2      BBBBBBB  ... some text referring Oxford....  
    3      CCCCCCC  ... some text referring to Oxford St Aldate...  

，并支持表

PlaceNames

Record ID   PlaceName
    1        Oxford  
    1        St  
    1        Giles  
    2        Oxford  
    3        Oxford  
    3        St  
    3        Aldate  

我希望能够构建一个搜索词，以便我可以输入全部或部分词关于地名。例如，如果我输入“Oxford”，我会得到所有 3 条记录，如果我输入“Oxford”和“Giles”，我只会得到记录 1 - 几乎就像使用 WHERE IN ('Oxford','Giles')，但这些术语是 AND 组合被ORed？

不知道我能不能实现这个目标？我尝试了各种子查询但没有成功

我正在使用 SQL Server 2008

我想避免创建全文搜索字段

任何清除迷雾的指针都会非常有帮助。

* 更新主要记录详细信息以避免混淆 *
两个表之间的唯一链接是记录 ID

** 更新 ** 11 月 3 日包含示例表

CREATE TABLE MAIN_RECORD (RecordID int,DocumentRef varchar(100));  
INSERT INTO MAIN_RECORD VALUES (86, 'Doc Referring to William Samuel ADAMS');  
INSERT INTO MAIN_RECORD VALUES (87, 'Doc Referring to William JONES');  
INSERT INTO MAIN_RECORD VALUES (88, 'Doc Referring to Samuel SMITH');  



CREATE TABLE FORENAMES (RecordID int,Forename varchar(25));  
INSERT INTO FORENAMES VALUES (86, 'William');  
INSERT INTO FORENAMES VALUES (86, 'Samuel');  
INSERT INTO FORENAMES VALUES (87, 'William');  
INSERT INTO FORENAMES VALUES (88, 'Samuel');  

我的初始查询是

SELECT main.[RecordID],main.documentRef  
    FROM [MAIN_RECORD] main  
    INNER JOIN  [FORENAMES] fn  
    ON main.RecordID = fn.RecordID  
    WHERE fn.ForeName IN ('William')  

This is Fine and returns

RecordID    documentRef  
86  Doc Referring to William Samuel ADAMS  
87  Doc Referring to William JONES  

ditto with Samuel, etc

我的问题是当我在名字搜索字段中有超过 1 个条目时即

SELECT main.[RecordID],main.documentRef  
    FROM [MAIN_RECORD] main  
    INNER JOIN  [FORENAMES] fn  
    ON main.RecordID = fn.RecordID  
    WHERE fn.ForeName IN ('William,Samuel') 

这不会返回任何内容。

我需要这个只返回包含塞缪尔和威廉的主记录，即。当搜索词包含多个名称时。
它还需要找到威廉·塞缪尔以及塞缪尔·威廉。

从其他人的帖子中，我沿着 DIVISION 的路线走了下去，并提出了以下内容（在主 SELECT 之前放置一些字符串操作）：

DECLARE @Forename nvarchar(max)
DECLARE @SQLCommand nvarchar(max)
DECLARE @Number_of_Terms int
SET @Forename = 'William,Samuel'
--SET @Forename = 'Samuel,William'
--SET @Forename = 'William'
--SET @Forename = 'Samuel'
SET @Number_of_Terms = LEN(@Forename) - LEN(REPLACE(@Forename,',',''))+1
SET @Forename = REPLACE(@Forename,',',''',''') 

SET @SQLCommand = 'SELECT fr.RecordID FROM dbo.BRS109_FullRecord fr '+
'INNER JOIN dbo.BRS109_Forenames fn '+
'ON fr.RecordID = fn.RecordID '+
'WHERE fr.RecordID = fn.RecordID '+
'AND fn.forename IN ('''+@Forename +''') ' +
' GROUP BY fr.RecordID ' +
' HAVING COUNT(fr.RecordId) = ' + CAST(@Number_of_Terms AS varchar(2)) +
' ORDER BY fr.RecordId'

EXECUTE sp_executesql @SQLCommand

这似乎给了我我正在寻找的东西。

非常感谢大家的贡献，特别是“Quassnoi”和“onedaywhen”——非常有帮助

Answer 1

SELECT  *
FROM    mainrecord mr
WHERE   (
        SELECT  COUNT(*)
        FROM    placenames pn
        WHERE   pn.record = mr.record
                AND pn.placename IN ('Oxford', 'St', 'Giles')
        ) = 3

Answer 2

您是在暗示关系划分吗？例如提供所有产品的供应商、可以驾驶机库中所有飞机的飞行员等？

如果是这样，本文有许多 SQL 实现示例。

这是使用您的数据的一个：

WITH MainRecord (Record_ID, Details, Textual_Information)
     AS
     (
      SELECT Record_ID, Details, Textual_Information
        FROM (
              VALUES (1, 'AAAAAAA', ' ... some text referring to Oxford St Giles... '), 
                     (2, 'BBBBBBB', ' ... some text referring Oxford.... '), 
                     (3, 'CCCCCCC', '  ... some text referring to Oxford St Aldate... ')
             ) AS MainRecord (Record_ID, Details, Textual_Information)
     ), 
     PlaceNames (Record_ID, PlaceName)
     AS
     (
      SELECT Record_ID, PlaceName
        FROM (
              VALUES (1, 'Oxford'), 
                     (1, 'St'), 
                     (1, 'Giles'), 
                     (2, 'Oxford'), 
                     (3, 'Oxford'), 
                     (3, 'St'), 
                     (3, 'Aldate')
             ) AS PlaceNames (Record_ID, PlaceName)
     ),
     FullSet (Record_ID, PlaceName, Textual_Information)
     AS
     (
      SELECT DISTINCT P1.Record_ID, P1.PlaceName, 
             M1.Textual_Information 
        FROM MainRecord AS M1
             CROSS JOIN PlaceNames AS P1
     ), 
     NoMatch (Record_ID, PlaceName, Textual_Information)
     AS 
     (
      SELECT F1.Record_ID, F1.PlaceName, F1.Textual_Information
        FROM FullSet AS F1
              EXCEPT
      SELECT P1.Record_ID, P1.PlaceName, 
             M1.Textual_Information 
        FROM MainRecord AS M1
             INNER JOIN PlaceNames AS P1
               ON M1.Textual_Information LIKE '%' + P1.PlaceName + '%'
      )
SELECT F1.Record_ID, F1.PlaceName, F1.Textual_Information
  FROM FullSet AS F1
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM NoMatch AS N1
                     WHERE N1.Record_ID = F1.Record_ID
                           AND N1.Textual_Information = F1.Textual_Information
                   );

---

更新：

我更喜欢您的原始数据；）无论如何，我建议的方法是相同的，即关系划分（这次有更好的连接）：

WITH FullSet (RecordID, Forename, DocumentRef)
     AS
     (
      SELECT DISTINCT P1.RecordID, P1.Forename, 
             M1.DocumentRef 
        FROM MAIN_RECORD AS M1
             INNER JOIN FORENAMES AS P1
                ON M1.RecordID = P1.RecordID
     ), 
     NoMatch (RecordID, Forename, DocumentRef)
     AS 
     (
      SELECT F1.RecordID, F1.Forename, F1.DocumentRef
        FROM FullSet AS F1
              EXCEPT
      SELECT P1.RecordID, P1.Forename, 
             M1.DocumentRef 
        FROM MAIN_RECORD AS M1
             INNER JOIN FORENAMES AS P1
               ON M1.RecordID = P1.RecordID
                  AND M1.DocumentRef LIKE '%' + P1.Forename + '%'
      )
SELECT F1.RecordID, F1.Forename, F1.DocumentRef
  FROM FullSet AS F1
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM NoMatch AS N1
                     WHERE N1.RecordID = F1.RecordID
                           AND N1.DocumentRef = F1.DocumentRef
                   );

Answer 3

不太确定在您的环境中，但在 Oracle 中，这个应该可以工作。

select * from mainrecord
where placename like '%Oxford%'
INTERSECT
select * from mainrecord
where placename like '%Giles%'

Answer 4

使用 LIKE 语句
像这样：

SELECT * 
FROM table AS t 
WHERE t.PlaceName LIKE "%Oxford%" AND t.PlaceName LIKE "%Giles%"

使用这个查询你不需要第二个表，
一切都会通过第一个完成

Answer 5

如果这就是您所需要的，您可以使用 LIKE

SELECT PlaceName FROM PlaceNames WHERE PlaceName LIKE "%Oxford%" AND PlaceName LIKE "%Giles%"

如果您需要更多的灵活性（例如限制匹配整个单词），您可以 轻松将正则表达式搜索添加到 SQL Server

    SELECT PlaceName FROM PlaceNames WHERE dbo.RegExMatch(PlaceName,'\bOxford\b') = 1 
AND dbo.RegExMatch(PlaceName,'\bGiles\b') = 1