MySQL/MariaSQL表格中前後2筆資料之計算(前後2筆之差)

在MySQL/MariaSQL中要做前後2筆資料之計算(以做到如同excel中的樞鈕分析表的效果,如下圖5)

Step1. 利用select建立2個暫存表格，並對每個表格的每一列做編號,第1個表格的編號是第2個表格相對應列的後推一號.

Step2. 將這2個表格做join

Ref:https://www.ptt.cc/bbs/Database/M.1194440834.A.15F.html

Ref:https://ithelp.ithome.com.tw/articles/10136053

範例如下:

1.資料表結構與資料如下圖1與圖2

2.建立暫存表格方法(如圖3)

表格1. @rownum為變數,使用SELECT @rownum:=1宣告...(因為要在select語法中宣告,而不使用set @rownum:=1)

SELECT @rownum:=@rownum+1 AS NO,SUM(adult) ,SUM(child),d FROM amount,(SELECT @rownum:=1) AS row WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d

表格2.

SELECT @rownum2:=@rownum2+1 AS NO,SUM(adult) ,SUM(child),d FROM amount,(SELECT @rownum2:=0) AS row WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d

*因為 GROUP by d了,d會是唯一的,所以不用再 d in (select....)

3.將2表格join,表格2的某欄位與表格1的某欄位做計算,如(表格2adult欄位-表格1adult欄位)計算出,每天剩餘量之差

如圖4,因此可以得到一些資訊,例如週末購買人數較多,所以為負,或是3/4~3/5,

成人口罩從每週2個調至每週3個,因此原本前2天要購買的人都保留這2天才開始購買

SELECT a.d,b.adult-a.adult,b.child-a.child from

(SELECT @rownum:=@rownum+1 AS rw,SUM(adult) AS adult,SUM(child) AS child,d FROM amount,(SELECT @rownum:=1) AS r WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d ORDER BY rw) AS a

,(SELECT @rownum2:=@rownum2+1 AS rw ,SUM(adult) AS adult,SUM(child) AS child,d FROM amount,(SELECT @rownum2:=0) AS r WHERE d IN (SELECT DISTINCT d FROM amount) GROUP BY d ORDER BY rw) AS b

WHERE a.rw=b.rw;

圖1:資料表結構

圖2:資料

圖3.建立2個暫存表格

圖四.結果

圖五.excel樞紐分析表做出一樣的結果