sql 查询多张表的分数总和

Question

请大家帮忙了，本人分数不是很多，希望能帮助一下表名为Mainboard,name列，Bubject列，price列
张三 主板 10
李四 主板 7
王五 主板 6
王五 主板 5表名为EMS，name列，Bubject列，price列张三 内存 10
李四 内存 7
王五 内存 5
张三 内存 10我要查询效果分别是按照消费情况排名。
name price number
张三 30 1
王五 16 2
李四 14 3

Answer 1

Oracle中有rank函数，可以这样写：SQL> create table zz_ch_mainboard (name varchar2(8),subject varchar2(16),price number);

Table created
SQL> insert into zz_ch_mainboard values ('张三','主板',10);

1 row inserted
SQL> insert into zz_ch_mainboard values ('李四','主板',7);

1 row inserted
SQL> insert into zz_ch_mainboard values ('王五','主板',6);

1 row inserted
SQL> insert into zz_ch_mainboard values ('王五','主板',5);

1 row inserted
SQL> commit;

Commit complete
SQL> create table zz_ch_ems (name varchar2(8),subject varchar2(16),price number);

Table created
SQL> insert into zz_ch_ems values ('张三', '内存', 10);

1 row inserted
SQL> insert into zz_ch_ems values ('李四', '内存', 7);

1 row inserted
SQL> insert into zz_ch_ems values ('王五', '内存', 5);

1 row inserted
SQL> insert into zz_ch_ems values ('张三', '内存', 10);

1 row inserted
SQL> commit;

Commit complete

SQL>
SQL> select name,sum(price) tp,rank() over (order by sum(price) desc) rk from
2 (select name,price from zz_ch_mainboard union all select name,price from zz_ch_ems)
3 group by name;

NAME TP RK
-------- ---------- ----------
张三 30 1
王五 16 2
李四 14 3

Answer 2

select a.name,sum(a.pri+b.pri)as price from
(select name,sum(price) as pri from Mainboard group by name )
as a
inner join
(
select name,sum(price) as pri from ems group by name
) as b
on (a.name = b.name) group by a.name order by price desc

Answer 3

select name,sum(pirce) from Mainboard A inner join EMS B on A.name=B.name