mysql - 子查询、临时表、mysql 变量或从一个表的列中获取 pos 和 neg 搜索词以查询另一个的过程

Question

我正在通过一组冒险关键字术语查询交易标题表，并在与冒险相关的术语之一出现在标题中时标记相关冒险。为了在最少数量的查询中匹配多个冒险和多个交易，我将其分为两个步骤:

1. 我创建了一个临时表，将每个冒险的所有搜索词加入一个由“|”连接的列中所以我可以在正则表达式中使用它。

2. 将此临时表与 deals.title regexp temp.concatenated 搜索词上的 deals 表连接起来。

效果很好，但我有两个问题:

1. 我可以在不创建临时表的情况下在子查询中执行此操作吗？

2. 我想在我的搜索词表中引入一个状态，将搜索词分类为负面或正面。然后我想搜索交易标题并仅当标题中有一个或多个正面术语并且标题中没有负面术语时才返回匹配项。我不知道如何从同一个表中获取两种不同状态的结果，然后使用这些结果来加入/查询另一个表。我该怎么做呢？？我还没有在 sql 中使用变量或存储过程，但看起来这可能是可行的方法？

这是代码的第一部分。

我有一张与冒险相关的搜索词表:

search_terms
id | adventure_id | term
130     124     kitesurf    
131     124     kiteboard   
132     124     kite surf   
133     124     kite-surf   
134     124     kite board  
135     125     water ski
    135     125     waterski


adventures
id | activity
124  Kite boarding
125  Waterskiing

以及与企业相关的交易表

deals
id | title | biz_id ...
3    50% off waterskiing on the lake 10

SQL:

create temporary table tmp1 as
select adv.activity,st.adventure_id, GROUP_CONCAT(st.term SEPARATOR '|') as `match`
FROM (search_terms st)
JOIN adventures adv ON adv.id=st.adventure_id
GROUP BY adv.id;

select d.biz_id, temp.adventure_id, d.id as deal_id
from daily_deals d
JOIN tmp1 temp ON d.title REGEXP temp.match;

现在进入第二个问题。我在 search_terms 表中添加了一个“状态”列，所以 搜索词
编号 | adventure_id |术语 |状态(是或否)

基本上我希望能够说 GROUP_CONCAT(st.term that are status=yes SEPARATOR '|') as **yes** 和 GROUP_CONCAT( st.term (that are status=no) SEPARATOR '|') as **no** 然后在临时表中执行

select d.biz_id, temp.adventure_id, d.id as deal_id
        from daily_deals d
        JOIN tmp1 temp ON d.title REGEXP temp.yes AND NOT_REGEXP temp.no;

我怎样才能得到这样的结果？感谢您的帮助，这是我第一次使用 SO。

Answer 1

1. Can I do this in a subquery without creating a temporary table?

例如，通常您可以用派生查询替换临时表

SELECT d.biz_id, 
       TEMP.adventure_id, 
       d.id AS deal_id 
FROM   daily_deals d 
       JOIN (SELECT adv.activity, 
                    st.adventure_id, 
                    GROUP_CONCAT(st.term SEPARATOR '|') AS `match` 
             FROM   search_terms st 
                    JOIN adventures adv 
                      ON adv.id = st.adventure_id 
             GROUP  BY adv.id) TEMP 
         ON d.title REGEXP TEMP.MATCH;