mysql - 跨所有行重复的列的最小值和最大值

Question

我有一个 orders 表，其中包含列 supplier_id 和 revenue:

每个供应商的总收入表如下:

SELECT
  supplier_id,
  SUM(revenue) as total_revenue
FROM
  orders
GROUP BY
  supplier_id

我想将收入从 0 标准化为 1:

因此，我需要所有供应商的所有总收入的min 和max。

如果可能，将这些值作为每行重复的附加列:

我如何在 MySQL 中执行此操作？

请注意，我想要所有供应商总收入的min 和max，而不是每个订单的最小和最大收入供应商。

Answer 1

你可以尝试使用JOIN

SELECT 
    t1.supplier_id,
    t1.total_revenue,
    t2.max_total_revenue,
    t2.min_total_revenue
FROM (
    SELECT 
        supplier_id,
        SUM(revenue) as total_revenue
    FROM orders
    GROUP BY supplier_id
) t1
LEFT JOIN (
    SELECT 
        MAX(total_revenue) max_total_revenue,
        MIN(total_revenue) min_total_revenue
    FROM (
        SELECT 
            supplier_id, 
            SUM(revenue) as total_revenue
        FROM orders
        GROUP BY supplier_id
    )
) t2 ON 1 = 1