我有一个表planGOODS_CD DATE QTY
A 20111001 100
A 20111005 500
A 20111011 400
A 20111012 200
A 20111023 300
A 20111028 800
B 20111020 900
B 20111101 700
B 20111121 600我希望查出到2011/10/25日为止,最后一天的数量。
即:A 300
B 900
A 20111001 100
A 20111005 500
A 20111011 400
A 20111012 200
A 20111023 300
A 20111028 800
B 20111020 900
B 20111101 700
B 20111121 600我希望查出到2011/10/25日为止,最后一天的数量。
即:A 300
B 900
解决方案 »
- 关于pl/sql developer 的使用
- left join 如何去除重复
- oracle数据导入导出问题
- 9i中创建数据库不成功,报错:ORA-01503: CREATE CONTROLFILE failed,忽略后还有很多其他错误,怎么解决?
- 密码忘记了。。。。。。。。。。?
- oracle8.1.7比oracle8.1.6到底有哪些方面的改进?
- 请问用PC做Oracle的服务器硬件如何配置?
- 新手问题
- 有关PL/SQL的异常捕获的两个相关问题:
- 请教,我们要对数据库进行E-R分析,用的是ORACLE。可以用POWERDESIGNER吗?
- 请教一个sql,实现根据截止到每个日期的总数量,计算出分散每个日期段的数量
- 一条语句竟然如此之慢求高人解答
(
select 'A' GOODS_CD, '20111001' oDATE, '100' QTY from dual
union
select 'A', '20111005', '500' from dual
union
select 'A', '20111011', '400' from dual
union
select 'A', '20111012', '200' from dual
union
select 'A', '20111121', '300' from dual
union
select 'A', '20111028', '800' from dual
union
select 'B', '20111020', '900' from dual
union
select 'B', '20111101', '700' from dual
union
select 'B', '20111121', '600' from dual)
select goods_cd, qty from a where odate = (select max(odate) from a)
--兰兰少了一个条件吧?
with a as
(
select 'A' GOODS_CD, '20111001' oDATE, '100' QTY from dual
union
select 'A', '20111005', '500' from dual
union
select 'A', '20111011', '400' from dual
union
select 'A', '20111012', '200' from dual
union
select 'A', '20111121', '300' from dual
union
select 'A', '20111028', '800' from dual
union
select 'B', '20111020', '900' from dual
union
select 'B', '20111101', '700' from dual
union
select 'B', '20111121', '600' from dual)
select goods_cd, qty from a where odate = (select max(odate) from a where odate <= 参数日期)
WITH A AS
(SELECT 'A' GOODS_CD, '20111001' ODATE, '100' QTY
FROM DUAL
UNION ALL
SELECT 'A', '20111005', '500'
FROM DUAL
UNION ALL
SELECT 'A', '20111011', '400'
FROM DUAL
UNION ALL
SELECT 'A', '20111012', '200'
FROM DUAL
UNION ALL
SELECT 'A', '20111023', '300'
FROM DUAL
UNION ALL
SELECT 'A', '20111028', '800'
FROM DUAL
UNION ALL
SELECT 'B', '20111020', '900'
FROM DUAL
UNION ALL
SELECT 'B', '20111101', '700'
FROM DUAL
UNION ALL
SELECT 'B', '20111121', '600' FROM DUAL)
SELECT d.*
FROM (SELECT GOODS_CD,
ODATE,
QTY,
ROW_NUMBER() OVER(PARTITION BY GOODS_CD ORDER BY ODATE DESC) RN
FROM A
WHERE TO_DATE(ODATE, 'YYYYMMDD') <= TO_DATE('20111025', 'YYYYMMDD')
) D
WHERE RN = 1;
SELECT GOODS_CD,MAX(QTY) FROM TABLE1 WHERE DATE < '20111025' GROUP BY GOODS_CD
with a as
(
select 'A' GOODS_CD, '20111001' oDATE, '100' QTY from dual
union
select 'A', '20111005', '500' from dual
union
select 'A', '20111011', '400' from dual
union
select 'A', '20111012', '200' from dual
union
select 'A', '20111121', '300' from dual
union
select 'A', '20111028', '800' from dual
union
select 'B', '20111020', '900' from dual
union
select 'B', '20111101', '700' from dual
union
select 'B', '20111121', '600' from dual)
select goods_cd, qty from a where (goods_cd,odate) in (select GOODS_CD,max(odate) from a where odate <= 20111025 group by GOODS_CD);
with a as
(
select 'A' GOODS_CD, '20111001' oDATE, '100' QTY from dual
union
select 'A', '20111005', '500' from dual
union
select 'A', '20111011', '400' from dual
union
select 'A', '20111012', '200' from dual
union
select 'A', '20111121', '300' from dual
union
select 'A', '20111028', '800' from dual
union
select 'B', '20111020', '900' from dual
union
select 'B', '20111101', '700' from dual
union
select 'B', '20111121', '600' from dual)
select goods_cd, qty from a where (goods_cd,odate) in (select GOODS_CD,min(odate) from a where odate > 20111025 group by GOODS_CD);