php - 如何找到Mysql中最后两个条目之间的差异

Question

我需要一个关于 MySQL 中最后两个条目差异之间的差异的解决方案

SELECT DATE_FORMAT(order_datetime,'%m/%Y') as date,
       SUM(order_total_after_tax) as number
FROM tbl_order 
WHERE status = 'Confirmed'
GROUP BY DATE_FORMAT(order_datetime,'%Y/%m')
Limit 2

输出 ：

date     number
02/2019 2345.01
03/2019 103751.05

但我需要一个确切的解决方案是我需要找到最后两个结果之间的差异

2345.01 -103751.05

结果：-101406.04 如何在 MySQL 中执行 Query 是什么？

标签： phpmysqlsql

Answer 1

一种方法是 a cross join，但它看起来像这样：

SELECT last.number - last_1.number
FROM (SELECT DATE_FORMAT(order_datetime,'%m/%Y') as date,
             SUM(order_total_after_tax) as number
      FROM tbl_order 
      WHERE status = 'Confirmed'
      GROUP BY DATE_FORMAT(order_datetime,'%Y/%m')
      ORDER BY date
      LIMIT 1
     ) as last CROSS JOIN
     (SELECT DATE_FORMAT(order_datetime,'%m/%Y') as date,
             SUM(order_total_after_tax) as number
      FROM tbl_order 
      WHERE status = 'Confirmed'
      GROUP BY DATE_FORMAT(order_datetime,'%Y/%m')
      ORDER BY date
      LIMIT 1 OFFSET 1
     ) last_1