MySQL -使用RLIKE和GROUP无法插入内部连接的UNION SELECT
MySQL -使用RLIKE和GROUP无法插入内部连接的UNION SELECT
提问于 2013-12-11 14:15:28
我创建了一个mySQL查询，通过关联搜索单词和排序结果(Sortkey)。例如，如果我搜索"test word"，我的查询将如下所示(而且它的工作原理类似于魅力)：
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所以，正如我所说的，它可以工作，但我想在查询中插入内部联接，我试图将它放在任何地方，但没有任何工作。通常，为了在不查找关键字的情况下显示所有结果，我使用此查询(再次，它工作得很好)：
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我需要插入内部连接，也添加WHERE语句搜索客户的设备。请帮帮我!
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-12-11 14:25:27
select innerresult.* From
( Insert your query here ) innerresult
inner join customers On customers.equipment_id = innerresult.equipment_id
Where customers.customer_id = 1我觉得应该这么做。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20521469
复制相关文章
相似问题
腾讯云开发者
