MySQL 不同列的MySql SELECT联合？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7407864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql SELECT union for different columns?
提问by Steven
I have a select query that selects files with a thumbnail file attached and I also need to get those with no thumbnail attached.
我有一个选择查询，它选择带有缩略图文件的文件，我还需要获取那些没有附加缩略图的文件。
My current sql query is
我当前的 sql 查询是
SELECT node.title, node.nid, files.fid, files.filepath,
content_type_mobile_event.field_date_value,
content_type_mobile_event.field_movie_shorts_value,
content_type_mobile_event.field_director_value,
content_type_mobile_event.field_length_value,
content_type_mobile_event.field_movie_type_value,
content_type_mobile_event.field_year_value,
content_type_mobile_event.field_event_type_value,
content_type_mobile_event.field_movie_location_value,
content_type_mobile_event.field_movie_desc_value,
content_type_mobile_event.field_movie_id_value,
content_type_mobile_event.field_movie_thumb_fid,
content_type_mobile_event.field_movie_trailer_url
FROM node, content_type_mobile_event, files
WHERE node.nid=content_type_mobile_event.nid AND
content_type_mobile_event.field_movie_thumb_fid=files.fid
ORDER BY content_type_mobile_event.field_date_value ASC
I need to also get
我还需要得到
SELECT node.title, node.nid, content_type_mobile_event.field_date_value,
content_type_mobile_event.field_movie_shorts_value,
content_type_mobile_event.field_director_value,
content_type_mobile_event.field_length_value,
content_type_mobile_event.field_movie_type_value,
content_type_mobile_event.field_year_value,
content_type_mobile_event.field_event_type_value,
content_type_mobile_event.field_movie_location_value,
content_type_mobile_event.field_movie_desc_value,
content_type_mobile_event.field_movie_id_value,
content_type_mobile_event.field_movie_thumb_fid,
content_type_mobile_event.field_movie_trailer_url
FROM node, content_type_mobile_event
WHERE node.nid=content_type_mobile_event.nid AND
content_type_mobile_event.field_movie_thumb_fid!=1
ORDER BY content_type_mobile_event.field_date_value ASC
I would normally just do a
我通常只会做一个
(
SELECT node.title, node.nid, files.fid, files.filepath,
content_type_mobile_event.field_date_value,
content_type_mobile_event.field_movie_shorts_value,
content_type_mobile_event.field_director_value,
content_type_mobile_event.field_length_value,
content_type_mobile_event.field_movie_type_value,
content_type_mobile_event.field_year_value,
content_type_mobile_event.field_event_type_value,
content_type_mobile_event.field_movie_location_value,
content_type_mobile_event.field_movie_desc_value,
content_type_mobile_event.field_movie_id_value,
content_type_mobile_event.field_movie_thumb_fid,
content_type_mobile_event.field_movie_trailer_url
FROM node, content_type_mobile_event, files
WHERE node.nid=content_type_mobile_event.nid AND
content_type_mobile_event.field_movie_thumb_fid=files.fid
ORDER BY content_type_mobile_event.field_date_value ASC
)
UNION
(
SELECT node.title, node.nid, content_type_mobile_event.field_date_value,
content_type_mobile_event.field_movie_shorts_value,
content_type_mobile_event.field_director_value,
content_type_mobile_event.field_length_value,
content_type_mobile_event.field_movie_type_value,
content_type_mobile_event.field_year_value,
content_type_mobile_event.field_event_type_value,
content_type_mobile_event.field_movie_location_value,
content_type_mobile_event.field_movie_desc_value,
content_type_mobile_event.field_movie_id_value,
content_type_mobile_event.field_movie_thumb_fid,
content_type_mobile_event.field_movie_trailer_url
FROM node, content_type_mobile_event
WHERE node.nid=content_type_mobile_event.nid AND
content_type_mobile_event.field_movie_thumb_fid!=1
ORDER BY content_type_mobile_event.field_date_value ASC
)
But the problem is the second one has a different set of columns (minus the files.* part)
但问题是第二个有一组不同的列（减去 files.* 部分）
I cannot for the life of me figure out how to do this.
我一生都无法弄清楚如何做到这一点。
回答by GolezTrol
If you have different fields that have different meaning too, you can't and shouldn't return them in the same position. You can however 'fill in the blanks' by adding null to your fields, like this:
如果您有不同含义的不同字段，则不能也不应该将它们返回到同一位置。但是，您可以通过在字段中添加 null 来“填空”，如下所示：
select id, name, date, null as userid, 'A' as recordtype from table1
union all
select id, name, null /*as date*/, userid, 'B' as recordtype from table2
You can provide an alias for the null in the first select. You can add aliases in the second select for clarity, but it won't be used. You can even use constant values which you can use to distinguish the record type later.
您可以在第一个选择中为空值提供别名。为清楚起见，您可以在第二个选择中添加别名，但不会使用它。您甚至可以使用常量值，以后可以使用它来区分记录类型。
回答by dispake
If select A does not have a column, simply use a null value
如果选择 A 没有列，只需使用空值
table A (colA, ColB, ColC)
table B (colA, ColD, ColE)
select colA, ColB, ColC, null, null from table A
union
select colA, null, null, colD, colE from table B
just need to be sure that each column you are matching are of the same data type
只需要确保您匹配的每一列都是相同的数据类型
回答by Bala R
You can fakea column to do union
你可以伪造一个列来做联合
eg.
例如。
select x, y from A
union
select z, null from B
