基於ACCESS和ASP的SQL多個表查詢與計算統計代碼

標籤：資料庫   信用卡   sql   多個表查詢串連   access   

最近在寫幾個關於“Project - Subitem - Task”的管理系統，說是系統還是有點誇大了，基本就是一個多表查詢調用和insert、update的資料庫操作，只是出現不少計算和統計的問題，使得SQL顯得複雜。所以，有必要在一個階段任務完成後，做一次總結，把一些測試過程中的SQL代碼做總結，以防以後用到又忘記了，也歡迎各位DB碼農一起吐槽。

這幾個月陸續寫了幾個系統，最後一個系統是信用卡管理系統，也是SQL累積到較複雜的階段，以這個為例子來整理這段時間以來的基於ACCESS和ASP的SQL多個表查詢與計算統計代碼是比較合適的。

PS：因為涉及到自己的銀行資訊安全，隱去了一些資訊，但額度、賬單金額等還是真實的，是不是很剁手。

回到正題，信用卡較多，希望用較好的方案統計信用卡賬單、額度、還款等等資訊，而又不想利用如“51信用卡管理”之類的軟體，讓人家擷取我的財務資訊，最好就是自己弄個資料庫來解決，放到自己的雲主機或虛擬伺服器上去，手機也是一樣訪問的。這是做這個簡單系統的原因，很多資料並不是一成而就的，而是在用的過程中不斷添加需要統計到的資訊而改進代碼。

建立一個CreditCard的資料庫，我採用的是ACCESS2007，副檔名accdb。總共建立三個表，分別是credit（信用卡資訊）、Bill（賬單）、Payment（還款），盡量以最基礎的資料錄入，減少冗餘，更多的計算和統計寫在ASP上。

正如上面的CreditCard Information的頁面，看看SQL的語句：

select Credit.ID as 序號, Credit.CCno as 索引, Credit.Bank as 銀行,Credit.brand as 品牌, Credit.Card as 卡號, Format(Credit.limit,'Currency') as 額度,Credit.Belong as 持卡人, Credit.billday as 賬單日, Credit.consumeday as 消費日,Count(Bill.CCno) as 賬單數, Format(Credit.templimit,'Currency') as 臨時額度,Format(Credit.unsettled,'Currency') as 未出賬單金額,Format((Credit.limit+Credit.templimit-Credit.unsettled),'Currency') as 剩餘額度,Credit.newtime as 更新時間FROM Credit LEFT JOIN Bill ON Credit.CCno = Bill.CCnoGROUP BY Credit.ID, Credit.CCno, Credit.Bank, Credit.brand, Credit.Card,Credit.limit, Credit.Belong, Credit.billday, Credit.consumeday,Credit.templimit, Credit.unsettled, Credit.newtimeorder by Credit.ID

Select語句中在as之後的名稱可作為欄位（列）的名；對於現實金額計數法和貨幣符號，則是用Format（欄位或運算式，‘Currency‘）得到￥12345.67格式；SQL中是可以用運算式的，如”額度+臨時額度-未出賬單金額“可以得到剩餘額度等。由於用到Count（統計）Bill表中的對應銀行的賬單數量，所以用到了兩個表的連結，即【Credit】和【Bill】兩個表。兩個錶鏈接還是比較容易的，採用LEFT、INNER、RIGHT都可以實現Count，但其他用法需求裡，這三個錶鏈接方式是有區別的，特別在三張表以上的連結的應用就很重要了。

PS：隱去索引、銀行、卡號等。

Bill Payment是三個表的同時調用，目的是一覽無餘地看到信用卡的賬單、還款時間、還款情況等方面的資訊，且看SQL語句：

