SQL SELECT 对同一个表的多个子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3907354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT with multiple subqueries to same table
提问by Jon Smock
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:
我一遍又一遍地使用相同的 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
(For the sake of this example, please ignore the fact that TBookCheckouts should probably be split into TCheckouts and TBooks)
（为了这个例子，请忽略 TBookCheckouts 可能应该拆分为 TCheckouts 和 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...).
我想说明的是：我倾向于对同一个表中的列有很多子查询。我还倾向于需要按日期对这些子查询表进行排序以获取最新记录，因此它不像执行 LEFT JOIN 那样简单（至少对我而言）。但是请注意，除了返回哪个字段之外，我基本上执行了 3 次相同的子查询。SQL Server 可能足够聪明来优化它，但我认为不是（我绝对需要更好地阅读执行计划......）。
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 doing 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.
我已经研究过从派生表进行 LEFT JOIN，可能包含 ROW_NUMBER() 和 PARTITION BY，但我似乎无法将它们拼凑在一起。
采纳答案by Thomas
If you are using SQL Server 2005 and later, you can use a ranking function like so:
如果您使用的是 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
回答by A-K
On 2005 and higher, OUTER APPLY is your friend:
在 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
回答by OMG Ponies
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 datecheckedoutwill be NULL.
如果学生还没有签任何书籍，对bookname，bookauthor和datecheckedout将是NULL。
回答by thomaspaulb
The answer of OMGPonies is a good one. I would write it with Common Table Expressions for readability:
OMGPonies 的答案是一个很好的答案。我会用通用表表达式来编写它以提高可读性：
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 = 1can be replaced by c.rank IN(1, 2)for last 2 checkouts, BETWEEN 1 AND 3for last 3, etc...
该c.rank = 1可以被替换c.rank IN(1, 2)为去年2个检出，BETWEEN 1 AND 3为去年3，等...
回答by Dennis
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.
当我遇到这样的问题时，这就是我解决的方法，我认为这将是您的情况的解决方案。
回答by Noel Abrahams
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
回答by p.campbell
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
回答by nang
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.
只需将连接添加到 TStudent 即可。还剩下 1 个问题：如果一个学生有 2 个或更多 Bookcheckouts 且具有相同的最大结帐日期，则每个学生将获得多个 BookCheckouts。
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.
我使用“BookName”作为第二个排序标准。=> 改用主键使其成为真正的唯一标准。
