mysql - 执行内部连接过滤定义的值

Question

假设我有以下两个表。当在 A1.pagetitle、A1.content 或 A2.value< 中找到匹配项时，我想要实现的是返回排名结果的搜索 - 按此顺序。

但我只需要包含第二个表中的结果，其中 tmplvarid 匹配特定值(在本例中为 15 或 17)。

 mod_site_content (A1)
 +----+-----------+--------------+
 | id | pagetitle | content      |
 +----+-----------+--------------+
 | 1  | home      | <p> ... </p> |
 | 2  | project 1 | <p> ... </p> |
 | 3  | comment 1 | <p> ... </p> |
 | 4  | project 2 | <p> ... </p> |
 | 5  | project 3 | <p> ... </p> |
 | 6  | comment 2 | <p> ... </p> |
 | etc ...                       |
 +----+-----------+--------------+

 mod_site_tmplvar_contentvalues (A2)
 +----+-----------+-----------+-----------------+
 | id | tmplvarid | contentid | value           |
 +----+-----------+-----------+-----------------+
 | 1  | 2         | 1         | [{"key":"val"}] |
 | 2  | 2         | 2         | [{"key":"val"}] |
 | 3  | 5         | 1         | [{"key":"val"}] |
 | 4  | 5         | 5         | [{"key":"val"}] |
 | 5  | 15        | 4         | [{"key":"val"}] |
 | 6  | 15        | 5         | [{"key":"val"}] |
 | etc ...                                      |
 +----+-----------+-----------+-----------------+

我想我快到了——目前我有:

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
INNER JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
WHERE pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?
ORDER BY rank

但这包括 所有 相应的 contentid - 我一辈子都想不出如何合并只包含某些条件的条件。

Answer 1

当您加入时，像这样过滤加入列上的结果

对于在两个表中都返回的行:

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
INNER JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
    AND A2.tmplvarid IN(15,17)
WHERE (pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?)

DEMO

---

从 mod_site_content 返回行和从 mod_site_tmplvar_contentvalues 返回任何匹配

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
LEFT JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
    AND A2.tmplvarid IN(15,17)
WHERE (pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?)

DEMO