在关联字段都已建索引的前提下,SQL 语句稍改一下: ---------------------------------------------------------------------------------- SELECT A.ProductID, A.ProductName, A.CategoryID FROM Product A,Category B WHERE A.ProductID IN(SELECT TOP 3 ProductID FROM Product WHERE CategoryID=A.CategoryID) AND A.CategoryID=B.CategoryID AND EXISTS(SELECT 1 FROM FrontCategory WHERE FrontCategoryID=B.ParentFrontCategoryID AND ParentFrontCategoryID='0') ORDER BY A.FrontCategoryID
SELECT A.ProductID, A.ProductName, A.CategoryID FROM Product A,Category B WHERE A.ProductID IN(SELECT TOP 3 ProductID FROM Product WHERE CategoryID=A.CategoryID) AND A.CategoryID=B.CategoryID AND EXISTS(SELECT 1 FROM FrontCategory WHERE FrontCategoryID=B.ParentFrontCategoryID AND ParentFrontCategoryID='0') ORDER BY A.FrontCategoryID
“ParentFrontCategoryID='0'”在这列上建索引。
----------------------------------------------------------------------------------
SELECT
A.ProductID,
A.ProductName,
A.CategoryID
FROM
Product A,Category B
WHERE
A.ProductID IN(SELECT TOP 3 ProductID FROM Product WHERE CategoryID=A.CategoryID)
AND
A.CategoryID=B.CategoryID
AND
EXISTS(SELECT 1 FROM FrontCategory WHERE FrontCategoryID=B.ParentFrontCategoryID AND ParentFrontCategoryID='0')
ORDER BY
A.FrontCategoryID
A.ProductID,
A.ProductName,
A.CategoryID
FROM
Product A,Category B
WHERE
A.ProductID IN(SELECT TOP 3 ProductID FROM Product WHERE CategoryID=A.CategoryID)
AND
A.CategoryID=B.CategoryID
AND
EXISTS(SELECT 1 FROM FrontCategory WHERE FrontCategoryID=B.ParentFrontCategoryID AND ParentFrontCategoryID='0')
ORDER BY
A.FrontCategoryID