表1 usersname date money1 money2 opertime streetleo 200701 100 200 200801 01
tim 200701 100 200 200801 02
leo 200702 300 400 200801 01
tim 200702 200 200 200801 02表2 strstreet streetname01 沙河口02 甘井子
users.street = str.street要求得到数据,根据操作时间(opertime,date没啥用,就是区别一下同一个人的不同数据,不是一个月份的数据)查询
得到一个人的totalmoney12 = total1+ total2
total1=sum (money1)
total2=sum (money2)name totalmoney12 total1 total2 opertime streetnameleo 1000 400 600 200801 沙河口
tim 700 300 400 200801 甘井子我写完了求和,但是没把streetname 加上select name,(sum(total1)+sum(total2)) totalmoney12,sum(money1) total1,sum(money2) total2, opertime,street
from users where opertime = '200801' group by name
name totalmoney12 total1 total2 opertime streetleo 1000 400 600 200801 01
tim 700 300 400 200801 02
tim 200701 100 200 200801 02
leo 200702 300 400 200801 01
tim 200702 200 200 200801 02表2 strstreet streetname01 沙河口02 甘井子
users.street = str.street要求得到数据,根据操作时间(opertime,date没啥用,就是区别一下同一个人的不同数据,不是一个月份的数据)查询
得到一个人的totalmoney12 = total1+ total2
total1=sum (money1)
total2=sum (money2)name totalmoney12 total1 total2 opertime streetnameleo 1000 400 600 200801 沙河口
tim 700 300 400 200801 甘井子我写完了求和,但是没把streetname 加上select name,(sum(total1)+sum(total2)) totalmoney12,sum(money1) total1,sum(money2) total2, opertime,street
from users where opertime = '200801' group by name
name totalmoney12 total1 total2 opertime streetleo 1000 400 600 200801 01
tim 700 300 400 200801 02
解决方案 »
- 求助,怎么控制SQL Plus查询出来的结果显示!我想要所有的列在一行显示,值对应相应的列名!!
- 各位老师麻烦打个分给个评价
- 求一条sql语句:可以合并上面3个表,变成下面的table123
- oracle 空间不够 请帮忙
- LINUX下面安装oracle报错
- 物化视图(Materialize View)与一般视图(View)有何区别
- 又一个很简单的oracle 小问题!!
- 小弟有个关于oracal的查询语句问题,希望能得到大哥们指教
- 怎么配置OMS呀,怎么配置好了,老是用户名和口令不对
- 用C#+ORACLE和JAVA+ORACLE开发那个好些??
- Oracle作业执行问题
- oracle导出的SQL脚本看不懂啊
SQL> select distinct
2 name,
3 (sum(money1) over(partition by name))+(sum(money2) over(partition by name)) as totalmoney12,
4 sum(money1) over(partition by name) as total1,
5 sum(money2) over(partition by name) as total2,
6 U.opertime,
7 S.streetname
8 from users U,
17 str S
22 where U.street = S.street
23 and U.opertime = '200801';NAME TOTALMONEY12 TOTAL1 TOTAL2 OPERTIME STREETNAME
---- ------------ ---------- ---------- -------- ----------
leo 1000 400 600 200801 沙河口
tim 700 300 400 200801 甘井子