mysql当月データおよび当月以前の累積データの取得
2490 ワード
sqlは以下の通り
日と週の累積%Y-%m-%d日統計%Y-%m月統計%Y-%u週統計は、時間の異なるフォーマットで行うことができます.
SELECT a.foodVariety foodVariety,c.total weight,a.month month,SUM(b.total) total
FROM( SELECT FOOD_VARIETY_ foodVariety,DATE_FORMAT(REGISTER_TIME_ , '%Y-%m') month,
REGISTER_TIME_ registerTime,SUM(NET_WEIGHT_) total,TYPE_ type FROM d_inout_stock
GROUP BY FOOD_VARIETY_,month) AS a
JOIN ( SELECT FOOD_VARIETY_ foodVariety ,DATE_FORMAT(REGISTER_TIME_, '%Y-%m') month
,SUM(NET_WEIGHT_) total
FROM d_inout_stock where GROUP BY foodVariety,month) AS b
ON a.foodVariety = b.foodVariety AND a.month >= b.month
JOIN ( SELECT FOOD_VARIETY_ foodVariety,DATE_FORMAT(REGISTER_TIME_, '%Y-%m') month
,SUM(NET_WEIGHT_) total
FROM d_inout_stock where GROUP BY foodVariety,month) AS c
ON a.foodVariety = c.foodVariety AND a.month = c.month
日と週の累積%Y-%m-%d日統計%Y-%m月統計%Y-%u週統計は、時間の異なるフォーマットで行うことができます.