SQL 列不同的表查询结果合并操作
16lz
2021-12-16
两个不同的表进行查询,需要把结果合并,
比如table1的列为 id, user_id, type_id,pro_id;
table2的列为 id,user_id,collect_id;分别如下图所示
table1:
table2:
将两个表的查询结果合并到一起的查询语句为
select *, null as collect_id from table1 where user_id = 527unionselect 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
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 "模块名" ,"事件编码") T5JOIN(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."事件编码"
更多相关文章
- Android中不同应用间实现SharedPreferences数据共享
- android 中使用TextView实现分段显示不同颜色的字符串
- android使用同一个RecyclerView实现两种不同Item布局
- 【Android(安卓)内存优化】Bitmap 内存缓存 ( Bitmap 内存复用 |
- LinearLayout按下(pressed)或获取焦点(focused)时背景设置不同颜
- LinearLayout按下(pressed)或获取焦点(focused)时背景设置不同颜
- android 中theme.xml与style.xml的区别
- 浅析Android如何从不同的层次保证安全性
- 【如何在不同Android设备中得到有效的AudioRecord实例】