甲骨文关系数据库

Question

我正在尝试实现一个代码，该代码可以过滤图书馆中借阅书籍多于DVD的客户数量。该查询应显示客户名称、总图书借出数和 DVD 借出总数。 带有所有表格的 ERD

这是我的代码，用于计算总图书借出次数和DVD借出总数。

SELECT C.CUSTOMER_FIRSTNAME||' '|| C.CUSTOMER_LASTNAME "CUSTOMER 
NAME",COUNT(*)"TOTAL CHECKOUTS"
FROM  CATALOG_ITEM CI
JOIN PHYSICAL_ITEM PI
ON CI.CATALOG_ITEM_ID = PI.CATALOG_ITEM_ID
JOIN TRANSACTION TI
ON PI.PHYSICAL_ITEM_ID = TI.PHYSICAL_ITEM_ID
JOIN LIBRARY_CARD LC
ON TI.LIBRARY_CARD_ID = LC.LIBRARY_CARD_ID
JOIN CUSTOMER C
ON LC.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_FIRSTNAME||' '|| C.CUSTOMER_LASTNAME,CI.TYPE;

Answer 1

我终于解决了我的问题。使用 COUNT(CASE WHEN 字段 = 'record' 然后 1 结束(，我能够指定要在每列上计数的记录。

SELECT C.CUSTOMER_FIRSTNAME ||' '||C.CUSTOMER_LASTNAME "CUSTOMER NAME",
COUNT(CASE WHEN CI.TYPE = 'BOOK' THEN 1 END)"TOTAL BOOK CHECKOUTS",
COUNT(CASE WHEN CI.TYPE = 'DVD' THEN 1 END)"TOTAL DVD CHECKOUTS"
FROM CATALOG_ITEM CI
JOIN PHYSICAL_ITEM PI
ON CI.CATALOG_ITEM_ID = PI.CATALOG_ITEM_ID
JOIN TRANSACTION TI
ON PI.PHYSICAL_ITEM_ID = TI.PHYSICAL_ITEM_ID
JOIN LIBRARY_CARD LI
ON TI.LIBRARY_CARD_ID = LI.LIBRARY_CARD_ID
JOIN CUSTOMER C
ON LI.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_FIRSTNAME ||' '||C.CUSTOMER_LASTNAME
HAVING COUNT(CASE WHEN CI.TYPE = 'BOOK' THEN 1 END) > COUNT(CASE WHEN CI.TYPE = 
'DVD' THEN 1 END)
ORDER BY C.CUSTOMER_FIRSTNAME ||' '||C.CUSTOMER_LASTNAME;

任何替代解决方案将不胜感激。