对同一个表进行多个子查询的 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,但我似乎无法将它们全部拼凑在一起。
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 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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
如果您使用的是 SQL Server 2005 及更高版本,您可以使用如下的排名函数:
If you are using SQL Server 2005 and later, you can use a ranking function like so:
在 2005 年及更高版本中,OUTER APPLY 是您的朋友:
On 2005 and higher, OUTER APPLY is your friend:
使用:
如果学生尚未借出任何书籍,则
bookname
、bookauthor
和datecheckedout
将为 NULL。Use:
If the student has not checkout any books, the
bookname
,bookauthor
, anddatecheckedout
will be NULL.OMGPonies 的答案是一个很好的答案。为了便于阅读,我会使用通用表表达式来编写它:
对于最后 2 次结账,
c.rank = 1
可以替换为c.rank IN(1, 2)
,< code>BETWEEN 1 AND 3 最后 3 个,等等...The answer of OMGPonies is a good one. I would write it with Common Table Expressions for readability:
The
c.rank = 1
can be replaced byc.rank IN(1, 2)
for last 2 checkouts,BETWEEN 1 AND 3
for last 3, etc...希望这就是您正在寻找的,我知道这些情况的简单方法,
这就是我在遇到这样的问题时解决的方法,我认为这将是您案例的解决方案。
Hope this is what you are looking for, a simple way which I know for these case
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:
只需将连接添加到 TStudent 即可完成。
还剩下 1 个问题:如果某个学生有 2 个或更多具有相同最大结账日期的 Bookcheckouts,则每个学生会获得多个 Bookcheckouts。
为了避免重复:
我使用“BookName”作为第二个排序标准。 =>使用主键来使其成为真正的唯一标准。
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.
To avoid duplicates:
I used "BookName" as second ordering criterion. => Use primary key instead to make it a real unique criterion.
尝试
Try