SQL 列不同的表查询结果合并操作

两个不同的表进行查询，需要把结果合并，

比如table1的列为 id, user_id, type_id,pro_id；

table2的列为 id,user_id,collect_id；分别如下图所示

table1：

table2：

将两个表的查询结果合并到一起的查询语句为

?

1 2 3

select *, null as collect_id from table1 where user_id = 527 union select id,user_id, null as type_id, null as pro_id, collect_id from table2 where user_id = 527;

结果为：

其实就是把对应的列补充到没有该列的表中，在例子中就是把collect_id补充到table1中,

把type_id,pro_id补充到table2中。

补充知识：sql结果集合并用union all 不同表的列合并用join

结果集合并用union all 不同表的列合并用join

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SELECT "模块名" , "事件编码" , "点击数量" , "使用时长（单位：分）" FROM   ( SELECT T.fun_name as "模块名" , T.event_code as "事件编码" , SUM (click_records) as "点击数量" FROM ( SELECT m.* FROM default .daily_new_clientrpt_master m WHERE event_id in ( SELECT max (event_id) AS "事件" from default .daily_new_clientrpt_master group by user_name,fun_code ORDER BY "事件" DESC ) ) T where day = today() GROUP BY "模块名" , "事件编码" ) T5 JOIN ( SELECT T.fun_name as "模块名" , T.event_code as "事件编码" , round( sum (stay_time)/60000,0) as "使用时长（单位：分）" FROM ( SELECT m.* FROM default .daily_new_clientrpt_master m WHERE event_id in   (   SELECT "事件" FROM (   SELECT max (event_id) AS "事件" , max (stay_time) AS "事件1" from default .daily_new_clientrpt_master group by user_name,fun_code ORDER BY "事件1" DESC ) ) )   T where day = today() AND like (event_code, '%10000' ) GROUP BY "模块名" , "事件编码" ) T6 ON T5. "模块名" =T6. "模块名" AND T5. "事件编码" =T6. "事件编码"

以上这篇SQL 列不同的表查询结果合并操作就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/u010498402/article/details/78038553