将同一表上的多个MySQL查询合并为一个 [英] Combine Multiple MySQL Queries on the Same Table Into One

问题描述

如何在同一个表上进行多个查询，选择不同的列？

如果有帮助...全部这些查询在SQL语句的select部分中具有一个公共列。他们都选择 ID ，然后选择特定的内容。

If it helps at all... All of the queries have a common column in the select part of the SQL statement. They all select the ID, then followed by something specific.

所以每个查询都需要 ID 和以下任意一项： post_name ， post_title 或 post_excerpt 。

So every query needs the ID and either of the following: post_name, post_title, or post_excerpt.

也可以帮助简化操作。我正在尝试在这些列中搜索广泛匹配和完全匹配。

Also if it helps to simplify things. I'm trying to search for broad matches and exact matches in these columns.

因此，在我的示例中，我将寻找：地板完成，地板 ，或在以下列中完成： post_name ， post_title 和 post_excerpt 。

So in my example, I will be looking for: "floor finish", "floor", or "finish" in the following columns: post_name, post_title, and post_excerpt. All within the same table.

我试图用 UNION 来实现。

这是我的查询：

Array
(
    [broad] => Array
        (
            [floor] => Array
                (
                    [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor%'
                    [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor%'
                    [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor%'
                )

            [finish] => Array
                (
                    [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%finish%'
                    [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%finish%'
                    [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%finish%'
                )

        )

    [exact] => Array
        (
            [slugs] => SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor-finish%'
            [titles] => SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor finish%'
            [excerpts] => SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor finish%'
        )

    [combined] => ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor-finish%' ) UNION ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_name` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_name` LIKE '%finish%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor finish%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_title` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_title` LIKE '%finish%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor finish%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%floor%' ) UNION ( SELECT `ID`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND `post_excerpt` LIKE '%finish%' )
)

但是，以上结果很有趣。我似乎得到了所有正确的结果，但每个结果值的键（应该是列的名称）始终保持不变。即使分配给它的值可能是 post_title 或 post_excerpt post_name code>。

However, the above result is interesting. I appear to get all the correct results except the key of each result value (which is supposed to be the name of the column) always remains the same. It's always post_name even though the value assigned to it might be a post_title or post_excerpt.

因此，每个结果都有一个 ID 和 post_name 。基本上，它们的键是错误的，但是值似乎是准确的。

So every result has an ID and post_name. Basically they keys are wrong but the values appear to be accurate.

我也尝试过类似的操作：

Array
        (
            [broad] => Array
                (
                    [floor] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor%' OR `post_title` LIKE '%floor%' OR `post_excerpt` LIKE '%floor' )
                    [finish] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%finish%' OR `post_title` LIKE '%finish%' OR `post_excerpt` LIKE '%finish%' )
                )

            [exact] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor-finish%' OR `post_title` LIKE '%floor finish%' OR `post_excerpt` LIKE '%floor finish%' )
            [combined] => SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor-finish%' OR `post_title` LIKE '%floor finish%' OR `post_excerpt` LIKE '%floor finish%' ) UNION (SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%floor%' OR `post_title` LIKE '%floor%' OR `post_excerpt` LIKE '%floor%' )) UNION (SELECT `ID`, `post_name`, `post_title`, `post_excerpt` FROM tps_3_posts WHERE `post_status` = 'publish' AND ( `post_name` LIKE '%finish%' OR `post_title` LIKE '%finish%' OR `post_excerpt` LIKE '%finish%' ))
        )

)

这更符合我要完成的工作。我希望每个结果都具有 ID ， post_excerpt ， post_slug 和 post_title 。如果没有匹配项，则将其键显示为空值，或者甚至完全不显示键。

This is more along the lines of what I'm trying to accomplish. I would like each result to have ID, post_excerpt, post_slug, and post_title. If there's no match, display they key with an empty value, or just completely don't even display the key.

第二次尝试的问题是它只需要三个所需列之一中的匹配项。因此，如果它在 post_excerpt 中匹配，并且没有其他地方匹配，它将仍然从 post_title 和<$ c中提取值$ c> post_name 。因此，使结果不准确。

The problem with the second attempt is that it's only requiring a match in one of the three desired columns. So if it matched in the post_excerpt and no where else, it will still pull values from the post_title and post_name. Thus making the results inaccurate.

我已经阅读了几个看似相似的问题，但是大多数问题并没有真正明确的答案……或者……问题/答案更适合于SEPARATE表上的多个查询。

I've read several what appear to be similar questions however most don't have real solid clear answers... OR ... The questions/answers are more geared to multiple queries on SEPARATE tables.

在同一表上合并多个MySQL查询的任何指导或建议吗？

顺便说一下...我在两个示例中都使用组合作为我的最终查询，以发送到数据库。

BY THE WAY... I am using the "combined" in both my examples as my final query to send to the database.

再说一次...如果一列中没有匹配项，则将它们的键显示为null或完全从结果中完全省略键。

So just one more time... If there's no match in a column, display they key as null or just completely omit the key from the results entirely.

推荐答案

由于查询中的 UNION 语句，您获得了错误的键。您具有不同的键名，但在联合查询的三个不同列之间具有兼容的列类型。数据库引擎没有抛出错误，而是从第一个查询中选择列名并将其用于所有列名：

You're getting the wrong "key" because of the UNION statement in your query. You have different key names, but compatible column types between the three different columns your unioned queries. Rather than throw an error, the database engine is just picking the column name from the first query and using that for all of them:

id | post_name # <= column name in first query
1  | "my post"

UNION

id | post_title # <= column name is different, but type is compatible, so UNION succeeds
1  | "my post title"

UNION

id | post_excerpt # <= ditto
1  | "my post excerpt"

将导致：

id | post_name # <= column name from first query
1  | "my post"
1  | "my post title"
1  | "my post excerpt"

这就是您所遇到的。

相反，您可以执行以下操作：

Instead, you could do something like:

id | post_name | post_title | post_excerpt
1  | "my post" | null       | null        # <= deliberately select nulls for these columns in this query

UNION

id | post_name | post_title      | post_excerpt
1  | null      | "my post title" | null

UNION

id | post_name | post_title | post_excerpt
1  | null      | null       | "my post excerpt"

哪个会给您类似的结果：

Which would give you results like:

id | post_name | post_title      | post_excerpt
1  | "my post" | null            | null
1  | null      | "my post title" | null
1  | null      | null            | "my post excerpt"

在您的表格中，一个非常基本的版本可能如下：

With your table, a very basic version of this might look like:

SELECT ID, post_name, null AS post_title, null AS post_excerpt FROM tps_3_posts

UNION

SELECT ID, null AS post_name, post_title, null AS post_excerpt FROM tps_3_posts

UNION

SELECT ID, null AS post_name, null AS post_title, post_excerpt FROM tps_3_posts

对于您尝试执行的操作可能更有用。 如果您想查看它的运行，请使用SQLFiddle 。

which might be more usable for what you are trying to do. Here's a SQLFiddle if you want to see it in action.

这篇关于将同一表上的多个MySQL查询合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！