SELECT with multiple subqueries to same table
I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information and the last book they checked out, if one exists:
SELECT TStudents.*,
BookName = (SELECT TOP 1 BookName
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC),
BookAuthor = (SELECT TOP 1 BookAuthor
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC),
BookCheckout = (SELECT TOP 1 DateCheckedOut
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC)
FROM TStudents
(For the sake of this example, please ignore the fact that TBookCheckouts should probably be split into TCheckouts and TBooks)
What I'm trying to illustrate: I tend to have a lot of subqueries for columns from the same table. I also tend to need to sort those subqueried tables by a date to get the most recent record, so it's not quite as simple (at least to me) as doing a LEFT JOIN. Notice, though, that except for which field is being returned, I'm essentially doing the same subquery 3 times. SQL Server may be smart enough to optimize that, but I'm thinking not (I definitely need to get better at reading execution plans...).
While there might be advantages to structuring it this way (sometimes this ends up being more readable, if I have tons of subqueries and sub-tables), it doesn't seem like this is particularly efficient.
I've looked into d开发者_Python百科oing a LEFT JOIN from a derived table, possibly incorporating a ROW_NUMBER() and PARTITION BY, but I just can't seem to piece it all together.
If you are using SQL Server 2005 and later, you can use a ranking function like so:
With LastCheckout As
(
Select StudentId, BookName, BookAuthor, DateCheckedOut
, Row_Number() Over ( Partition By StudentId Order By DateCheckedOut Desc) As CheckoutRank
From TBookCheckouts
)
Select ..., LastCheckout.BookName, LastCheckout.BookAuthor, LastCheckout.DateCheckedOut
From TStudents
Left Join LastCheckout
On LastCheckout.StudentId = TStudents.StudentId
And LastCheckout.CheckoutRank = 1
On 2005 and higher, OUTER APPLY is your friend:
SELECT TStudents.*,
t.BookName ,
t.BookAuthor ,
t.BookCheckout
FROM TStudents
OUTER APPLY(SELECT TOP 1 s.*
FROM TBookCheckouts AS s
WHERE s.StudentID = TStudents.ID
ORDER BY s.DateCheckedOut DESC) AS t
Use:
SELECT s.*,
x.bookname,
x.bookauthor,
x.datecheckedout
FROM TSTUDENTS s
LEFT JOIN (SELECT bc.studentid,
bc.bookname,
bc.bookauthor,
bc.datecheckedout,
ROW_NUMBER() OVER(PARTITION BY bc.studentid
ORDER BY bc.datecheckedout DESC) AS rank
FROM TSBOOKCHECKOUTS bc) x ON x.studentid = s.id
AND x.rank = 1
If the student has not checkout any books, the bookname
, bookauthor
, and datecheckedout
will be NULL.
Hope this is what you are looking for, a simple way which I know for these case
SELECT (SELECT TOP 1 BookName
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC)[BOOK_NAME],
(SELECT TOP 1 BookAuthor
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC)[BOOK_AUTHOR],
(SELECT TOP 1 DateCheckedOut
FROM TBookCheckouts
WHERE StudentID = TStudents.ID
ORDER BY DateCheckedOut DESC)[DATE_CHECKEDOUT]
This is how I solved when I faced problem like this, I think this would be the solution for your case.
If you wanted to get into using a Common Table Expression, you could the following query. It doesn't gain you anything, in this case, but for future:
;with LatestBookOut as
(
SELECT C.StudentID, BookID, Title, Author, DateCheckedOut AS BookCheckout
FROM CheckedOut AS C
INNER JOIN ( SELECT StudentID,
MAX(DateCheckedOut) AS DD
FROM Checkedout
GROUP BY StudentID) StuMAX
ON StuMAX.StudentID = C.StudentID
AND StuMAX.DD = C.DateCheckedOut
)
SELECT B.BookCheckout,
BookId,
Title,
Author,
S.*
FROM LatestBookOut AS B
INNER JOIN Student AS S ON S.ID = B.StudentID
create table BookCheckout(StudentID int, CheckoutDate date, BookName varchar(10))
insert into BookCheckout values (1, '1.1.2010', 'a');
insert into BookCheckout values (1, '2.1.2010', 'b');
insert into BookCheckout values (1, '3.1.2010', 'c');
insert into BookCheckout values (2, '1.1.2010', 'd');
insert into BookCheckout values (2, '2.1.2010', 'e');
select *
from BookCheckout bc1
where CheckoutDate = (
Select MAX(CheckoutDate)
from BookCheckout bc2
where bc2.StudentID= bc1.StudentID)
StudentID CheckoutDate BookName
2 2010-01-02 e
1 2010-01-03 c
Just add the join to TStudent and you are done. There is 1 problem left: You get multiple BookCheckouts per student if there are 2 or more Bookcheckouts for a Student with the same, max checkout date.
select s.*, LastBookCheckout.*
from TStudent s,
(select *
from BookCheckout bc1
where CheckoutDate = (
Select MAX(CheckoutDate)
from BookCheckout bc2
where bc2.StudentID= bc1.StudentID)) LastBookCheckout
where s.ID = LastBookCheckout.StudentID
To avoid duplicates:
select *
from (
select *, RANK() over (partition by StudentID order by CheckoutDate desc,BookName) rnk
from BookCheckout bc1) x
where rnk=1
I used "BookName" as second ordering criterion. => Use primary key instead to make it a real unique criterion.
The answer of OMGPonies is a good one. I would write it with Common Table Expressions for readability:
WITH CheckoutsPerStudentRankedByDate AS (
SELECT bookname, bookauthor, datecheckedout, studentid,
ROW_NUMBER(PARTITION BY studentid ORDER BY datecheckedout DESC) AS rank
FROM TSBOOKCHECKOUTS
)
SELECT
s.*, c.bookname, c.bookauthor, c.datecheckedout
FROM TSTUDENTS AS s
LEFT JOIN CheckoutsPerStudentRankedByDate AS c
ON s.studentid = c.studentid
AND c.rank = 1
The c.rank = 1
can be replaced by c.rank IN(1, 2)
for last 2 checkouts, BETWEEN 1 AND 3
for last 3, etc...
Try
;WITH LatestCheckouts
AS
(
SELECT DISTINCT
A.StudentID
, A.BookName
, A.BookAuthor
, A.DateCheckedOut
FROM TBookCheckouts A
INNER JOIN
(
SELECT StudentID
, DateCheckedOut = MAX(DateCheckedOut)
FROM TBookCheckouts
GROUP BY
StudentID
) B
ON A.StudentID = B.StudentID
AND A.DateCheckedOut = B.DateCheckedOut
)
SELECT students.*
, BookName = checkouts.BookName
, BookAuthor = checkouts.BookAuthor
, BookCheckout = checkouts.DateCheckedOut
FROM TStudents students
LEFT JOIN
LatestCheckouts checkouts
ON students.ID = checkouts.StudentID
精彩评论