如何将使用窗口函数的SQL后端到没有窗口函数的服务器上？

Question

我必须将MySQL 8查询返回到MySQL 5.7。其中一个子查询使用了以前版本不支持的窗口函数。子查询如下所示(简化)：

SELECT
    ROW_NUMBER()  OVER w AS `row_num`,
    MIN(`date`) OVER w AS `min_date`,
    MAX(`date`) OVER w AS `max_date`,
    `supplier_id`,
    `customer_id`
FROM `bookings`
WINDOW w AS (PARTITION BY `supplier_id`, `customer_id`)

如何在不使用“窗口”功能的情况下模仿这段代码？

Answer 1

您可以将其编写为：

SELECT (@rn := @rn + 1) as row_num, bb.min_date, bb.max_date,
       b.supplier_id,
       b.customer_id
FROM bookings b CROSS JOIN
     (SELECT @rn := 0) params LEFT JOIN
     (SELECT supplier_id, customer_id, MIN(date) as min_date, MAX(date) as max_date
      FROM bookings b
      GROUP BY supplier_id, customer_id
     ) bb
     USING (supplier_id, customer_id);

编辑：

哦，行号也在使用窗口。所以：

SELECT (@rn := if(sc = concat_os(':', supplier_id, customer_id), @rn + 1
                  if(@sc := concat_os(':', supplier_id, customer_id), 1, 1)
                 )
       ) as row_num,
       b.*
FROM (SELECT bb.min_date, bb.max_date,
             b.supplier_id,
             b.customer_id
      FROM bookings b CROSS JOIN
           (SELECT supplier_id, customer_id, MIN(date) as min_date, MAX(date) as max_date
            FROM bookings b
            GROUP BY supplier_id, customer_id
           ) bb
           USING (supplier_id, customer_id)
      ORDER BY supplier_id, customer_id
     ) b CROSS JOIN
     (SELECT @rn := 0, @sc := '') params