SELECT T.FEE_MONTH,
T.MI_CODE,
SUM(NVL(T.AMNT, 0)) AMNT, /*费用*/
SUM(NVL(T.TIMES, 0)) TIMES, /*人次*/
SUM(NVL(T.PCOUNT, 0)) PCOUNT /*人数*/
FROM M_ZF_INSTFEE_MSTAT T
WHERE T.PSNTYPE = '1' /*城镇居民*/
AND T.FEE_MONTH BETWEEN '201012' AND '201112'
AND (T.MI_CODE = '00000004' OR T.MI_CODE = '05110004')
GROUP BY T.MI_CODE, T.FEE_MONTH
显示结果如下:
FEE_MONTH MI_CODE AMNT TIMES PCOUNT
1 201012 00000004 0 0 0
2 201012 05110004 0 0 0
3 201101 00000004 20326 5 5
4 201101 05110004 3328 1 1
5 201102 00000004 2503 2 2
6 201102 05110004 0 0 0
7 201103 00000004 3672 1 1
8 201103 05110004 0 0 0
9 201104 00000004 0 0 0
10 201104 05110004 0 0 0
11 201105 00000004 0 0 0
12 201105 05110004 0 0 0
我想得到如下结果,请教SQL,谢谢!
FEE_MONTH MI_CODE AMNT TIMES PCOUNT PRE_AMNT(上一月份的金额)
1 201012 00000004 0 0 0 0
2 201012 05110004 0 0 0 0
3 201101 00000004 20326 5 5 0
4 201101 05110004 3328 1 1 0
5 201102 00000004 2503 2 2 20326
6 201102 05110004 0 0 0 3328
7 201103 00000004 3672 1 1 2503
8 201103 05110004 0 0 0 0
9 201104 00000004 0 0 0 3672
10 201104 05110004 0 0 0 0
11 201105 00000004 0 0 0 0
12 201105 05110004 0 0 0 0
T.MI_CODE,
SUM(NVL(T.AMNT, 0)) AMNT, /*费用*/
SUM(NVL(T.TIMES, 0)) TIMES, /*人次*/
SUM(NVL(T.PCOUNT, 0)) PCOUNT /*人数*/
FROM M_ZF_INSTFEE_MSTAT T
WHERE T.PSNTYPE = '1' /*城镇居民*/
AND T.FEE_MONTH BETWEEN '201012' AND '201112'
AND (T.MI_CODE = '00000004' OR T.MI_CODE = '05110004')
GROUP BY T.MI_CODE, T.FEE_MONTH
显示结果如下:
FEE_MONTH MI_CODE AMNT TIMES PCOUNT
1 201012 00000004 0 0 0
2 201012 05110004 0 0 0
3 201101 00000004 20326 5 5
4 201101 05110004 3328 1 1
5 201102 00000004 2503 2 2
6 201102 05110004 0 0 0
7 201103 00000004 3672 1 1
8 201103 05110004 0 0 0
9 201104 00000004 0 0 0
10 201104 05110004 0 0 0
11 201105 00000004 0 0 0
12 201105 05110004 0 0 0
我想得到如下结果,请教SQL,谢谢!
FEE_MONTH MI_CODE AMNT TIMES PCOUNT PRE_AMNT(上一月份的金额)
1 201012 00000004 0 0 0 0
2 201012 05110004 0 0 0 0
3 201101 00000004 20326 5 5 0
4 201101 05110004 3328 1 1 0
5 201102 00000004 2503 2 2 20326
6 201102 05110004 0 0 0 3328
7 201103 00000004 3672 1 1 2503
8 201103 05110004 0 0 0 0
9 201104 00000004 0 0 0 3672
10 201104 05110004 0 0 0 0
11 201105 00000004 0 0 0 0
12 201105 05110004 0 0 0 0
解决方案 »
- oracle 存储过程 调用远程 连接超时异常
- oracle blob读取
- 存储过程实现数据校验,求助啊!!
- WIN7 32位系统Database Configuration Assistant删除数据库灰色
- Oracle中汉字数据显示为乱码的问题
- 如何处理添加如"<a href='#'>;&*;;</a>"的数据?
- 如何控制应用程序中用户的权限?
- 各位Oracle大虾,现在Oracle10g,我看Oracle9i的书可以吗?
- word 中插入visio图出错的原因。解决就结贴给分。
- vc如何通过ADO调用输入参数为记录类型的oracle存储过程
- oracle9i导出的数据库文件怎么导入oracle10g中
- 大数据量比对方法
with tbl as
(
select '201101' as sdate, '001' as id, 11 as price from dual
union all
select '201101' as sdate, '002' as id, 12 as price from dual
union all
select '201102' as sdate, '001' as id, 21 as price from dual
union all
select '201102' as sdate, '002' as id, 22 as price from dual
)
select sdate, id, price, nvl(lag(price) over(partition by id order by sdate), 0) as last_price
from tbl
order by sdate, id;
SDATE ID PRICE LAST_PRICE
------ --- ---------- ----------
201101 001 11 0
201101 002 12 0
201102 001 21 11
201102 002 22 12
那岂不是说我要输出100条记录,那我就要在tb1中写100条select语句了,这肯定不行
你先弄清楚 with 的用法是干嘛的先