Geek asks and answers

Pull different columns efficiently using a common correlated subquery

I need to pull a few columns from a subquery, which also needs a WHERE filter that refers to columns in the FROM table. I have a few questions about this:

  • Is there another solution to this problem besides mine below?
  • Is another solution needed or is this solution effective enough?

Example:

In the following example, I am writing a presentation to present test results, in particular, to detect failures that may be required to resolve or return.

I can't just use JOIN because I need to filter my actual subquery first (note that I get TOP 1 for the “test subject”, sorted by date or descending)

My goal is not to write (and execute) essentially the same subquery.

SELECT ExamineeID, LastName, FirstName, Email,
   (SELECT COUNT(examineeTestID)
    FROM exam.ExamineeTest tests
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2) Attempts,
   (SELECT TOP 1 ExamineeTestID
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY Score DESC) bestExamineeTestID,
   (SELECT TOP 1 Score
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY Score DESC) bestScore,
   (SELECT TOP 1 DateDue
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY Score DESC) bestDateDue,
   (SELECT TOP 1 TimeCommitted
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY Score DESC) bestTimeCommitted,
   (SELECT TOP 1 ExamineeTestID
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY DateDue DESC) currentExamineeTestID,
   (SELECT TOP 1 Score
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY DateDue DESC) currentScore,
   (SELECT TOP 1 DateDue
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY DateDue DESC) currentDateDue,
   (SELECT TOP 1 TimeCommitted
    FROM exam.ExamineeTest T
    WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
    ORDER BY DateDue DESC) currentTimeCommitted
FROM exam.Examinee E
+3
source share
4 answers

To answer your second question, yes, the best way is fine, because the query you are using is hard to understand, it’s hard to maintain, and even if the performance is acceptable now, it’s a shame to query the same table several times when you don’t need plus performance, may not always be acceptable if your application ever grows to a noticeable size.

To answer your first question, I have several methods for you. They assume SQL 2005 or higher, if not specified.

, BestExamineeID CurrentExamineeID, , ExamineeID, , NULL, NULL.

OUTER/CROSS APPLY , WHERE JOIN. . , .

SELECT
   ExamineeID,
   LastName,
   FirstName,
   Email,
   B.Attempts,
   BestScore = B.Score,
   BestDateDue = B.DateDue,
   BestTimeCommitted = B.TimeCommitted,
   CurrentScore = C.Score,
   CurrentDateDue = C.DateDue,
   CurrentTimeCommitted = C.TimeCommitted
FROM
   exam.Examinee E
   OUTER APPLY ( -- change to CROSS APPLY if you only want examinees who've tested
      SELECT TOP 1
         Score, DateDue, TimeCommitted,
         Attempts = Count(*) OVER ()
      FROM exam.ExamineeTest T
      WHERE
         E.ExamineeID = T.ExamineeID
         AND T.TestRevisionID = 3
         AND T.TestID = 2
      ORDER BY Score DESC
   ) B
   OUTER APPLY ( -- change to CROSS APPLY if you only want examinees who've tested
      SELECT TOP 1
         Score, DateDue, TimeCommitted
      FROM exam.ExamineeTest T
      WHERE
         E.ExamineeID = T.ExamineeID
         AND T.TestRevisionID = 3
         AND T.TestID = 2
      ORDER BY DateDue DESC
   ) C

, , Count(*) OVER (), OUTER APPLY, . exam.Examinee, .

, () . -, , , , , , .

WITH Data AS (
   SELECT
      *,
      Count(*) OVER (PARTITION BY ExamineeID) Cnt,
      Row_Number() OVER (PARTITION BY ExamineeID ORDER BY Score DESC) ScoreOrder,
      Row_Number() OVER (PARTITION BY ExamineeID ORDER BY DateDue DESC) DueOrder
   FROM
      exam.ExamineeTest
), Vals AS (
   SELECT
      ExamineeID,
      Max(Cnt) Attempts,
      Max(CASE WHEN ScoreOrder = 1 THEN Score ELSE NULL END) BestScore,
      Max(CASE WHEN ScoreOrder = 1 THEN DateDue ELSE NULL END) BestDateDue,
      Max(CASE WHEN ScoreOrder = 1 THEN TimeCommitted ELSE NULL END) BestTimeCommitted,
      Max(CASE WHEN DueOrder = 1 THEN Score ELSE NULL END) BestScore,
      Max(CASE WHEN DueOrder = 1 THEN DateDue ELSE NULL END) BestDateDue,
      Max(CASE WHEN DueOrder = 1 THEN TimeCommitted ELSE NULL END) BestTimeCommitted
   FROM Data
   GROUP BY
      ExamineeID
)
SELECT
   E.ExamineeID,
   E.LastName,
   E.FirstName,
   E.Email,
   V.Attempts,
   V.BestScore, V.BestDateDue, V.BestTimeCommitted,
   V.CurrentScore, V.CurrentDateDue, V.CurrentTimeCommitted
FROM
   exam.Examinee E
   LEFT JOIN Vals V ON E.ExamineeID = V.ExamineeID
   -- change join to INNER if you only want examinees who've tested

, SQL 2000:

