请教高手一个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该怎么写 --------------------编程问答-------------------- 相同 name，可能有100种取值 --------------------编程问答--------------------
select t1.platform,t1.creatertime,t2.name,t2.price ,sum(t2.price),t3.sumPrice from table1 t1 ， table2 t2 ,(select sum(price) as sumPrice form table2 ) t3 where t1.gl_id1 = t2.gl_id1 GROUP BY t2.name--------------------编程问答-------------------- have a try
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
+1
补充一下,你的table2表中的price是VARCHAR2 (50),所以需将3L语句其中两句的略微改下:
(select name, sum(price) as sum_same_name_price from table2 group by name) t3,
(select sum(price) as sum_price from table2) t4
修改为:
--------------------编程问答-------------------- select sum(a.price)
(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
--------------------编程问答-------------------- 这个会有多种匹配的吧
--------------------编程问答--------------------
补充：Java , Web 开发
