获取和指定日期前一天的对比数据,这样的sql语句该如何写呢
获取和指定日期前一天的对比数据,这样的sql语句该怎么写呢
设表结构如下:
rq sl pid
2012-9-1 50 1
2012-9-1 30 2
2012-8-31 45 1
2012-8-31 36 2
获取9月1日的数据,按照pid相同,得出sl和前一天的差,结果类似如下
rq sl bh pid
2012-9-1 50 5 1
2012-9-1 30 -6 2
------解决方案--------------------
设表结构如下:
rq sl pid
2012-9-1 50 1
2012-9-1 30 2
2012-8-31 45 1
2012-8-31 36 2
获取9月1日的数据,按照pid相同,得出sl和前一天的差,结果类似如下
rq sl bh pid
2012-9-1 50 5 1
2012-9-1 30 -6 2
------解决方案--------------------
- SQL code
with t (rq, sl, pid) as ( select to_date('2012-9-1','yyyy-mm-dd'), 50, 1 from dual union all select to_date('2012-9-1','yyyy-mm-dd'), 30, 2 from dual union all select to_date('2012-8-31','yyyy-mm-dd'), 45, 1 from dual union all select to_date('2012-8-31','yyyy-mm-dd'), 36, 2 from dual) select * from ( select rq, sl, sl-(select sl from t t1 where t1.rq=t.rq-1 and t1.pid=t.pid) bh, pid from t) where bh is not null;
------解决方案--------------------
托大了,我以为这个SQL可以直接查询出来!汗....
- SQL code
WITH FOO AS( SELECT '2012-9-1' AS RQ,50 AS SL,1 AS PID FROM DUAL UNION --这个地方把LZ的RQ修改成9-2,否则sql会出错 SELECT '2012-9-2' AS RQ,30 AS SL,2 AS PID FROM DUAL UNION SELECT '2012-8-31' AS RQ,45 AS SL,1 AS PID FROM DUAL UNION SELECT '2012-8-31' AS RQ,36 AS SL,2 AS PID FROM DUAL ) SELECT MPID,SUM(ASL)-SUM(BSL) AS BH,PID FROM ( SELECT MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) AS MPID, CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) = RQ THEN SL ELSE 0 END AS ASL, CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID)!= RQ THEN SL ELSE 0 END AS BSL, PID FROM FOO ) O GROUP BY PID,MPID
------解决方案--------------------
求助,帮顶
------解决方案--------------------
--试试LAG函数
--rq sl pid
with t as (
select date'2012-9-1' as fdate, 50 as QUANTITY, 1 as fid from dual
union all
select date'2012-9-1', 30, 2 from dual
union all
select date'2012-8-31', 45, 1 from dual
union all
select date'2012-8-31', 36, 2 from dual
)
select fdate,quantity,fid,lag(QUANTITY)over(partition by fid order by fdate) as lastDay from t
FDATE QUANTITY FID LASTDAY
------------------------- ---------------------- ---------------------- ----------------------
2012-08-31 00:00:00 45 1
2012-09-01 00:00:00 50 1 45
2012-08-31 00:00:00 36 2
2012-09-01 00:00:00 30 2 36
------解决方案--------------------
WITH t AS
( SELECT DATE'2012-9-1' AS fdate, 50 AS QUANTITY, 1 AS fid FROM dual
UNION ALL
SELECT DATE'2012-9-1', 30, 2 FROM dual
UNION ALL
SELECT DATE'2012-8-31', 45, 1 FROM dual
UNION ALL
SELECT DATE'2012-8-31', 36, 2 FROM dual
)
SELECT fdate,
quantity,
fid,
lastday,
DECODE(lastday,NULL,quantity,quantity-lastday) fresult
FROM
(SELECT fdate,
quantity,
fid,
lag(QUANTITY)over(partition BY fid order by fdate) AS lastDay
FROM t
)
FDATE QUANTITY FID LASTDAY FRESULT
------------------------- ---------------------- ---------------------- ---------------------- ----------------------
2012-08-31 00:00:00 45 1 45
2012-09-01 00:00:00 50 1 45 5
2012-08-31 00:00:00 36 2 36