嘿,伙计们,我有一个查询,目前从一个表单的关键字列表,并返回前5名匹配的相关语句。它们在两个不同的表中(Keyword
&Problem
)。我遇到的问题是,如果有人搜索TURN,返回一个结果,但如果你搜索TURN,没有结果。我知道这似乎很简单,但我正在工作的查询是有点少。这是我正在使用的查询:
SELECT p.*, pc.wordCount, pc.commonCount
FROM Problem p INNER JOIN (
SELECT p.Problem_Identity, COUNT(k.Keyword_Identity) AS "wordCount",
SUM(k.Keyword_Common) as "commonCount"
FROM( SELECT * FROM Keyword ) k
INNER JOIN KeywordsInProblem kip ON (k.Keyword_Identity = kip.Keyword_Identity)
INNER JOIN Problem p ON (p.Problem_Identity = kip.Problem_Identity)
WHERE UCASE(k.Keyword_Word) IN(''. strtoupper(implode("', '", $keyArr)) .'')
GROUP BY p.Problem_Identity) pc
ON (p.Problem_Identity = pc.Problem_Identity) where pc.commonCount > 0
ORDER BY pc.wordCount DESC, pc.commonCount DESC LIMIT 5
PS: $keyArr
变量是一个爆炸表单输入语句,以便在数据库中使用它之前对其进行消毒。
$likeCondition = "";
foreach($keyArr as $key)
{
$likeCondition .= (strlen(trim($likeCondition)))?
" OR UCASE(k.Keyword_Word) LIKE '%".strtoupper($key)."%' ":
" WHERE UCASE(k.Keyword_Word) LIKE '%".strtoupper($key)."%' ";
}
$sql = "SELECT
p.*,
pc.wordCount,
pc.commonCount
FROM
Problem p
INNER JOIN (
SELECT
p.Problem_Identity,
COUNT(k.Keyword_Identity) AS wordCount,
SUM(k.Keyword_Common) AS commonCount
FROM(
SELECT * FROM Keyword) k
INNER JOIN
KeywordsInProblem kip
ON
(k.Keyword_Identity = kip.Keyword_Identity)
INNER JOIN
Problem p
ON
(p.Problem_Identity = kip.Problem_Identity)
".$likeCondition."
GROUP BY p.Problem_Identity) pc
ON
(p.Problem_Identity = pc.Problem_Identity)
WHERE
pc.commonCount > 0
ORDER BY pc.wordCount DESC, pc.commonCount DESC LIMIT 5";
您必须遍历$keyArr并在每次迭代中连接它
OR UCASE(k.Keyword_Word) like ''.$keyArr[i] .'% '
用()包装所有这些限制