select Bill.ID as 序號, Bill.CCno as 索引, Credit.bank as 銀行, Credit.Card as 卡號,Bill.BLno as 賬單編號, Month(Bill.billdate) as 賬單月份, Bill.paydate as 最後還款日期,Format(Bill.balance,'Currency') as 本期賬單金額, Max(Payment.Paydate) as 還款時間,IIf(Max(Payment.Paydate) Is Not Null,IIf(Bill.balance-SUM(Payment.balance)<=0,'結清賬單','部分還款,餘'&Bill.paydate-date()&'天'),IIF(Bill.paydate-date()>0,Bill.paydate-date()&'天','逾期:'&date()-Bill.paydate&'天')) as 剩餘還款時間, Format(SUM(Payment.balance),'Currency') as 還款總金額,Format(IIf(Bill.balance-SUM(Payment.balance)<=0,0,Bill.balance-SUM(Payment.balance)),'Currency') as 未還款金額,IIf(Bill.balance-SUM(Payment.balance)<=0,'√','') as 賬單完成FROM (Credit INNER JOIN Bill ON Credit.CCno = Bill.CCno)LEFT JOIN Payment ON Bill.BLno = Payment.BLnoGROUP BY Bill.ID, Bill.CCno, Credit.bank, Credit.Card, Bill.BLno, Bill.billdate, Bill.paydate, Bill.balanceHAVING(Year(Bill.billdate)='2014' AND Month(Bill.billdate)='12')order by Bill.ID Desc

Select語句中有Month（時間類型欄位）來擷取月份，也就有year（時間類型欄位）、week（）等擷取相關時間資訊；用Max、Min來擷取一個欄位（列）中最大或最小的數值，這個數值可以是數字或時間類型。

這段Select語句中涉及到了一個if語句，句式：IIF（欄位或運算式，運算式為真的結果，運算式為假的結果），運算式可以再巢狀表格達式。對上面”剩餘還款時間“這個欄位的if語句進行解析。

IIf(Max(Payment.Paydate) Is Not Null,IIf(Bill.balance-SUM(Payment.balance)<=0,‘結清賬單‘,‘部分還款,餘‘&Bill.paydate-date()&‘天‘),IIF(Bill.paydate-date()>0,Bill.paydate-date()&‘天‘,‘逾期:‘&date()-Bill.paydate&‘天‘))as 剩餘還款時間

第一個if層（紅色），IIf(Max(Payment.Paydate) Is Not Null，運算式為真的結果，運算式為假的結果），當【Payment】表中的Paydate（這裡指還款時間）不是空值的時候（藍色），也就是這時有【Bill】表中的某月的賬單，但這個賬單在表【Payment】有值（不是空值），那麼執行”運算式為真的結果“；如果這張信用卡某月的賬單還沒有還款記錄，即還款時間為空白值（Null），那麼執行”運算式為假的結果“。

那麼這兩個真假運算式的結果是什麼呢？再看下一個if層（紫色），IIf(Bill.balance-SUM(Payment.balance)<=0,‘結清賬單‘,‘部分還款,餘‘&Bill.paydate-date()&‘天‘)。也就是這時已經有還款記錄了，那麼這是存在兩種狀態，即一次性還完了某月賬單應還款總額、和只還了部分錢，還剩餘一部分沒還。從運算式來看，賬單金額-n次還款金額總和≤0，即已經還完全部款項（可以多還錢的，因為大多數時候下個月還會有刷卡，肯定還有賬單的），那麼就顯示”結清賬單“；如果賬單金額-n次還款金額總和＞0，也就是可以還了N次，都沒有還完（欠太多錢，要”周轉“幾次才還完），那麼就顯示”部分還款，餘n天“。

還有一個第一層if語句運算式為假的結果（綠色），IIF(Bill.paydate-date()>0,Bill.paydate-date()&‘天‘,‘逾期:‘&date()-Bill.paydate&‘天‘)。也就是最後還款時間-當前日期＞0，那麼計算最後還款時間-當前日期的天數；否則小於等於0就逾期（因為最後一天還款，並不是所有銀行都能入賬為還款，會第二天記賬），逾期N天。

明天再繼續解析三個表的連結的方法選擇，如何利用或選擇左連結（LEFT JOIN）、內連結（INNER JOIN）和右連結（RIGHT JOIN），他們之間的區別和搭配。

基於ACCESS和ASP的SQL多個表查詢與計算統計代碼