在PostgreSQL中使用内联接时出错？
在PostgreSQL中使用内联接时出错？
提问于 2018-08-14 12:58:09
因为我是PostgreSQL的新手，所以我使用PostgreSQL sample database来学习和练习PostgreSQL。我的问题陈述是找到在4月10日至13日期间租用电影的前15名支出最高的客户的付款完成了吗？我的疑问如下：
SELECT customer.first_name,payment.amount
FROM customer
INNER JOIN payment ON customer.customer_id = payment.customer_id
WHERE customer_id IN (SELECT customer_id
FROM (
SELECT customer_id, SUM(amount)
FROM payment
WHERE extract(month from payment_date) = 4
AND extract(day from payment_date) BETWEEN 10 AND 13
GROUP BY customer_id
HAVING SUM(amount) > 5
ORDER BY SUM(amount) DESC
LIMIT 15
) AS top_fifteen);我收到以下错误：
relation "customer" does not exist我不确定为什么我会在这里得到这个异常，有人能在这里帮助我减轻这个问题吗？任何线索都会受到高度赞赏。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-14 13:11:28
使用如下所示的表别名并尝试
SELECT c.first_name,p.amount
FROM customer c
INNER JOIN payment p
ON c.customer_id = p.customer_id
WHERE c.customer_id IN
(
SELECT customer_id FROM
(
SELECT customer_id, SUM(amount)
FROM payment
WHERE extract(month from payment_date) = 4
AND extract(day from payment_date) BETWEEN 10 AND 13
GROUP BY customer_id
HAVING SUM(amount) > 5
ORDER BY SUM(amount) DESC
LIMIT 15) AS top_fifteen);我将您的查询重写如下
select * from
(
select c.first_name,c.last_name,
sum(amount) as pay_amount,
count(p.customer_id) pay_num from payment p
inner join Customer c on p.customer_id=c.customer_id
where extract(month from p.payment_date) = 4
AND extract(day from p.payment_date) BETWEEN 10 AND 13
group by c.first_name,c.last_name,p.customer_id
) T
order by pay_amount desc
Limit 15页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51833981
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号