gpt4 book ai didi

没有子查询的sql server最大值计数

转载 作者:行者123 更新时间:2023-12-04 14:18:54 24 4
gpt4 key购买 nike

我想编写一个 T-SQL 查询,它不仅返回最大值,而且返回具有最大值的行数。一定有比我想出的更好的方法

 --wrong way 
select LibraryBranchId, max(daysCheckedOut), count(daysCheckedOut)
from books group by LibraryBranchId

LibraryBranchId Expr1 Expr2
----------------------------------
1 100 398503 (WRONG!)
2 75 94303 (WRONG!)
3 120 103950 (WRONG!)

我可以通过 INNER JOINing 一个子查询来正确地做到这一点,但这似乎很浪费
 --right way, but seems WAY too long
select LibraryBranchId,max(daysCheckedOut),count(daysCheckedOut)
from books inner join
( select LibraryBranchId, max(daysCheckedOut) as maxDaysCheckedOut
from books group by LibraryBranchId ) as maxbooks
on books.LibraryBranchId=maxbooks.LibraryBranchId
where daysCheckedOut=maxDaysCheckedOut
group by LibraryBranchId

LibraryBranchId Expr1 Expr2
----------------------------------
1 100 17 (RIGHT!)
2 75 11 (RIGHT!)
3 120 2 (RIGHT!)

那么有没有一种方法像查询 #1 一样简单,但返回查询 #2 中的正确结果?

微软 SQL Server 2000

编辑:我第一次尝试输入时错过了上面两个重要的 GROUP BY,我已经添加了它们
编辑:假装 Cade Roux 写的版本是我写的

最佳答案

我认为是对的:

SELECT maxbooks.LibraryBranchId, maxbooks.maxDaysCheckedOut, count(*)
FROM books
INNER JOIN (
SELECT LibraryBranchId, max(daysCheckedOut) AS maxDaysCheckedOut
FROM books
GROUP BY LibraryBranchId
) AS maxbooks
ON books.LibraryBranchId = maxbooks.LibraryBranchId
AND books.daysCheckedOut = maxbooks.maxDaysCheckedOut
GROUP BY maxbooks.LibraryBranchId, maxbooks.maxDaysCheckedOut

我认为没有更简单的方法 - 从概念上讲,它是两个集合的交集。关于分支的元组集和满足它的元组集。

关于没有子查询的sql server最大值计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1121021/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com