有两个表
table1 , table2
table1结构为:id NUMBER (9) ,
platform VARCHAR2 (50),
gl_id1 VARCHAR2 (50),
creatertime VARCHAR2 (50)table2结构为:
id NUMBER (9) ,
gl_id1 VARCHAR2 (50),
price VARCHAR2 (50),
name VARCHAR2 (50),两表通过 gl_id1 字段关联,想做一个查询
select t1.platform,t1.creatertime,t2.name,t2.price ,(相同 name的price字段的和),(所有记录的price字段的总和) from table1 t1 , table2 t2 where t1.gl_id1 = t2.gl_id1 order by t2.name
我想得到的结果,是比上面的查询还多两个字段,一个是相同 name的price字段的和,还有一个是所有记录的price字段的总和,请问这个sql该怎么写
table1 , table2
table1结构为:id NUMBER (9) ,
platform VARCHAR2 (50),
gl_id1 VARCHAR2 (50),
creatertime VARCHAR2 (50)table2结构为:
id NUMBER (9) ,
gl_id1 VARCHAR2 (50),
price VARCHAR2 (50),
name VARCHAR2 (50),两表通过 gl_id1 字段关联,想做一个查询
select t1.platform,t1.creatertime,t2.name,t2.price ,(相同 name的price字段的和),(所有记录的price字段的总和) from table1 t1 , table2 t2 where t1.gl_id1 = t2.gl_id1 order by t2.name
我想得到的结果,是比上面的查询还多两个字段,一个是相同 name的price字段的和,还有一个是所有记录的price字段的总和,请问这个sql该怎么写
order by t2.name
select t1.platform, t1.creatertime, t2.name, t2.price,
t3.sum_same_name_price, t4.sum_price
from table1 t1, table2 t2,
(select name, sum(price) as sum_same_name_price from table2 group by name) t3,
(select sum(price) as sum_price from table2) t4
where t1.gl_id1 = t2.gl_id1
and t2.name = t3.name
order by t2.name
(select sum(price) as sum_price from table2) t4
修改为:(select name, sum(CAST(table2.price AS MONEY) as sum_same_name_price from table2 group by name) t3,
(select sum(CAST(table2.price AS MONEY) as sum_price from table2) t4
from (select t1.id,
t1.platform,
t1.gl_id1,
t1.creatertime,
t2.id,
t2.gl_id1,
sum(t2.price) as price,
t2.name
from tble1 t1
inner join tble2 t2
on t1.gl_idl = t2.gl_id1
order by t2.name) a