使用UNION ALL SQL Query获取更多值

I am using this query to get results from two tables:

我正在使用此查询从两个表中获取结果:

SELECT * FROM (
    SELECT parent_id as mID, count(*) as cnt
        FROM wp_forum_posts
        WHERE text LIKE '%{$word}%'
        GROUP by 1
        UNION ALL
            SELECT id, count(*)
            FROM wp_forum_threads
            WHERE subject LIKE '%{$word}%'
            GROUP by 1) x
ORDER BY 2, 1

I want to select some more values from wp_forum_threads. Values like subject. How can I do this? Simply adding behind id does not work. the query returns no result, then.

我想从wp_forum_threads中选择更多值。主题等价值观。我怎样才能做到这一点?简单地添加后面的id不起作用。然后,查询不返回任何结果。

1 个解决方案

#1

The number of columns in the the select on both parts of the UNION ALL should be the same. That means that if for example you want to add "subject" to the 2nd part of the query, you'll need to add a "place holder" in the 1st part of the query is well:

UNION ALL两个部分的select中的列数应该相同。这意味着,如果您想要将“subject”添加到查询的第2部分,则需要在查询的第1部分添加“占位符”:

    SELECT * FROM (
SELECT parent_id as mID, NULL, count(*) as cnt
    FROM wp_forum_posts
    WHERE text LIKE '%{$word}%'
    GROUP by 1
    UNION ALL
        SELECT id, subject, count(*)
        FROM wp_forum_threads
        WHERE subject LIKE '%{$word}%'
        GROUP by 1) x
ORDER BY 2, 1

this should work.

这应该工作。