如何在MySQL中使用UNION时知道结果来自哪个表 [英] How to know what table a result came from when using UNION in MySQL

问题描述

我正在使用联合查询:

SELECT snippet_id, title FROM tbl_snippets WHERE title LIKE ?
UNION
SELECT tag_id, tag FROM tbl_tags WHERE tag LIKE ?
UNION
SELECT category_id, category FROM tbl_categories WHERE category LIKE ?

我怎么知道结果来自哪个表?snippet_id，tag_id和category_id没有像SN，TG或CI这样的前缀，可以用来确定结果的来源. 结果基本上汇总成一个结果，所以我真的不知道我在想什么，这就是为什么我来这里.

How will I know which table a result came from?snippet_id, tag_id and category_id doesn't have a prefix like SN, TG, or CI that I could use to determine where a result came from. The results are basically put together in one result so I don't really know if what I'm thinking is possible that's why I came asking here.

推荐答案

为什么不将前缀添加为单独的(计算出的)列?

Why not adding the prefix as a separate (computed) column?

SELECT 'SN' prefix, snippet_id, title FROM tbl_snippets WHERE title LIKE ?
UNION ALL
SELECT 'TA', tag_id, tag FROM tbl_tags WHERE tag LIKE ?
UNION ALL
SELECT 'CA', category_id, category FROM tbl_categories WHERE category LIKE ?

编辑:出于以下原因，我也将UNION [DISTINCT]更改为UNION ALL-

I have also changed UNION [DISTINCT] to UNION ALL - for the following reasons:

• 如果原始查询对UNION和UNION ALL产生不同的结果，则引入前缀将更改结果行的数量.
• 在大多数情况下，UNION ALL比UNION DISTINCT略快.
• 大多数人实际上都想拥有UNION ALL.

• If the original query produces different results for UNION and UNION ALL, introducing the prefix will change the number of result rows.
• In most cases UNION ALL is slightly faster than UNION DISTINCT.
• Most people actually want to have UNION ALL.

这篇关于如何在MySQL中使用UNION时知道结果来自哪个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！