mysql - 计数和子选择

Question

我有一个显示标题、bookcopiesid 和名称的选择。

select 
    books.title, borrow.bookcopiesid, users.name, usersid,library_locations.name, checkout_date, return_date, due_date 
FROM 
    books,  borrow,users, library_locations, userlib 
WHERE 
    library_locations.id = userlib.libid 
AND 
    userlib.userid = users.id 
AND 
    borrow.bookcopiesid = books.bookid 
AND 
    borrow.usersid = users.id and return_date is not null ;

我怎样才能得到类似的东西

SELECT title, COUNT(*) as count 
FROM (
    SELECT books.title, borrow.bookcopiesid, users.name, usersid,library_locations.name, checkout_date, return_date, due_date 
    FROM books,  borrow,users, library_locations, userlib 
    WHERE library_locations.id = userlib.libid and userlib.userid = users.id and borrow.bookcopiesid = books.bookid and borrow.usersid = users.id and return_date is not null) 
GROUP BY title 
ORDER BY count DESC); 

工作。

我正在尝试显示每个名称的标题数

Answer 1

我想这就是您要找的东西？

SELECT
    books.title,
    COUNT(*) as count
FROM
    books, 
    borrow,
    users, 
    library_locations,
    userlib 
WHERE 
    library_locations.id = userlib.libid 
    AND userlib.userid = users.id 
    AND borrow.bookcopiesid = books.bookid 
    AND borrow.usersid = users.id 
    AND return_date is not null
GROUP BY books.title 
ORDER BY COUNT(*) DESC;

不需要子查询；您只需要限定 SELECT 和 GROUP BY 子句中的列(就像您在 WHERE 子句中所做的那样)。

此外，return_date 需要限定......但我不知道来自哪个表，所以你可以自己添加。