用分析函数改写子查询可以有效改善SQL性能
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
create table ITEM ( CODE VARCHAR2(10), PRICE NUMBER, YEAR VARCHAR2(10), PRODATE DATE, SALPRICE NUMBER, BUYPRICE NUMBER ); commit; insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('001', 100, '2017', to_date('11-07-2017', 'dd-mm-yyyy'), 200, 50); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('001', 200, '2017', to_date('17-07-2017', 'dd-mm-yyyy'), 300, 150); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('001', 300, '2017', to_date('18-07-2017', 'dd-mm-yyyy'), 400, 250); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('002', 100, '2016', to_date('08-07-2016', 'dd-mm-yyyy'), 200, 50); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('002', 300, '2016', to_date('07-07-2016', 'dd-mm-yyyy'), 400, 250); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('002', 200, '2017', to_date('06-07-2017', 'dd-mm-yyyy'), 300, 150); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('002', 400, '2017', to_date('19-07-2017', 'dd-mm-yyyy'), 500, 350); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('003', 100, '2015', to_date('08-07-2015', 'dd-mm-yyyy'), 200, 50); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('003', 200, '2014', to_date('17-07-2014', 'dd-mm-yyyy'), 300, 150); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('004', 100, '2017', to_date('11-07-2017', 'dd-mm-yyyy'), 200, 50); insert into ITEM (CODE, PRICE, YEAR, PRODATE, SALPRICE, BUYPRICE) values ('005', 200, '2017', to_date('16-07-2017', 'dd-mm-yyyy'), 300, 150); commit; create table ITEMSTS ( CODE VARCHAR2(10), ITEMTYPE NUMBER, ITEMSTS NUMBER ); commit; insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('001', 1, 1); insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('002', 1, 1); insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('003', 1, 1); insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('004', 3, 1); insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('005', 2, 2); insert into ITEMSTS (CODE, ITEMTYPE, ITEMSTS) values ('005', 1, 2); commit;
SELECT I.CODE, I.YEAR, I.PRODATE, (SELECT SUM(I2.PRICE) FROM ITEM I2 WHERE I.CODE = I2.CODE AND I2.PRODATE BETWEEN I.PRODATE - 180 AND I.PRODATE AND I.YEAR = I2.YEAR) AS SUM_PRICE, (SELECT SUM(I2.SALPRICE) FROM ITEM I2 WHERE I.CODE = I2.CODE AND I2.PRODATE BETWEEN I.PRODATE - 180 AND I.PRODATE AND I.YEAR = I2.YEAR) AS SUM_SALPRICE, (SELECT SUM(I2.BUYPRICE) FROM ITEM I2 WHERE I.CODE = I2.CODE AND I2.PRODATE BETWEEN I.PRODATE - 180 AND I.PRODATE AND I.YEAR = I2.YEAR) AS SUM_BUYPRICE FROM ITEM I, ITEMSTS S WHERE I.CODE = S.CODE AND S.ITEMTYPE = 1 AND S.ITEMSTS = 1 AND I.PRODATE > SYSDATE - 30 SELECT I.CODE, I.YEAR, I.PRODATE, CASE WHEN I.PRODATE > SYSDATE - 30 THEN SUM(I.PRICE) OVER(PARTITION BY I.CODE, I.YEAR ORDER BY I.PRODATE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW) END AS SUM_PRICE, CASE WHEN I.PRODATE > SYSDATE - 30 THEN SUM(I.SALPRICE) OVER(PARTITION BY I.CODE, I.YEAR ORDER BY I.PRODATE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW) END AS SAL_SUM_PRICE, CASE WHEN I.PRODATE > SYSDATE - 30 THEN SUM(I.BUYPRICE) OVER(PARTITION BY I.CODE, I.YEAR ORDER BY I.PRODATE RANGE BETWEEN 180 PRECEDING AND CURRENT ROW) END AS BUY_SUM_PRICE FROM ITEM I INNER JOIN ITEMSTS S ON I.CODE = S.CODE WHERE S.ITEMTYPE = 1 AND S.ITEMSTS = 1 AND I.PRODATE > SYSDATE - 30