在SQL中加入GROUP BY和HAVING

Supposed that I have a schema like this one. 在SQL中加入GROUP BY和HAVING

假设我有这样的架构。

And now I want to retreive name, address and number of books checkout of the borrowers who have at least five book checkout.

现在我想要检查至少有五本书结账的借款人的姓名,地址和结账数量。

After a while, I came up with this solution as I'm fresher in Database:

过了一会儿,我想出了这个解决方案,因为我在数据库中更新鲜:

SELECT BR.Name, BR.Address,
   Checkout.NoOfBookCheckedOut
FROM Borrower BR, (
    SELECT CardNo, COUNT(BookId) AS 'NoOfBookCheckedOut'
    FROM Book_Loans
    WHERE DateOut IS NOT NULL
    GROUP BY CardNo
    HAVING COUNT(BookId) >= 5
 ) AS 'Checkout'
WHERE Checkout.CardNo = BR.CardNo

Is my solution correct? Is there any better way to optimize this query?

我的解决方案是否正确有没有更好的方法来优化此查询?

3 个解决方案

#1

You can use inner join here

您可以在此处使用内部联接

SELECT BR.Name, BR.Address,
   Checkout.NoOfBookCheckedOut
FROM Borrower BR
INNER JOIN (
    SELECT CardNo, COUNT(BookId) AS 'NoOfBookCheckedOut'
    FROM Book_Loans
    WHERE DateOut IS NOT NULL
    GROUP BY CardNo
    HAVING COUNT(BookId) >= 5
 ) Checkout
ON Checkout.CardNo = BR.CardNo

#2

I think you could simplify your query by joining both tables without the need for the derived table.

我认为您可以通过连接两个表来简化查询,而无需派生表。

SELECT
    BR.Name,
    BR.Address,
    COUNT(BL.BookId) as NoOfBookCheckedOut

FROM
    Borrower BR JOIN Book_Loans BL ON BR.CardNo = BL.CardNo

WHERE
    BL.DateOut IS NOT NULL

GROUP BY
    BR.Name,
    BR.Address

HAVING
    COUNT(BL.BookId) >= 5

#3

To optimize your query, make sure you have proper indexes in place on columns used in join and where clauses.

要优化查询,请确保在join和where子句中使用的列上具有适当的索引。

#1