mySQL查询 - 多表 - 按相关性排序 - 删除重复项

wfuga 发布于 2019-03-09 mysql 最后更新 2019-03-09 14:35 0 浏览

我是先进的MySQL查询,所以请在这里很好... 让我们假设我有下面的表格......

Table Name: users
username   other_non_relevant_field
========   ========================
Bob        blah blah blah
Steve      blah blah blah
Adam       blah blah blah
Table Name: table_1
username    field_abc    field_def    other_non_relevant_field
========    =========    =========    ========================
Steve       quick        brown        blah blah blah
Adam        fox          quick        blah blah blah
Table Name: table_2
username    field_ghi    field_jkl    other_non_relevant_field
========    =========    =========    ========================
Bob         fox          fox          blah blah blah
Bob         brown        quick        blah blah blah
Steve       fox          lazy         blah blah blah
Adam        jump         dog          blah blah blah
因此,让我们假设我想返回所有包含单词“quick”或“brown”的用户的列表,而不管他们在哪个表或字段中,并根据相关性显示结果。为此,我使用这个查询:
SELECT users.username, table_1.field_abc, table_1.field_def,
    table_2.field_ghi, table_2.field_jkl
FROM users
JOIN table_1 ON ( table_1.username=users.username )
JOIN table_2 ON ( table_2.username=users.username )
WHERE
    table_1.field_abc LIKE "%quick%" OR table_1.field_abc LIKE "%brown%"
    OR
    table_1.field_def LIKE "%quick%" OR table_1.field_def LIKE "%brown%"
    OR
    table_2.field_ghi LIKE "%quick%" OR table_2.field_ghi LIKE "%brown%"
    OR
    table_2.field_jkl LIKE "%quick%" OR table_2.field_jkl LIKE "%brown%"
ORDER BY (
    (
        CASE WHEN table_1.field_abc LIKE "%quick%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_1.field_abc LIKE "%brown%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_1.field_def LIKE "%quick%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_1.field_def LIKE "%brown%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_2.field_ghi LIKE "%quick%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_2.field_ghi LIKE "%brown%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_2.field_jkl LIKE "%quick%"
        THEN 1
        ELSE 0
        END
    ) + (
        CASE WHEN table_2.field_jkl LIKE "%brown%"
        THEN 1
        ELSE 0
        END
    )
) DESC;
我现在有几个问题... 1)查询似乎很长。有没有更简单的方法来做到这一点? 2)如何使LIKE不区分大小写,因此%qUiCk%仍会返回结果? 3)目前每个相关记录都被返回;但是,我实际上只希望每个用户得到一个结果,但首先列出的是最高的相关用户。我怎么做?
已邀请:

quo_et

赞同来自:

要回答问题1),您可以替换

LIKE '%a%' OR LIKE '%b%'
REGEXP '(a|b)'
其次,如果为true,则a REGEXP b返回1,如果为false,则返回0,因此您可以删除ORDER BY中的CASE。尝试这个:
SELECT users.username, table_1.field_abc, table_1.field_def,
    table_2.field_ghi, table_2.field_jkl
FROM users
JOIN table_1 ON table_1.username = users.username
JOIN table_2 ON table_2.username = users.username
WHERE
    table_1.field_abc REGEXP "(quick|brown)" OR
    table_1.field_def REGEXP "(quick|brown)" OR
    table_2.field_ghi REGEXP "(quick|brown)" OR
    table_2.field_jkl REGEXP "(quick|brown)"
ORDER BY (
    (table_1.field_abc REGEXP "(quick|brown)") +
    (table_1.field_def REGEXP "(quick|brown)") +
    (table_2.field_ghi REGEXP "(quick|brown)") +
    (table_2.field_jkl REGEXP "(quick|brown)")
) DESC;
回答问题2)。对于CHARVARCHARTEXT字段,LIKEREGEXP以及甚至=在MySQL中默认情况下不区分大小写。要执行区分大小写的搜索,您需要:
  1. 添加BINARY关键字。例如:REGEXP BINARY '(a|b)'
  2. 将字段类型更改为BINARYVARBINARY,或BLOB
  3. 将您要搜索的字段的字符排序规则更改为区分大小写的字符。
要回答问题3),您需要添加一个GROUP BY子句,例如:
SELECT users.username, SUM(
    (table_1.field_abc REGEXP "(quick|brown)") +
    (table_1.field_def REGEXP "(quick|brown)") +
    (table_2.field_ghi REGEXP "(quick|brown)") +
    (table_2.field_jkl REGEXP "(quick|brown)")
) hits,
    table_1.field_abc, table_1.field_def,
    table_2.field_ghi, table_2.field_jkl
FROM users
JOIN table_1 ON table_1.username = users.username
JOIN table_2 ON table_2.username = users.username
WHERE
    table_1.field_abc REGEXP "(quick|brown)" OR
    table_1.field_def REGEXP "(quick|brown)" OR
    table_2.field_ghi REGEXP "(quick|brown)" OR
    table_2.field_jkl REGEXP "(quick|brown)"
GROUP BY
    users.username
ORDER BY (
    (table_1.field_abc REGEXP "(quick|brown)") +
    (table_1.field_def REGEXP "(quick|brown)") +
    (table_2.field_ghi REGEXP "(quick|brown)") +
    (table_2.field_jkl REGEXP "(quick|brown)")
) DESC;