如何在MySQL中连接整个结果集? [英] How do I Concatenate entire result sets in MySQL?

问题描述

我正在尝试以下查询:

SELECT A,B,C FROM table WHERE field LIKE 'query%'
UNION
SELECT A,B,C FROM table WHERE field LIKE '%query'
UNION
SELECT A,B,C FROM table WHERE field LIKE '%query%'
GROUP BY B ORDER BY B ASC LIMIT 5

这是三个查询卡在一起，有点.但是，返回的结果集在查询#1的结果之前反映了查询#3的结果，这是不希望的.

That's three queries stuck together, kinda sorta. However, the result set that comes back reflects results from query #3 before the results from query #1 which is undesired.

是否有任何方法可以对它们进行优先级排序，以使查询#1的所有结果，查询#2的所有结果，查询#3的所有结果都一样?我现在还不想在PHP中执行此操作(更不用说必须控制在第一个查询中显示的结果而不在第二个查询中显示等等).

Is there any way to prioritize these so that results come as all for query #1, then all for query #2 then all for query #3? I don't want to do this in PHP just yet (not to mention having to control for results that showed up in the first query not to show in the second and so forth).

推荐答案

也许您应该尝试添加第四列，说明它来自的表，然后对其进行排序和分组:

Maybe you should try including a fourth column, stating the table it came from, and then order and group by it:

SELECT A,B,C, "query 1" as origin FROM table WHERE field LIKE 'query%'
UNION
SELECT A,B,C, "query 2" as origin FROM table WHERE field LIKE '%query'
UNION
SELECT A,B,C, "query 3" as origin FROM table WHERE field LIKE '%query%'
GROUP BY origin, B ORDER BY origin, B ASC LIMIT 5

这篇关于如何在MySQL中连接整个结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！