SQL是否在内连接中添加更多列？

xienkqul 于 2022-09-21 发布在 PostgreSQL

关注(0)|答案(4)|浏览(133)

我有两张table：
客户||
-|-|
Id|名称|order_id

订单|
-|-|
Id|Customer_id|日期|金额

我想得到每个客户的第一个订单，所以我在日期使用了inner join和min。不过，我也想要所有的订单列。

我的SQL查询是

SELECT 
    customers.id, customers.name, 
    MIN(orders.date) 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY   
    customers.id;

当我尝试从Orders表添加更多列时，如下所示：

SELECT 
    customers.id, customers.name, 
    MIN(orders.date), orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY 
    customers.id;

我收到一个错误

错误：列“orders.id”必须出现在GROUP BY子句中或在聚合函数中使用

postgresql

来源：https://stackoverflow.com/questions/73697635/sql-add-more-columns-in-inner-join

4条答案

按热度按时间

ylamdve61#

使用不带group by的distinct on。您可以查看this，以了解详细信息。

SELECT distinct on (customers.id)
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
ORDER BY BY 
    customers.id, orders.date;

赞(0）回复(0）举报 2022-09-21

gr8qqesn2#

select oo.*,c.*,fo.* from 
(select o.customer_id,min(o.date)order_date 
from orders o
group by o.customer_id)fo
inner join orders oo on oo.customer_id=fo.customer_id and oo.date=fo.order_date
inner join customer c on c.id=oo.customer_id

我想这会对你有帮助的。在SQL Group by子句中，只能选择分组列和聚合列。对于您的情况，您必须添加额外的连接，以使用Customer_id和Minimun Order_Date从Order表中获取整个数据

赞(0）回复(0）举报 2022-09-21

bis0qfac3#

那就不要在主句中用min。

min(orders.date)表示您希望获取一列的最小日期。您可以在加入前先过滤订单

SELECT 
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id 
INNER JOIN (
        SELECT customer_id, Min(date) as MinDate 
        FROM orders 
        GROUP BY customer_id
    ) filtered_order 
        ON filtered_order.customer_id = customers.id 
        AND filtered_order.MinDate = orders.date

赞(0）回复(0）举报 2022-09-21

wr98u20j4#

您可以编写以下查询

SELECT customers.id, customers.name, min(orders.date) 
FROM customers
FULL JOIN orders ON customers.id=orders.customer_id
GROUP BY customers.id;

赞(0）回复(0）举报 2022-09-21

我来回答

SQL是否在内连接中添加更多列？

4条答案

相关问题

热门标签

最新问答