CREATE OR REPLACE VIEW V_BUYORDER AS
SELECT distinct stock_code,
max(CASE rn WHEN 1 THEN nlimitprice ELSE null END) AS buy_price1,
max(CASE rn WHEN 1 THEN nvolume ELSE null END) AS buy_quan1,
max(CASE rn WHEN 2 THEN nlimitprice ELSE null END) AS buy_price2,
max(CASE rn WHEN 2 THEN nvolume ELSE null END) AS buy_quan2,
max(CASE rn WHEN 3 THEN nlimitprice ELSE null END) AS buy_price3,
max(CASE rn WHEN 3 THEN nvolume ELSE null END) AS buy_quan3
FROM (SELECT distinct stock_code,
nlimitprice,
nvolume,
ROW_NUMBER() OVER(PARTITION BY stock_code ORDER BY stock_code) RN
FROM (select stock_code, nlimitprice, nvolume
from (select t2.stock_code, --产品代码
t1.nlimitprice, --价格
sum(t1.nvolume) nvolume --数量
from tbfutrsopenorder t1, futures_new t2
where to_char(t1.dplacedat, 'yyyy-MM-dd') =
to_char(sysdate, 'yyyy-MM-dd')
and t1.nproductid = t2.id
and t1.ntradeaction = 0
and (t1.norderstatus = 0 or t1.norderstatus = 2)
group by t2.stock_code,t1.nlimitprice
order by t2.stock_code asc,
t1.nlimitprice desc)
order by stock_code,nlimitprice desc)
order by stock_code, nlimitprice desc)
GROUP BY stock_code
想提高查询速度
SELECT distinct stock_code,
max(CASE rn WHEN 1 THEN nlimitprice ELSE null END) AS buy_price1,
max(CASE rn WHEN 1 THEN nvolume ELSE null END) AS buy_quan1,
max(CASE rn WHEN 2 THEN nlimitprice ELSE null END) AS buy_price2,
max(CASE rn WHEN 2 THEN nvolume ELSE null END) AS buy_quan2,
max(CASE rn WHEN 3 THEN nlimitprice ELSE null END) AS buy_price3,
max(CASE rn WHEN 3 THEN nvolume ELSE null END) AS buy_quan3
FROM (SELECT distinct stock_code,
nlimitprice,
nvolume,
ROW_NUMBER() OVER(PARTITION BY stock_code ORDER BY stock_code) RN
FROM (select stock_code, nlimitprice, nvolume
from (select t2.stock_code, --产品代码
t1.nlimitprice, --价格
sum(t1.nvolume) nvolume --数量
from tbfutrsopenorder t1, futures_new t2
where to_char(t1.dplacedat, 'yyyy-MM-dd') =
to_char(sysdate, 'yyyy-MM-dd')
and t1.nproductid = t2.id
and t1.ntradeaction = 0
and (t1.norderstatus = 0 or t1.norderstatus = 2)
group by t2.stock_code,t1.nlimitprice
order by t2.stock_code asc,
t1.nlimitprice desc)
order by stock_code,nlimitprice desc)
order by stock_code, nlimitprice desc)
GROUP BY stock_code
想提高查询速度
解决方案 »
- 救急啊,我用JSP ,JDBC连虚机数据库连不上
- ORA-00604: 递归 SQL 层 1 出现错误,ORA-06553:PLS-213: standard包打不开
- oracle 导入数据库
- 求教oracle sql语句
- 求教Oracle数据库监听程序的问题
- 大哥大姐,请教这样的问题,sum语句求和,分不多了,全在这,谢谢各位
- 中型企业生产环境下的Oracle9i一般是在什么操作系统平台运行比较好?
- 我在W2KAS下安装Oracle8i标准发行版2(8.1.6),是正版的Oracle8i,可是插入Oracle光盘点安装,出现鼠标沙漏,然后沙漏恢复,没有安装上也没
- 为什么我在win2000 server中不能安装oracle8i?
- 如何查看Oracle中sql语句的执行时间
- 判断表的某个字段是否存在
- sql 如何选择某列之外的所有列
SELECT stock_code,
max( DECODE(RN,1,nlimitprice ,NULL)) AS buy_price1,
max(DECODE(RN,1,nvolume ,NULL)) AS buy_quan1,
max(DECODE(RN,2,nlimitprice ,NULL) ) AS buy_price2,
max(DECODE(RN,2,nvolume ,NULL) ) AS buy_quan2,
max(DECODE(RN,3,nlimitprice ,NULL)) AS buy_price3,
max(DECODE(RN,3,nvolume ,NULL) ) AS buy_quan3
FROM (SELECT DISTINCT stock_code,
nlimitprice,
nvolume,
ROW_NUMBER() OVER(PARTITION BY stock_code ORDER BY stock_code) RN
FROM (select stock_code, nlimitprice, nvolume
from (select t2.stock_code, --产品代码
t1.nlimitprice, --价格
sum(t1.nvolume) nvolume --数量
from tbfutrsopenorder t1, futures_new t2
where to_char(t1.dplacedat, 'yyyy-MM-dd') =
to_char(sysdate, 'yyyy-MM-dd')
and t1.nproductid = t2.id
and t1.norderstatus in(1,2)
and t1.ntradeaction = 0
group by t2.stock_code,t1.nlimitprice
)
)
)
GROUP BY stock_code PS:因为对具体情况不了解,只能大概给你修改一下了
SELECT STOCK_CODE,
MAX(CASE RN WHEN 1 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE1,
MAX(CASE RN WHEN 1 THEN NVOLUME ELSE NULL END) AS BUY_QUAN1,
MAX(CASE RN WHEN 2 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE2,
MAX(CASE RN WHEN 2 THEN NVOLUME ELSE NULL END) AS BUY_QUAN2,
MAX(CASE RN WHEN 3 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE3,
MAX(CASE RN WHEN 3 THEN NVOLUME ELSE NULL END) AS BUY_QUAN3
FROM (SELECT T2.STOCK_CODE,
T1.NLIMITPRICE,
SUM(T1.NVOLUME) NVOLUME,
ROW_NUMBER() OVER(PARTITION BY T2.STOCK_CODE ORDER BY T2.STOCK_CODE, T1.NLIMITPRICE DESC) RN
FROM TBFUTRSOPENORDER T1, FUTURES_NEW T2
WHERE T1.NPRODUCTID = T2.ID
AND T1.NTRADEACTION = 0
AND (T1.NORDERSTATUS = 0 OR T1.NORDERSTATUS = 2)
AND TO_CHAR(T1.DPLACEDAT, 'YYYY-MM-DD') =
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
GROUP BY T2.STOCK_CODE, T1.NLIMITPRICE) TT
GROUP BY STOCK_CODE;
SELECT STOCK_CODE,
MAX(CASE RN WHEN 1 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE1,
MAX(CASE RN WHEN 1 THEN NVOLUME ELSE NULL END) AS BUY_QUAN1,
MAX(CASE RN WHEN 2 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE2,
MAX(CASE RN WHEN 2 THEN NVOLUME ELSE NULL END) AS BUY_QUAN2,
MAX(CASE RN WHEN 3 THEN NLIMITPRICE ELSE NULL END) AS BUY_PRICE3,
MAX(CASE RN WHEN 3 THEN NVOLUME ELSE NULL END) AS BUY_QUAN3
FROM (SELECT STOCK_CODE,
NLIMITPRICE,
NVOLUME,
ROW_NUMBER() OVER(PARTITION BY STOCK_CODE ORDER BY STOCK_CODE, NLIMITPRICE DESC) RN
FROM (SELECT T2.STOCK_CODE, T1.NLIMITPRICE, SUM(T1.NVOLUME) NVOLUME
FROM TBFUTRSOPENORDER T1, FUTURES_NEW T2
WHERE T1.NPRODUCTID = T2.ID
AND T1.NTRADEACTION = 0
AND (T1.NORDERSTATUS = 0 OR T1.NORDERSTATUS = 2)
AND TO_CHAR(T1.DPLACEDAT, 'YYYY-MM-DD') =
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
GROUP BY T2.STOCK_CODE, T1.NLIMITPRICE) ZZ) TT
GROUP BY STOCK_CODE;