我正在尝试获取会员ID，发行书籍数量最高和最低的人的会员名称

Question

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;

Answer 1

一种方法在这里使用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;

Answer 2

尝试在select部分添加表前缀:

SELECT issue.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;

Answer 3

如果存在定义不明确的列，可以考虑对列名进行限定。在本例中为member_id，例如

SELECT member.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;