运行此代码时，该member表具有member_name, member_id并且该issue表具有member_id, issue_id, book_no列我收到错误
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> SELECT member_id AS mem_id, member_name,
MAX(COUNT(issue.member_id)), MIN(COUNT(issue.member_id))
FROM issue INNER JOIN member ON issue.member_id = member.member_id;
一种方法ROW_NUMBER在这里使用：
WITH cte AS (
SELECT m.member_id, m.member_name, COUNT(i.member_id) AS num_books,
ROW_NUMBER() OVER (ORDER BY COUNT(i.member_id)) rn_least,
ROW_NUMBER() OVER (ORDER BY COUNT(i.member_id) DESC) rn_greatest
FROM member m
LEFT JOIN issue i ON i.member_id = m.member_id
GROUP BY m.member_id, m.member_name
)
SELECT member_id, member_name, num_books
FROM cte
WHERE rn_least = 1 OR rn_greatest = 1;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句