SQL merge operation of query results of tables with different columns

To query two different tables, you need to merge the results.

For example, the columns of table1 are id, user_id, type_id, pro_id;

The columns of table2 are id, user_id, collect_id; as shown in the following figure respectively

table1:

table2:

The query statement that merges the query results of the two tables is

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;

The result is:

In fact, it is to add the corresponding column to the table that does not have this column. In the example, collect_id is added to table1.

Add type_id and pro_id to table2.

Additional knowledge: SQL result set and use union all to combine columns from different tables with join

The result set is combined with union all and columns from different tables are combined with join

SELECT
"Module Name",
"Event Code",
"Number of clicks",
"Usage time (unit: minutes)"
FROM

(SELECT 
T.fun_name as "module name",
T.event_code as "Event Code",
SUM(click_records) as "Number of clicks"
FROM 
(SELECT m.* FROM default.daily_new_clientrpt_master m WHERE event_id in ( SELECT max(event_id) AS "event" from default.daily_new_clientrpt_master group by user_name,fun_code ORDER BY "event" DESC ) ) T where day = today() GROUP BY "module name" ,"event code") T5
JOIN
(
SELECT 
T.fun_name as "module name",
T.event_code as "Event Code",
round(sum(stay_time)/60000,0) as "Usage time (unit: minutes)"
FROM 
(SELECT m.* FROM default.daily_new_clientrpt_master m WHERE event_id in 
 ( 
 SELECT "event" FROM (
 SELECT max(event_id) AS "event", max(stay_time) AS "event1" from default.daily_new_clientrpt_master group by user_name,fun_code ORDER BY "event1" DESC) )
) 
 T where day = today() AND like(event_code,'%10000') GROUP BY "module name" ,"event code"
) T6 ON T5."Module name"=T6."Module name" AND T5."Event code"=T6."Event code"

The above SQL merge operation of query results of different tables is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

Summary of four situations of joint query between two tables in Mysql
Solve the problem of SQL Server saving object string conversion into uniqueidentifier failure
SQL server auto-increment ID--field operation of automatically increasing serial number
Detailed explanation of identity usage in SQL Server
MySQL 5.7.31 64-bit free installation version tutorial diagram
SqlServer generates consecutive numbers according to the specified number operation

<<: Analyze the difference between computed and watch in Vue

>>: Three ways to communicate between Docker containers