根据表中的值查询表 [英] Query a table based on values from within that table

问题描述

我们有一个包含问答插件的 WordPress 网站，需要将其移至其他主机.Q&A 帖子仅占整个帖子表的一小部分，因此我想使用 SQL 查询将它们过滤掉.

We have a WordPress site that contains a Q&A plugin, that needs to be moved to a different hosting. The Q&A posts only make up for a fraction of the total Posts table, so I want to filter them out using a SQL query.

我想通过 PhpMyAdmin 为符合以下条件之一的行选择数据库中的所有内容:

I want to select everything in the DB, through PhpMyAdmin, for the rows that match one of the following criteria:

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.

我是 SQL 的完全新手，所以我从谷歌搜索开始，并发现了临时表的概念.这导致我创建了这段代码:

I am a complete novice to SQL so I started with some googling, and found the concept of Temporary Tables. Which lead me to create this bit of code:

SELECT *  INTO #QA 
FROM `wp_posts` WHERE 
`post_type` = "answer" OR
`post_type` = "question"

但是我收到以下错误:

#1064 - Er is iets fout in de gebruikte syntax bij 'FROM wp_posts WHERE
post_type = "answer";或
post_type = "问题";LIMI' in regel 2

#1064 - Er is iets fout in de gebruikte syntax bij 'FROM wp_posts WHERE
post_type = "answer" OR
post_type = "question" LIMI' in regel 2

翻译成附近的语法有问题"....

Which translates to "There is somthing wrong with the syntax near"....

我正在尝试的是否可行?

Is what I am attempting even feasible?

推荐答案

您使用的语法最常与 SQL Server 相关联.MySQL 使用(更常见的)create table as 语法.并且，它专门允许在语法中使用临时表.

The syntax that you are using is most commonly associated with SQL Server. MySQL uses the (more common) create table as syntax. And, it allows specifically for temporary tables in the syntax.

所以，MySQL 中的等价物是 创建表:

So, the equivalent in MySQL is CREATE TABLE AS:

CREATE TEMPORARY TABLE QA AS
    SELECT p.*  
    FROM wp_posts p
    WHERE post_type IN ('answer', 'question');

请注意，临时表是一种非常特殊的表类型，仅存在于当前会话"中.-- 比如说，您当前与数据库的连接.它对其他用户不可见，当您重新连接到数据库时它会消失.

Note that a temporary tables is a very specific type of table that exists only in the current "session" -- say, your current connection to the database. It is not visible to other users and it will disappear when you reconnect to the database.

这篇关于根据表中的值查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！