对来自第二个表的数据使用 Substring_Index [英] Using Substring_Index with data from a second table

问题描述

我想选择 wp_posts 表中匹配的所有内容:

I want to select everything in the wp_posts table that matches:

1. post_type = "answer"
2. post_type = 问题"
3. post_type 包含修订版，前面是上述任一条件的 ID.例如:21-revision-v1 或 10903-revision-v1 我想选择第一个数字部分与上一个选择的帖子 ID 匹配的帖子2 项要求.

1. post_type = "answer"
2. post_type = "question"
3. post_type contains revision, preceded by the ID of either one of the previous criteria. For example: 21-revision-v1 or 10903-revision-v1 Where I want to select those posts of which the first numerical part matches the ID of posts selected in the previous 2 requirements.

我现在构建了一个新表 ap_qa，其中包含匹配上述条件 1 或 2 的帖子的所有 ID.

I now constructed a new table ap_qa which holds all the ID's from posts matching either criteria 1 or 2 above.

现在选择匹配条件 3 的案例，我想到使用 Substring_Index() 因为它允许在字符串中进行匹配.

Now to select the cases that match criteria 3 I thought of using Substring_Index() as that allows for matches within a string.

我当前的代码是:

SELECT * 
FROM `wp_posts` p 
WHERE p.post_type IN ('answer', 'question') OR
Substring_Index(p.post_Type,'-revision',1) IN QA.ID

where 后面的第一条规则是满足条件 1 和 2，最后一行是满足条件 3.但是我的语法无效，返回.

The first rule following where is to satisfy criteria 1 and 2, the last row is meant to satisfy criteria 3. However my syntax is invalid, as is returned.

错误信息如下(荷兰语):

The error message reads (in Dutch):

#1064 - 在 regel 4 中的 de gebruikte 语法 bij 'QA.ID' 中的 Er 是 iets fout

#1064 - Er is iets fout in de gebruikte syntax bij 'QA.ID' in regel 4

推荐答案

我现在构建了一个新表 ap_qa，其中包含匹配上述条件 1 或 2 的帖子的所有 ID.

I now constructed a new table ap_qa which holds all the ID's from posts matching either criteria 1 or 2 above.

您根本不需要临时表.您可以在单个查询中直接从原始表中获得您想要的结果:

You don't at all need a temp table for this. You can get the result that you want directly from the original table in a single query:

select *
from wp_posts wp
where post_type in ('answer', 'question') and exists (
    select 1
    from wp_posts wp1
    where 
        wp1.post_type in ('answer', 'question') 
        or wp1.id = substring_index(wp.post_type, '-revision', 1)
)

这篇关于对来自第二个表的数据使用 Substring_Index的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！