有两个表
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该怎么写
SELECT T1.PLATFORM,
T1.CREATERTIME,
T2.NAME,
T2.PRICE,
SUM(T2.PRICE) OVER(PARTITION BY T2.NAME), --(相同 name的price字段的和),
SUM(T2.PRICE) OVER() --(所有记录的price字段的总和)
FROM TABLE1 T1, TABLE2 T2
WHERE T1.GL_ID1 = T2.GL_ID1
ORDER BY T2.NAME;
这个要+distinct ,不然会有重复name。
t1.creatertime,
t2.name,
t2.price,
sum(t2.price) over(partition by t2.name),
sum(t2.price) over()
from table1 t1, table2 t2
where t1.gl_id1 = t2.gl_id1
order by t2.name