从两个表的联合将数据插入到新表中 [英] inserting data into new table from union of two tables

问题描述

我有一个以下查询，结合两个表Table1和表2

I have a following query that unions two tables Table1 and Table 2

ResultSet res = st.executeQuery(" select user_id, movie_Id, movie_name, user_name, rating, genre from Table1 union all select t1.user_id, t2.movie_Id, t2.movie_name, t2.user_name,  t2.rating, t2.genre from Table2 t2 join Table1 t1 on t2.user_name = t1.user_name;");

输出 -

1   12  pianist                 vishal  7   action
2   4   titanic                 rajesh  7       action
3   5   snakes on a plane   anuj    2   drama
4   9   oh my god           arun    5   drama
5   9   jumanji                 vishal  8   fantasy
6   68  the rabbit hole         vishal  4   mystery
1   249 fast and furious    vishal  0   action
2   356 sun and horse           rajesh  0   fantasy
2   423 scream                  rajesh  0   comedy
2   391 alone                   rajesh  0   tragedy
2   739 price and pauper    rajesh  0   drama
4   451 seven                   arun    5   comedy
5   9   ghosts                  vishal  0   horror
5   216 face off            vishal  0   comedy
5   344 future                  vishal  0   drama
5   387 night and day           vishal  0   suspense
5   249 fast and furious    vishal  0   action
6   9   ghosts                  vishal  0   horror
6   216 face off            vishal  0   comedy
6   344 future                  vishal  0   drama
6   387 night and day           vishal  0   suspense
6   249 fast and furious    vishal  0   action

现在，我想要插入从表格3传递的值，但只有movie_Id，user_Id和rating

Now i want that I should insert values comming from this to a Table 3 but only movie_Id,user_Id and rating

ie
movie_id int
user_Id int
rating varchar

ie movie_id int user_Id int rating varchar

ie

user_Id movie_Id    rating
1   12      7   
2   4       7   
3   5       2   
4   9       5   
5   9       8   
6   68      4   
1   249     0   
2   356     0   
2   423     0   
2   391     0   
2   739     0   
4   451     5   
5   9       0   
5   216     0   
5   344     0   
5   387     0   
5   249     0   
6   9       0   
6   216     0   
6   344     0   
6   387     0   
6   249     0   

如何编写以下查询？

感谢

推荐答案

使用 INSERT INTO ... SELECT ... ：

INSERT INTO table3(movie_id, user_id, rating)
SELECT movie_id, user_id, rating
FROM
(
    SELECT movie_Id, user_id, rating
    FROM Table1 
    UNION ALL
    SELECT t2.movie_id, t1.user_id, t2.rating
    FROM Table2 t2 
    JOIN Table1 t1 ON t2.user_name = t1.user_name
) AS t;

这篇关于从两个表的联合将数据插入到新表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！