我正在使用Firebird 2.1,并且需要一些帮助来优化此查询:(也许是通过用JOINS替换IN-s或通过某种方式来加快它的速度,因为它非常慢)
SELECT ClientID, ClientType, ClientName
FROM Clients
WHERE
(
AccessRights = 0 OR
OwnerUserID = :uid OR
(
AccessRights = 2 AND
ClientID IN (SELECT ClientID
FROM ClientRights
WHERE UserID = :uid)
)
)
AND ClientID IN (SELECT CC.ClientID
FROM CaseClients CC
WHERE CC.CaseID IN (SELECT DISTINCT CaseID
FROM TimeSheet
WHERE IsBilled = 0)
AND CC.ClientToBill = 1
AND (SELECT BillingType
FROM Cases
WHERE CaseID = CC.CaseID) = 2
);
谢谢!
最佳答案
SELECT ClientID, ClientType, ClientName FROM Clients
WHERE
(
AccessRights = 0 OR
OwnerUserID = :uid OR
(
AccessRights = 2 AND
EXISTS(SELECT * FROM ClientRights r WHERE r.UserID = :uid and r.ClientId=Clients.ClientID)
)
)
AND EXISTS(SELECT *
FROM CaseClients CC
WHERE
CC.ClientID=Clients.ClientID and
EXISTS(SELECT * FROM TimeSheet
WHERE IsBilled = 0 and TimeSheet.CaseID=CC.CaseID)
AND CC.ClientToBill = 1
AND EXISTS(SELECT BillingType
FROM Cases
WHERE CaseID = CC.CaseID and BillingType=2)
);
关于sql - 如何在Firebird 2.1中优化此查询?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9197887/