日期:2014-05-17  浏览次数:20986 次

获取和指定日期前一天的对比数据,这样的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

------解决方案--------------------
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