将同一表上的多个MySQL查询合并为一个

How do you make multiple queries on the same table, selecting different columns?

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

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.

如果有帮助的话……在SQL语句的select部分中，所有查询都有一个公共列。它们都选择ID，然后后面跟着一些特定的内容。

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

因此，每个查询都需要ID，并且需要以下任何一个:post_name、post_title或post_摘录。

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

如果它有助于简化事情。我试图在这些列中寻找广泛的匹配和精确的匹配。

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.

因此，在我的示例中，我将在以下列中查找:“floor finish”、“floor”或“finish”:post_name、post_title和post_摘录。都在同一个表格里。

I've attempted to accomplish this with UNION.

我试着和工会一起完成这件事。

Here are my queries:

这是我的查询:

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%' )
)

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.

然而，上面的结果很有趣。我似乎得到了所有正确的结果，除了每个结果值的键(应该是列的名称)始终保持不变。它总是post_name，即使分配给它的值可能是post_title或post_摘录。

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

所以每个结果都有一个ID和post_name。基本上它们的键是错误的，但是值看起来是正确的。

I also tried something like this:

我也尝试过这样的方法:

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%' ))
        )

)

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.

这更符合我的目标。我希望每个结果都有ID、post_摘录、post_slug和post_title。如果没有匹配，显示它们的键值为空，或者完全不显示键值。

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.

第二次尝试的问题是，它只需要在需要的三个列之一中进行匹配。因此，如果它在post_摘录中匹配，而没有在其他地方匹配，它仍将从post_title和post_name中提取值。从而使结果不准确。

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.

我读过一些看似相似的问题，但大多数都没有真正明确的答案……还是……问题/答案更适合于单独的表上的多个查询。

Any guidance or advice on combining multiple MySQL queries on the same table?

对于在同一个表上组合多个MySQL查询有什么指导或建议吗?

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

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

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.

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

2 个解决方案

#1

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:

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

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"

would result in:

将导致:

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

which is what you are experiencing.

这就是你正在经历的。

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

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

这对你要做的事情可能更有用。这是一个SQLFiddle，如果你想看到它在行动。

#2

As I understood in second case the problem is that only matched column should have value and others should be null. For this case you can use IF/ELSE or WHEN statement in SELECT. The idea is you select actual value is value of this column corresponds to expected value and null otherwise.

正如我在第二种情况中所理解的，问题是只有匹配的列应该具有值，而其他列应该为null。对于这种情况，您可以在SELECT中使用IF/ELSE或WHEN语句。其思想是你选择实际值为本列的值对应于期望值，否则为空。

Another potential solution is you can write e.g.

另一种可能的解决办法是你可以写。

SELECT `ID`, `post_name`, `post_title`, 'post_excerpt'

So you select string value that were criteria in WHERE statement and can use this value later to understand what this row belongs to.

因此，您可以选择WHERE语句中作为条件的字符串值，然后可以使用这个值来理解这一行属于什么。