我有一个多输入搜索表单,它有2个文本框。一个文本框用于"searchWords",另一个文本框用于"specificPeople"。
在第一个文本框中,您可能输入"Dorchester Hotel London",在第二个文本框中,您可能输入"Brad Pitt/Angelina Jolie"。使用ASP,我将第二个文本框的值转换为IN子句可以接受的格式,例如('Brad Pitt','Angelina Jolie')。
SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords
FROM photoSearch
JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.peopleID)
ON photoSearch.photoID = photoPeople.photoID AND people.people IN ('Brad Pitt','Angelina Jolie')
WHERE MATCH (caption, allPeople, allKeywords) AGAINST ('+dorchester +hotel' IN BOOLEAN MODE)
AND
photoSearch.dateCreated BETWEEN '2011-07-21' AND '2011-10-23'
ORDER BY photoSearch.dateCreated
这不会出错,但不会产生布拉德和安吉丽娜在一起的记录。它只显示了布拉德和安吉丽娜的记录。这就是我第一次意识到IN子句像OR一样工作的地方。
如何修改这个查询,返回具有这两个特定名称的行,而不是它们中的任何一个?
我的DB看起来像这样:
photoSearch
photoID INT / AUTO / INDEX
caption VARCHAR(2500) / FULLTEXT
allPeople VARCHAR(300) / FULLTEXT
allKeywords VARCHAR(300) / FULLTEXT
dateCreated DATETIME / INDEX
photoPeople
photoID INT / INDEX
peopleID INT / INDEX
people
peopleID INT / INDEX
people VARCHAR(100) / INDEX
任何帮助,感谢收到…一如既往:)
表内内容的示例:
<>之前photoSearchphotoID | caption | dateCreated1900多切斯特酒店…2011-10-03"photoPeople"[photoID] | [peopleID]1900年 147年1900年 148年"人"[peopleID] | [people]147布拉德·皮特148安吉丽娜·朱莉连接到photoPeople
和people
表n次,其中n是您要搜索的人数:
SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords
FROM photoSearch
JOIN ( photoPeople AS pp1 JOIN people AS p1 ON pp1.peopleID = p1.peopleID)
ON photoSearch.photoID = pp1.photoID AND p1.people = 'Brad Pitt'
JOIN ( photoPeople AS pp2 JOIN people AS p2 ON pp2.peopleID = p2.peopleID)
ON photoSearch.photoID = pp2.photoID AND p2.people = 'Angelina Jolie'
WHERE MATCH (caption, allPeople, allKeywords)
AGAINST ('+dorchester +hotel' IN BOOLEAN MODE)
AND photoSearch.dateCreated BETWEEN '2011-07-21' AND '2011-10-23'
ORDER BY photoSearch.dateCreated
问题是IN子句
AND people.people IN ('Brad Pitt','Angelina Jolie')
查找精确匹配,因此查询正在执行您所要求的操作。如果你做了
AND (people.people LIKE '%Brad Pitt%' OR people.people LIKE '%Angelina Jolie%')
你会得到你想要的结果。如果需要,可以使用
AND instr('/Brad Pitt/Angel Jolie/','/'+people.people+'/') > 0
你可以得到你期望的结果。
试试这个:
SELECT photoSearch.photoID, Left(photoSearch.caption,25), photoSearch.allPeople, photoSearch.allKeywords
FROM photoSearch ps
JOIN photoPeople pp on pp.photoId=ps.photoId
JOIN people on people.peopleId = pp.PeopleId
WHERE MATCH (caption, allPeople, allKeywords) AGAINST ('+dorchester +hotel' IN BOOLEAN MODE)
AND photoSearch.dateCreated BETWEEN '2011-07-21' AND '2011-10-23'
AND people.people IN ('Brad Pitt','Angelina Jolie')
ORDER BY photoSearch.dateCreated