表结构和数据如下,dated字段是 nvarchar2类型,要求获得每个月的金额,每个月的上月金额,和年初的金额,即去年12月的金额,查询的SQL如何写,求教各位大大
id dated money
1 2008-01 11
1 2008-02 22
1 2008-03 33
1 2008-04 44
1 2008-05 55
1 2008-06 66
1 2008-07 77
1 2008-08 88
1 2008-09 99
1 2008-10 100
id dated money
1 2008-01 11
1 2008-02 22
1 2008-03 33
1 2008-04 44
1 2008-05 55
1 2008-06 66
1 2008-07 77
1 2008-08 88
1 2008-09 99
1 2008-10 100
解决方案 »
- oracle透明网关小问题
- 安装好ORACLE 10G,EM用SYS/SYS用户登录不上
- bulk insert 无法识别?
- 江湖救急......请问如何获得存储过程的out值????
- 请问ORACLE怎么入手?
- 我都不知道这个问题标题应该怎么写?
- 紧急求救!!连接oracle出了这样的错误java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
- 有关一个导入的问题?
- 用户的操作信息记录在哪儿?
- 如何将Oracle中一个表的数据导出变成Insert语句的形式?
- 多表合并问题
- xquery日期比较以及数值比较问题,紧急,求各路高手相助
LAG(money,1,0) OVER(ORDER BY id, dated) AS "上月金额",
(SELECT MAX(money)
FROM moneytab
WHERE id=a.id AND
dated=TO_CHAR(TO_NUMBER(SUBSTR(a.dated,1,4))-1)||'-12') AS "年初金额"
FROM moneytab a;
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-01',11 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-02',22 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-03',33);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-04',44 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-05',55 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-06',66);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-07',77);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-08',88);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-09',99);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-10',100);INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-01',11 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-02',22 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-03',33);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-04',44 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-05',55 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-06',66);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-07',77);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-08',88);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-09',99);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-10',100);SELECT id,
SUM( DECODE(Dated, TO_CHAR(SYSDATE,'yyyy-mm'),MONEY,0)) AS "当月金额",
SUM( DECODE(Dated, TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyy-mm'),MONEY,0)) AS "上月金额",
SUM( DECODE(Dated, TO_CHAR(ADD_MONTHS(SYSDATE,-13),'yyyy-mm'),MONEY,0)) AS "年初金额"
FROM test
GROUP BY Id;
from test a
left join test b on a.id = b.id
and a.dated =
to_char(add_months(to_date(b.dated, 'yyyy-mm'), 1),
'yyyy-mm') left join test c on a.id=c.id and c.dated=to_char(add_months(trunc(to_date(a.dated, 'yyyy-mm'), 'yyyy'), -1),
'yyyy-mm')
lag(a.money)over(partition by a.id order by a.dated)上月金额,
b.money 年初金额
from tt a left join
(select id,dated,money from tt where substr(dated,6)='12')b
on substr(a.dated,1,4)=to_char(substr(b.dated,1,4)+1)
and a.id=b.id
from test a
left join test b on a.id = b.id
and a.dated =
to_char(add_months(to_date(b.dated, 'yyyy-mm'), 1),
'yyyy-mm') left join test c on a.id=c.id and c.dated=to_char(add_months(trunc(to_date(a.dated, 'yyyy-mm'), 'yyyy'), -1),
'yyyy-mm') where a.dated = to_char(sysdate, 'yyyy-mm')
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-01',11 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-02',22 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-03',33);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-04',44 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-05',55 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-06',66);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-07',77);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-08',88);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-09',99);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-10',100);INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-12',100);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2008-12',79);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-12',11 );INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-01',11 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-02',22 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-03',33);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-04',44 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-05',55 );
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-06',66);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-07',77);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-08',88);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-08',78);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-09',99);
INSERT INTO test(Id, Dated, Money)
VALUES(1,'2009-10',100);
SELECT id,
SUM( DECODE(Dated, TO_CHAR(SYSDATE,'yyyy-mm'),MONEY,0)) AS "当月金额",
SUM( DECODE(Dated, TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyy-mm'),MONEY,0)) AS "上月金额",
SUM( DECODE(Dated, TO_CHAR(ADD_MONTHS(SYSDATE,-13),'yyyy-mm'),MONEY,0)) AS "年初金额"
FROM test
GROUP BY Id;------------------------------------------------------------------------------------------------
column dated for a12SELECT t1.Id, t1.Dated, t1.Money,
t2.CurrmonSum "当月金额",
t3.LastmonSum "上月金额",
t4.Staryearsum "年初金额"
FROM test t1 LEFT JOIN (
SELECT id, SUBSTR(Dated,1,7) Currmon, SUM(Money) AS CurrmonSum
FROM test GROUP BY id, SUBSTR(Dated,1,7) ) t2
ON t1.Id=t2.Id AND SUBSTR(t1.Dated,1,7)=t2.Currmon
LEFT JOIN (
SELECT id, SUBSTR(Dated,1,7) Lastmon, SUM(Money) AS LastmonSum
FROM test GROUP BY id, SUBSTR(Dated,1,7) ) t3
ON t1.Id=t3.Id AND TO_CHAR(ADD_MONTHS(TO_DATE(t1.Dated,'yyyy-mm'),-1),'yyyy-mm')=t3.Lastmon
LEFT JOIN (
SELECT Id, SUBSTR(Dated,1,4) Staryear, SUM(Money) Staryearsum
FROM test
WHERE SUBSTR(dated,6,2)='12'
GROUP BY Id, SUBSTR(Dated,1,4) ) t4 ON SUBSTR(t1.Dated,1,4)-1=t4.Staryear;