MySQL-具有RLIKE和GROUP BY的UNION SELECT-无法插入INNER JOIN

debugcn 发表于 Dev

微核

我创建了一个mySQL查询，以按相关性搜索单词和结果（Sortkey）。例如，如果我搜索“测试词”，我的查询将看起来像这样（它像一个超级按钮一样起作用）：

SELECT * 
FROM (  SELECT *, 1 as SortKey 
        FROM equipments 
        WHERE (
            DESCRIPTION RLIKE '[[:<:]]test word[[:>:]]' 
            OR EQUIPMENT_ID RLIKE '[[:<:]]test word[[:>:]]' 
            OR CATEGORY RLIKE '[[:<:]]test word[[:>:]]'
        )

        UNION SELECT *, 2 as SortKey 
        FROM equipments 
        WHERE (
                (DESCRIPTION RLIKE '[[:<:]]test[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]test[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]test[[:>:]]' OR 
                DESCRIPTION RLIKE '[[:<:]]tests[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]tests[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]tests[[:>:]]') 

                AND

                (DESCRIPTION RLIKE '[[:<:]]word[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]word[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]word[[:>:]]' OR 
                DESCRIPTION RLIKE '[[:<:]]words[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]words[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]words[[:>:]]')
            )

        UNION SELECT *, 3 as SortKey 
        FROM equipments 
        WHERE (
                (DESCRIPTION RLIKE '[[:<:]]test[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]test[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]test[[:>:]]' OR 
                DESCRIPTION RLIKE '[[:<:]]tests[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]tests[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]tests[[:>:]]')
                ) 
                OR (
                (DESCRIPTION RLIKE '[[:<:]]word[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]word[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]word[[:>:]]' OR 
                DESCRIPTION RLIKE '[[:<:]]words[[:>:]]' OR 
                EQUIPMENT_ID RLIKE '[[:<:]]words[[:>:]]' OR 
                CATEGORY RLIKE '[[:<:]]words[[:>:]]')
            )

) T1 GROUP BY EQUIPMENT_ID ORDER BY `SortKey` ASC

因此，正如我所说，它可以工作，但是我想在查询中插入INNER JOIN，然后尝试将其放置在任何地方，但没有任何效果。通常，要显示所有结果而不寻找关键字，我使用此查询（再一次，它可以正常工作）：

SELECT
    `equipments`.`DESCRIPTION`,
    `equipments`.`EQUIPMENT_ID`,
    `equipments`.`CATEGORY`,
    `live_data`.`SERVICE`,
    `live_data`.`HOURS` 
FROM equipments 
INNER JOIN customers ON `customers`.`EQUIPMENT_ID` = `equipments`.`EQUIPMENT_ID`
LEFT JOIN live_data ON `live_data`.`EQUIPMENT_ID` = `equipments`.`EQUIPMENT_ID`
WHERE `customers`.`customer_id` = 1

我需要插入INNER JOIN还要添加WHERE语句以仅搜索客户的设备。请帮我！

托尼·霍普金森

select innerresult.* From
( Insert your query here ) innerresult
inner join customers On customers.equipment_id = innerresult.equipment_id
Where customers.customer_id = 1

我认为应该这样做。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。