Mysql的SELECT联合为不同的列? [英] MySql SELECT union for different columns?

问题描述

我有一个选择查询，用于选择附加了缩略图文件的文件，我还需要获取未附加缩略图的文件.

I have a select query that selects files with a thumbnail file attached and I also need to get those with no thumbnail attached.

我当前的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

我还需要得到

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

我通常只会做一个

(
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)

我一生都无法弄清楚该怎么做.

I cannot for the life of me figure out how to do this.

推荐答案

如果您也有不同的字段，这些字段也具有不同的含义，则不能也不应将它们返回到相同的位置.不过，您可以通过在字段中添加null来填空"，如下所示:

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:

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 

您可以在第一个选择中为null提供别名.为了清楚起见，您可以在第二个选择中添加别名，但是不会使用.您甚至可以使用常量值，以后可以用来区分记录类型.

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.

这篇关于Mysql的SELECT联合为不同的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！