时间戳

首页 > 解决方案 > 优化 SELECT 查询以处理大型数据库

sql - 优化 SELECT 查询以处理大型数据库

问题描述

这是我的数据库的一部分：

ID  EmployeeID    Status    EffectiveDate
 1  110545        Active    2011-08-01
 2  110700        Active    2012-01-05
 3  110060        Active    2012-01-05  
 4  110222        Active    2012-06-30
 5  110545        Resigned  2012-07-01
 6  110545        Active    2013-02-12

我想生成选择活跃员工的记录：

ID  EmployeeID    Status  EffectiveDate
 2  110700        Active  2012-01-05
 3  110060        Active  2012-01-05
 4  110222        Active  2012-06-30

所以，我尝试了这个查询：

SELECT *
FROM Employee AS E
WHERE E.Status='Active' AND 
      E.EffectiveDate between'2011-08-01' and '2012-07-02'AND  NOT 
      EXISTS(SELECT * FROM Employee AS E2 
             WHERE E2.EmployeeID = E.EmployeeID AND E2.Status = 'Resigned'
                        AND E2.EffectiveDate between '2011-08-01' and '2012-07-02'
             );

它仅适用于少量数据，但在大型数据库中出现超时错误。

你能帮我优化一下吗？

标签： sql

解决方案

我尝试使用案例语句来实现上述结果集。希望这可以帮助。

CREATE TABLE employee_test
(rec NUMBER,
employee_id NUMBER,
status VARCHAR2(100),
effectivedate DATE);


INSERT INTO employee_test VALUES(1,110545,'Active',TO_DATE('01-08-2011','DD-MM-YYYY'));
INSERT INTO employee_test VALUES(2,110545,'Active',TO_DATE('05-01-2012','DD-MM-YYYY'));
INSERT INTO employee_test VALUES(3,110545,'Active',TO_DATE('05-01-2012','DD-MM-YYYY'));
INSERT INTO employee_test VALUES(4,110545,'Active',TO_DATE('30-06-2012','DD-MM-YYYY'));
INSERT INTO employee_test VALUES(5,110545,'Resigned',TO_DATE('01-07-2012','DD-MM-YYYY'));
INSERT INTO employee_test VALUES(6,110545,'Active',TO_DATE('12-02-2013','DD-MM-YYYY'));
COMMIT;


SELECT * FROM(
                        SELECT e.* ,
                        CASE WHEN (effectivedate BETWEEN TO_DATE('2011-08-01','YYYY-MM-DD') AND  TO_DATE('2012-07-02','YYYY-MM-DD')  AND status='Active')
                        THEN 'Y' ELSE 'N' END AS FLAG
                         FROM Employee_Test e)
 WHERE Flag='Y'
 ;

推荐阅读