SELECT с несколькими подзапросами в одну таблицу
Я использую один и тот же шаблон SQL снова и снова, и я знаю, что должен быть лучший способ, но у меня возникают проблемы с его объединением. Вот простая версия шаблона, где я отвлекаю информацию о студенте и последнюю книгу, которую они проверяли, если таковая существует:
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
(Для этого примера, пожалуйста, проигнорируйте тот факт, что TBookCheckouts, вероятно, следует разделить на TCheckouts и TBooks)
Что я пытаюсь проиллюстрировать: у меня, как правило, много подзапросов для столбцов из одной таблицы. Мне также приходится сортировать те подклассифицированные таблицы по дате, чтобы получить самую последнюю запись, поэтому она не так проста (по крайней мере для меня), как делает LEFT JOIN. Обратите внимание, что, за исключением того, какое поле возвращается, я по существу делаю тот же подзапрос 3 раза. SQL Server может быть достаточно умным, чтобы оптимизировать это, но я не думаю (мне обязательно нужно лучше читать планы выполнения...).
Хотя могут быть преимущества для его структурирования таким образом (иногда это становится более читаемым, если у меня есть множество подзапросов и подтабликов), похоже, что это не особенно эффективно.
Я изучил выполнение LEFT JOIN из производной таблицы, возможно, включая ROW_NUMBER() и PARTITION BY, но я просто не могу собрать все это вместе.
Ответы
Ответ 1
Если вы используете SQL Server 2005 и более поздние версии, вы можете использовать функцию ранжирования следующим образом:
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
Ответ 2
В 2005 году и выше, OUTER APPLY - ваш друг:
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
Ответ 3
Использование:
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
Если студент не проверяет какие-либо книги, bookname, bookauthor и datecheckedout будут иметь значение NULL.
Ответ 4
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
Ответ 5
Если вы хотите использовать выражение Common Table Expression, вы можете выполнить следующий запрос. Это не принесет вам ничего, в данном случае, но для будущего:
;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
Ответ 6
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
Просто добавьте соединение в TStudent, и все готово.
Осталось 1 проблема: вы получаете несколько экземпляров BookCheckouts для каждого учащегося, если есть 2 или более книжных чеков для ученика с той же максимальной датой проверки.
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
Чтобы избежать дублирования:
select *
from (
select *, RANK() over (partition by StudentID order by CheckoutDate desc,BookName) rnk
from BookCheckout bc1) x
where rnk=1
Я использовал "BookName" в качестве второго критерия упорядочивания. = > Вместо этого используйте первичный ключ, чтобы сделать его реальным уникальным критерием.
Ответ 7
Ответ OMGPonies - хороший. Я бы написал его с помощью Common Table Expressions для чтения:
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
c.rank = 1 можно заменить на c.rank IN(1, 2) для последних двух проверок, BETWEEN 1 AND 3 для последних 3 и т.д.
