以后发过,但没得到很好解决!请各位帮忙了!库存表(t_kc) 测试数据:
productId(产品编号) num(库存数量) sdate(日期) re(备注)
a01 2000 2008-05-01 买入
a02 1000 2008-05-02 买入
a03 500 2008-05-02 买入
a01 500 2008-05-03 卖出现在需要生成一个视图,数据如下显示
productId(产品编号) num(库存数量) sdate(日期)
a01 2000 2008-05-01
----------------------------------------------------
a01 2000 2008-05-02
a02 1000 2008-05-02
a03 500 2008-05-02
----------------------------------------------------
a01 500 2008-05-03
a02 1000 2008-05-03
a03 500 2008-05-03 上面的视图相当于查看指定日期之前的所有产品的库存日报,不使用函数与存储过程,求一个通用的视图语句。注:上面的"-----"不需要生成,在此只是区分数据用的,
productId(产品编号) num(库存数量) sdate(日期) re(备注)
a01 2000 2008-05-01 买入
a02 1000 2008-05-02 买入
a03 500 2008-05-02 买入
a01 500 2008-05-03 卖出现在需要生成一个视图,数据如下显示
productId(产品编号) num(库存数量) sdate(日期)
a01 2000 2008-05-01
----------------------------------------------------
a01 2000 2008-05-02
a02 1000 2008-05-02
a03 500 2008-05-02
----------------------------------------------------
a01 500 2008-05-03
a02 1000 2008-05-03
a03 500 2008-05-03 上面的视图相当于查看指定日期之前的所有产品的库存日报,不使用函数与存储过程,求一个通用的视图语句。注:上面的"-----"不需要生成,在此只是区分数据用的,
SELECT a.productid, a.num, b.sdate
FROM a,
(SELECT TO_CHAR (:start_day + ROWNUM - 1, 'YYYY-MM-DD') sdate
FROM DUAL
CONNECT BY ROWNUM <= ABS(:end_day - :start_day) + 1) b
WHERE a.sdate <= b.sdate
一模一样的问题,换了个马甲?
答案还是一样
http://topic.csdn.net/u/20080519/10/31340a89-c599-4125-a87f-8e906781468b.html
FROM a,
(SELECT TO_CHAR (:start_day + ROWNUM - 1, 'YYYY-MM-DD') sdate
FROM DUAL
CONNECT BY ROWNUM <= ABS(:end_day - :start_day) + 1) b
WHERE a.sdate <= b.sdate
and row_number() over(partition by a.productid order by a.sdate desc)=1
row_number() over(partition by a.productid order by a.sdate desc) 这个函数,productId,sdate两个都有重复值,这样用并没有达到效果,下面是测试数据:
select sale,cnt,sdate
from
(
select productId,num,sdate,row_number() over(partition by productIdorder by sdate desc) as sort
from (
select 'a01' as productId,2000 as num,'2008-05-01' as sdate from dual union
select 'a01' as productId,2000 as num,'2008-05-02' as sdate from dual union
select 'a02' as productId,1000 as num,'2008-05-02' as sdate from dual union
select 'a03' as productId,500 as num,'2008-05-02' as sdate from dual union
select 'a01' as productId,2000 as num,'2008-05-03' as sdate from dual union
select 'a02' as productId,1000 as num,'2008-05-03' as sdate from dual union
select 'a03' as productId,5000 as num,'2008-05-03' as sdate from dual union
select 'a01' as productId,500 as num,'2008-05-03' as sdate from dual)
)
a where sort=1期待你帮我解决这个问题,
WITH a AS
(SELECT 'a01' AS productid, 2000 AS num, '2008-05-01' AS sdate
FROM DUAL
UNION
SELECT 'a01' AS productid, 2000 AS num, '2008-05-02' AS sdate
FROM DUAL
UNION
SELECT 'a02' AS productid, 1000 AS num, '2008-05-02' AS sdate
FROM DUAL
UNION
SELECT 'a03' AS productid, 500 AS num, '2008-05-02' AS sdate
FROM DUAL
UNION
SELECT 'a01' AS productid, 2000 AS num, '2008-05-03' AS sdate
FROM DUAL
UNION
SELECT 'a02' AS productid, 1000 AS num, '2008-05-03' AS sdate
FROM DUAL
UNION
SELECT 'a03' AS productid, 5000 AS num, '2008-05-03' AS sdate
FROM DUAL
UNION
SELECT 'a01' AS productid, 500 AS num, '2008-05-03' AS sdate
FROM DUAL)
SELECT productid, num, sdate
FROM (SELECT a.productid, a.num, b.sdate,
ROW_NUMBER () OVER (PARTITION BY a.productid, b.sdate ORDER BY a.sdate DESC)
soft
FROM a,
(SELECT TO_CHAR (:start_day + ROWNUM - 1,
'YYYY-MM-DD'
) sdate
FROM DUAL
CONNECT BY ROWNUM <= ABS (:end_day - :start_day) + 1) b
WHERE a.sdate <= b.sdate)
WHERE soft = 1
ORDER BY 3, 1结果
Row# PRODUCTID NUM SDATE1 a01 2000 2008-05-012 a01 2000 2008-05-02
3 a02 1000 2008-05-02
4 a03 500 2008-05-025 a01 500 2008-05-03
6 a02 1000 2008-05-03
7 a03 5000 2008-05-03看看是不是你要的?我这里start_day填的是2008-05-01 end_day是2008-05-03
select sale,cnt,sdate,row_number() over(partition by sale order by sdate desc) as sort
from (
select 'a01' as sale,2000 as cnt,'2008-05-01' as sdate from dual union
select 'a01' as sale,2000 as cnt,'2008-05-02' as sdate from dual union
select 'a02' as sale,1000 as cnt,'2008-05-02' as sdate from dual union
select 'a03' as sale,500 as cnt,'2008-05-02' as sdate from dual union
select 'a01' as sale,2000 as cnt,'2008-05-03' as sdate from dual union
select 'a02' as sale,1000 as cnt,'2008-05-03' as sdate from dual union
select 'a03' as sale,5000 as cnt,'2008-05-03' as sdate from dual union
select 'a01' as sale,500 as cnt,'2008-05-03' as sdate from dual)
结果是:第8行的数据是不需要的!
从上面看不太出你的原则
如果是按数量
row_number() over(partition by sale,sdate order by sum)
关键是你soft产生的原则
如果同一SALE同一天有多条纪录
partition by 后面就是要这两个参数,然后 order by 取数据的条件
如果同一天只会有一条纪录,要取最后一天的
partition by 就跟sale,order by 跟日期desc排序
from
(
SELECT a.productid, a.num, b.sdate,row_number() over(partition by a.productid,b.sdate order by a.sdate desc) as sort
FROM a,
(select TO_CHAR (to_date('2008-05-01','yyyy-mm-dd') + ROWNUM - 1, 'YYYY-MM-DD') sdate
from ( select rownum-1 rnum
from all_objects
where rownum <= sysdate - to_date('2008-05-01','yyyy-mm-dd')+1
)) b
WHERE a.sdate <= b.sdate order by sdate
)
where sort=1