如何从两个表中求和列值并保存仅在一个表中的另一个列

i have created tables item and item2, I know maybe it's data redundancy but i want to know how can select it, and create a view?

我已经创建了表项和item2，我知道这可能是数据冗余但我想知道如何选择它，并创建视图?

create table item(
id  number(10) primary key,
name varchar2(20),
mark number(10));
insert into item values(10,'Apple1',23);
insert into item values(11,'Apple2',0);
insert into item values(12,'Apple3',0);
insert into item values(13,'Apple4',0);
insert into item values(14,'Apple4',0);
insert into item values(15,'Apple4',0);
insert into item values(16,'Apple4',0);

create table  item2(
id number(10),
mark number(10));
alter table item2 add(constraint id_fk FOREIGN KEY (id) references  item(id));
Insert into  item2 values(10,1);
Insert into  item2 values(10,1);
Insert into  item2 values(11,7);
Insert into  item2 values(12,14);

I can query both:

我可以查询:

select * from item;

ID  Name    Mark
10  Apple1    23
11  Apple2     0
12  Apple3     0
13  Apple4     0
14  Apple4     0
15  Apple4     0
16  Apple4     0

select * from item2;

ID  Mark
10     1
10     1
11     7
12    14

I want to get the result set below using the select statement sum from the item and item2 tables:

我想使用item和item2表格中的select语句和来获得下面的结果集:

ID  Name    Mark
10  Apple1    25
11  Apple2     7
12  Apple3    14
13  Apple4     0
14  Apple4     0
15  Apple4     0
16  Apple4     0

How can I combine my queries to produce that output?

如何组合查询以生成输出?

3 个解决方案

#1

If I understand this correctly, you want to "pretend" that the second table had the NAME column also, populated according to the first table; then you would want to GROUP BY id and get the sum of MARK.

如果我理解正确，您想要“假装”第二个表也有NAME列，根据第一个表填充;然后你要按id分组并得到标记的和。

If so, instead of joining the tables to get the names (either before or after combining the tables and computing the sums), you can use a UNION ALL, in which you insert a fake NAME column with NULL in it for the second table; then you group by id, you sum the MARK column, and you take the MAX over NAME. MAX ignores NULL, so it will just pick the name from table ITEM.

如果是这样的话，您可以使用一个UNION ALL，在其中插入一个假的NAME列，并在第二个表中插入一个空值，而不是连接表来获取名称(在合并表和计算总数之前或之后)。然后按id分组，对标记列求和，取最大值/名。MAX忽略了NULL，所以它只会从表项中选择名称。

The solution below follows that logic in every detail.

下面的解决方案在每个细节中都遵循这种逻辑。

select   id, max(name) as name, sum(mark) as mark
from     ( select id, name, mark
             from item
           union all
           select id, null as name, mark
             from item2
         )
group by id
;

#2

You can union the two tables together on the id and mark. The name you can either add a null name column into the union and do a max/min on that field to get one value from that table. Otherwise you can union the id and marks, and then join back to the original table with the name to grab it from there and include it in the group by.

你可以把这两张桌子连在一起。您可以将空名称列添加到联合中，并在该字段上执行max/min，以从该表中获得一个值。否则，您可以将id和标记合并到具有名称的原始表中，从该表中获取id并将其包含到group by中。

select item_table.id, item_table.name, sum(mark_data_set.mark) as mark_score
from
(select 
    id, mark 
from item
union all
select id, mark
from item2
) mark_data_set
inner join item item_table on (mark_data_set.id = item_table.id)
group by item_table.id, item_table.name

#3

How about this?

这个怎么样?

select  id,name,(m1 +nvl(m2,0)) mark
from
(select t1.id,t1.name,t1.mark m1,t2.mark m2
from
item t1 
LEFT OUTER JOIN
(select id,sum(mark) mark from item2
group by id) t2
ON
t2.id = t1.id
)
order by id;

#1