为每个不同的客户选择除最大订单之外的所有订单

WITH ranked_orders AS (
    SELECT 
        customer_id, 
        order_id, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT 
    customer_id, 
    order_id, 
    amount
FROM ranked_orders
WHERE rn > 1;

orders = [
    {'customer_id': 1, 'order_id': 101, 'amount': 100},
    {'customer_id': 1, 'order_id': 102, 'amount': 200},
    {'customer_id': 2, 'order_id': 201, 'amount': 150},
    {'customer_id': 2, 'order_id': 202, 'amount': 300},
]

from collections import defaultdict

customer_orders = defaultdict(list)

for order in orders:
    customer_orders[order['customer_id']].append(order)

result = []

for customer_id, orders in customer_orders.items():
    max_order = max(orders, key=lambda x: x['amount'])
    result.extend([order for order in orders if order != max_order])

print(result)