时间戳

首页 > 解决方案 > 检索集合整数大于聚合关系计数的行

mysql - 检索集合整数大于聚合关系计数的行

问题描述

在这方面遇到了一些麻烦，离得很近但并不完全在那里。我查看了其他答案，但似乎没有一个完全捕捉到这一点。

一个图书馆群有一个共享系统，它有多个位置，最终目标是找到一个有库存的图书列表。每个图书馆成员仅是一个位置的成员，因此尽管系统是共享的，但需要比较的库存和后续结账都与给定位置相关联。

相关表格如下：

book_inventory (id, book_id, location_id, quantity)
membership (id, location_id, user_id)
book_checkout(id, membership_id, book_id, date_from, date_to)

到目前为止，这是我想出的（将“：locationid”替换为应该查找的位置的id），但它忽略了COUNT（）返回null的行。如此有效地，它返回所有有库存且当前已签出一本或多本的书籍。它需要退回这本书以及任何有库存但没有签出任何副本的书籍。

SELECT b.book_id, b.quantity FROM book_inventory b
LEFT JOIN book_checkout c ON c.book_id = b.book_id AND c.date_to IS NULL
INNER JOIN membership m ON m.id = c.membership_id AND m.location_id=:locationid
GROUP BY b.book_id HAVING b.quantity < COUNT(IFNULL(c.id, 0))

提前感谢您的任何见解！

标签： mysqlsqlmariadbmariadb-10.4

解决方案

COUNT(IFNULL(c.id, 0))应该是IFNULL(COUNT(c.id), 0)。如果没有人签出该书，则计数将为 NULL。

您还需要LEFT JOIN membership，因为您要加入另一个表LEFT JOINed。INNER JOIN如果没有c.membership_id加入，将导致从结果集中删除该行。

并且<应该是>找到库存比已签出数量更多的书籍。您还可以从中减去计数quantity以获取剩余的可用副本，并检查是否大于 0。

SELECT b.book_id, b.quantity, b.quantity - IFNULL(COUNT(c.id), 0) AS available
FROM book_inventory b
LEFT JOIN book_checkout c ON c.book_id = b.book_id AND c.date_to IS NULL
LEFT JOIN membership m ON m.id = c.membership_id AND m.location_id=:locationid
GROUP BY b.book_id
HAVING available > 0

推荐阅读