SELECT
   E.ExamineeID,
   E.LastName,
   E.FirstName,
   E.Email,
   Y.Attempts,
   Y.BestScore, Y.BestDateDue, Y.BestTimeCommitted,
   Y.CurrentScore, Y.CurrentDateDue, Y.CurrentTimeCommitted
FROM
   exam.Examinee E
   LEFT JOIN ( -- change to inner if you only want examinees who've tested
      SELECT
         X.ExamineeID,
         X.Cnt Attempts,
         Max(CASE Y.Which WHEN 1 THEN T.Score ELSE NULL END) BestScore,
         Max(CASE Y.Which WHEN 1 THEN T.DateDue ELSE NULL END) BestDateDue,
         Max(CASE Y.Which WHEN 1 THEN T.TimeCommitted ELSE NULL END) BestTimeCommitted,
         Max(CASE Y.Which WHEN 2 THEN T.Score ELSE NULL END) CurrentScore,
         Max(CASE Y.Which WHEN 2 THEN T.DateDue ELSE NULL END) CurrentDateDue,
         Max(CASE Y.Which WHEN 2 THEN T.TimeCommitted ELSE NULL END) CurrentTimeCommitted
      FROM
         (
            SELECT ExamineeID, Max(Score) MaxScore, Max(DueDate) MaxDueDate, Count(*) Cnt
            FROM exam.ExamineeTest
            WHERE
               TestRevisionID = 3
               AND TestID = 2
            GROUP BY ExamineeID
         ) X
         CROSS JOIN (SELECT 1 UNION ALL SELECT 2) Y (Which)
         INNER JOIN exam.ExamineeTest T
            ON X.ExamineeID = T.ExamineeID
            AND (
               (Y.Which = 1 AND X.MaxScore = T.MaxScore)
               OR (Y.Which = 2 AND X.MaxDueDate = T.MaxDueDate)
            )
      WHERE
         T.TestRevisionID = 3
         AND T.TestID = 2
      GROUP BY
         X.ExamineeID,
         X.Cnt
   ) Y ON E.ExamineeID = Y.ExamineeID

, (ExamineeID, Score) (ExamineeID, DueDate) . , . , ( ) , , . Score, , , max DueDate , , . , SQL 2000.

. , , CROSS APPLY ROW_NUMBER(), , , .

  • + = CROSS APPLY.
  • + : = ROW_NUMBER() .
  • = / ( ).

, SQL 2000, , , . , .

- , , , , . , , , , DDL, , , , .

, ExamineeTestBest ExamineeTestCurrent, ExamineeTest, . , , , , , .

+9

. .

  • count()
  • TOP (1) ORDER BY Score DESC
  • TOP (1) ORDER BY DateDue DESC

3 .
, 3 .

3 . , , . :

create function dbo.topexaminee_byscore(@ExamineeID int)
returns table
as
return (
  SELECT top (1)
    ExamineeTestID as bestExamineeTestID,
    Score as bestScore,
    DateDue as bestDateDue,
    TimeCommitted as bestTimeCommitted
  FROM exam.ExamineeTest
  WHERE (ExamineeID = @ExamineeID) AND (TestRevisionID = 3) AND (TestID = 2)
  ORDER BY Score DESC
)

- , . , . , :

select bestExamineeTestID, bestScore, bestDateDue, bestTimeCommitted
from (
  SELECT
    ExamineeTestID as bestExamineeTestID,
    Score as bestScore,
    DateDue as bestDateDue,
    TimeCommitted as bestTimeCommitted,
    row_number() over (partition by ExamineeID order by Score DESC) as takeme
  FROM exam.ExamineeTest
  WHERE (TestRevisionID = 3) AND (TestID = 2)
) as foo
where foo.takeme = 1

ORDER BY DateDue DESC , select ed.

.

/ /, . .

+4

, , "bestTest" . WHERE ORDER BY.

"bestNewTest". "currentTeest".

, 8 . . , , , .

+1

CTE OUTER APPLY.

;WITH testScores AS
(
    SELECT ExamineeID, ExamineeTestID, Score, DateDue, TimeCommitted
    FROM exam.ExamineeTest
    WHERE TestRevisionID = 3 AND TestID = 2
)
SELECT ExamineeID, LastName, FirstName, Email, total.Attempts,
       bestTest.*, currentTest.*
FROM exam.Examinee
LEFT OUTER JOIN
(
    SELECT ExamineeID, COUNT(ExamineeTestID) AS Attempts
    FROM testScores
    GROUP BY ExamineeID
) AS total ON exam.Examinee.ExamineeID = total.ExamineeID
OUTER APPLY
(
    SELECT TOP 1 ExamineeTestID, Score, DateDue, TimeCommitted
    FROM testScores
    WHERE exam.Examinee.ExamineeID = t.ExamineeID
    ORDER BY Score DESC
) AS bestTest (bestExamineeTestID, bestScore, bestDateDue, bestTimeCommitted)
OUTER APPLY
(
    SELECT TOP 1 ExamineeTestID, Score, DateDue, TimeCommitted
    FROM testScores
    WHERE exam.Examinee.ExamineeID = t.ExamineeID
    ORDER BY DateDue DESC
) AS currentTest (currentExamineeTestID, currentScore, currentDateDue, 
                  currentTimeCommitted)
0

More articles:


All Articles