MYSQL語法請教

mysql sql

badbayz 2016-06-15 13:12:14 ‧ 2424 瀏覽

分享至

如圖資料庫，以下是我個人想做個收支報表語法

SELECT
	DATE_FORMAT(`data`, '%Y-%m') 日期,
	SUM(CASE WHEN type IN ('現金提','金融卡') THEN cashout ELSE 0 END) ATM提領,
	SUM(CASE WHEN type = '中信卡' THEN cashout ELSE 0 END) 中國信託信用卡,
	SUM(CASE WHEN type = '跨行轉' AND note = '繳遠銀信用卡款' THEN cashout ELSE 0 END) 遠東商銀信用卡,
	SUM(CASE WHEN type = '委代扣' AND note = '台新銀行信用卡' THEN cashout ELSE 0 END) 台新銀行信用卡,
	SUM(CASE WHEN type = '電話費' THEN cashout ELSE 0 END) 電話及家裡網路,
	SUM(CASE WHEN type = '手續費' THEN cashout ELSE 0 END) 跨行手續費支出,
	balance 月結存,
	sum(cashout) 總支出,
	sum(cashin) 總收入
FROM
	banking
GROUP BY
	DATE_FORMAT(`data`, '%Y-%m')
ORDER BY
	`data` DESC

目前問題是balance 月結存這個我想撈每個月的最後一筆的balance欄位資料，就卡在這了= =，希望前輩們給些指點

看更多先前的討論...收起先前的討論...

badbayz iT邦新手 4 級 ‧ 2016-06-15 14:13:53 檢舉

SQL 測試範例檔案下載點 http://aquasss.com/banking.zip

badbayz iT邦新手 4 級 ‧ 2016-06-15 14:28:09 檢舉

SELECT
DATE_FORMAT(`data`, '%Y-%m') 日期,
SUM(CASE WHEN type IN ('現金提','金融卡') THEN cashout ELSE 0 END) ATM提領,
SUM(CASE WHEN type = '中信卡' THEN cashout ELSE 0 END) 中國信託信用卡,
SUM(CASE WHEN type = '跨行轉' AND note = '繳遠銀信用卡款' THEN cashout ELSE 0 END) 遠東商銀信用卡,
SUM(CASE WHEN type = '委代扣' AND note = '台新銀行信用卡' THEN cashout ELSE 0 END) 台新銀行信用卡,
SUM(CASE WHEN type = '電話費' THEN cashout ELSE 0 END) 電話及家裡網路,
SUM(CASE WHEN type = '手續費' THEN cashout ELSE 0 END) 跨行手續費支出,
balance 月初結存,
SUM(CASE WHEN id IN (SELECT MAX(id) FROM banking GROUP BY DATE_FORMAT(`data`, '%Y-%m')) THEN balance ELSE 0 END) 月底結存,
SUM(cashout) 總支出,
SUM(cashin) 總收入
FROM
banking
GROUP BY
DATE_FORMAT(`data`, '%Y-%m')
ORDER BY
`data` DESC

剛剛測試出來了@@，不過前輩們有更好的寫法讓我學習一下呢