基于ACCESS和ASP的SQL多个表查询与计算统计代码

最近在写几个关于“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.CCno
GROUP BY Credit.ID, Credit.CCno, Credit.Bank, Credit.brand, Credit.Card,
Credit.limit, Credit.Belong, Credit.billday, Credit.consumeday,
Credit.templimit, Credit.unsettled, Credit.newtime
order 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.BLno
GROUP BY Bill.ID, Bill.CCno, Credit.bank, Credit.Card, Bill.BLno, 
Bill.billdate, Bill.paydate, Bill.balance
HAVING(Year(Bill.billdate)='2014' AND Month(Bill.billdate)='12')
order by Bill.ID Desc

这段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），他们之间的区别和搭配。