php - MySQL 返回按组分隔的联合选择结果

Question

我想知道是否可以返回按别名分组的 UNION 结果。例如:

(SELECT * FROM table1) AS first
UNION
(SELECT * FROM table2) AS second

所以结果是:

first = contains all table1 rows
second = contains all table2 rows

实际上我想要一个像这样的关联数组:

[]=>[
    'first'=>[table1 results],
    'second'=>[table2 results]
]

我试过了，但是没用。也许我做得不好。这可以通过单个查询来完成吗？或者我必须执行两个单独的查询。谢谢。

Answer 1

您不能使用 union 执行此操作，因为它会删除重复项。您可以使用union all。

一种方法是:

SELECT t.*, 0 as which FROM table1 t
UNION ALL
SELECT t.*, 1 FROM table2 t
ORDER BY which;

如果您不想在输出中看到which，请使用子查询:

select . . .
from (select t.*, 0 as which from table1 t union all
      select t.*, 1 as which from table1 t
     ) t
order by which;