合并多个MySQL查询在同一个表进一(Combine Multiple MySQL Queries

如何做才能让你在同一个表的多个查询，选择不同的列？

如果它有助于在所有...所有查询必须在SQL语句的选择部分公共列。他们都选择ID ，然后其次是一些具体的事情。

所以每次查询需要的ID和下面的任一： post_name ， post_title ，或post_excerpt 。

此外，如果它有助于把事情简单化。我试图寻找广泛的比赛和这些列完全匹配。

所以在我的例子中，我将寻找“地板漆”，“地板”或“完成”，在以下几列： post_name ， post_title和post_excerpt 。所有在同一个表。

我已经尝试与完成这个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_name即使分配给它的值可能是一个post_title或post_excerpt 。

所以每个结果都有一个ID和post_name 。他们基本上键是错误的，但值显示是准确的。

我也尝试过这样的事情：

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 。如果没有匹配，显示它们与关键空值，或只是完全甚至不显示的关键。

与第二次尝试的问题是，它只是需要在三个期望的一列的比赛。因此，如果在匹配post_excerpt ，没有别的地方，它仍然会拉离值post_title和post_name 。从而使结果不准确。

我读过一些什么似乎是类似的问题但大多数没有真正坚实明确的答案......或者......这些问题/答案更适合于进行多次查询在不同的表。

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

顺便说一句......我现在用的是“组合拳”在这两个我的例子是我最终的查询发送到数据库。

所以只是一个更多的时间......如果有一列不匹配，显示他们的密钥空或只是完全忽略从完全的结果的关键。

Answer 1:

你得到，因为错误的“钥匙” UNION在你的查询语句。你有三种不同的列的联合在一起查询之间不同的键名，但兼容的列类型。而不是抛出一个错误，数据库引擎刚刚从第一个查询选择列名和使用，对所有的人：

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"

这是你正在经历。

相反，你可以这样做：

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"

这将使你的结果，如：

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

随着你的表，这个一个非常基本的版本可能是这样的：

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 ，如果你想看到它在行动。

Answer 2:

正如我在第二种情况下理解的问题是，只有匹配列应有的价值和其他应为空。对于这种情况，你可以使用IF / ELSE或当语句SELECT。我们的想法是你选择的实际值是此列的值对应于预期值;否则返回null。

另一种可能的解决方案是，你可以写如

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

所以，你选择了在WHERE语句标准，以后可以使用这个值来了解此行所属的字符串值。