mysql - 计数和子选择

我有一个选项，可以显示标题、书籍副本ID和名称。

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);

去工作。
我想显示每个名字的标题数目

最佳答案

我想这就是你要找的？

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还需要有资格。。。但我不知道是哪张桌子，所以你可以自己加进去。

关于mysql - 计数和子选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13637451/