插入使用 Oracle 的另一个表中的表值

标签: sql Oracle
发布时间: 2017/4/15 18:21:29
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有两张桌子,

表 1包含了以下字段,

u_id    id      no
12      51      1
21      51      2
31      51      3
41      51      4
51      51      5
61      51      6
72      51      7
81      51      8
91      51      9
92      51      10

表 2包含了以下字段,

id      one     two     three   four    five    six     seven   eight   nine    ten

51      12      21      31      41      51      61      72      81      91      92

我需要检查一下没有。和从表 1,插入到表 2 相应 u_id id。

为 eg。如果 id = 51 和不是 1,那么我不得不将 u id 值插入到第一列在表 2,

和 id = 51 和 no = 2 然后插入第二列,等等。请帮助。我使用 Oracle。

解决方法 1:

id 必须唯一

select id,
sum(u_id*(if no = 1 then 1 else 0 endif)) as one,
sum(u_id*(if no = 2 then 1 else 0 endif)) as two,
sum(u_id*(if no = 3 then 1 else 0 endif)) as three,
sum(u_id*(if no = 4 then 1 else 0 endif)) as four,
sum(u_id*(if no = 5 then 1 else 0 endif)) as five,
sum(u_id*(if no = 6 then 1 else 0 endif)) as six,
sum(u_id*(if no = 7 then 1 else 0 endif)) as seven,
sum(u_id*(if no = 8 then 1 else 0 endif)) as eight,
sum(u_id*(if no = 9 then 1 else 0 endif)) as nine,
sum(u_id*(if no = 10 then 1 else 0 endif)) as ten
from table_1 group by id;
赞助商