SQL统计贷款形态迁徙一例

[yudisanren]

单位要统计贷款五级分类形态迁徙数据，格式如下：

数据源有大概4000条记录，格式如下：

要求统计结果生成以下格式：

意即：年初正常——>本期正常、关注、次级、可疑、损失这5个形态的金额总共有多少，年初其他形态诸如“关注、次级”等同样。
刚开始我用VB循环5×5=25次执行SQL查询，我就想：能不能用一句SQL不循环执行查询，得到同样结果呢？
经过反复测试，反复改写SQL语句，改写后如下：

sqls = "TRANSFORM FORMAT(SUM(贷款余额)/10000,'#.00') " & _
"SELECT 次序,年初形态 FROM (" & _
"SELECT * FROM (" & _
"SELECT SWITCH(" & _
"年初形态='正常','1'," & _
"年初形态='关注','2'," & _
"年初形态='次级','3'," & _
"年初形态='可疑','4'," & _
"年初形态='损失','5'" & _
") AS 次序," & _
"年初形态,本期形态,SUM(贷款余额) AS 贷款余额 FROM (" & _
"SELECT B.五级形态 AS 年初形态,A.五级形态 AS 本期形态,A.贷款余额 FROM " & _
tbName2 & " A," & tbName1 & " B WHERE A.借据号=B.借据号" & " UNION ALL " & _
"SELECT '正常' AS 年初形态,'正常' AS 本期形态,NULL AS 贷款余额 FROM " & tbName2 & " UNION ALL " & _
"SELECT '关注' AS 年初形态,'关注' AS 本期形态,NULL AS 贷款余额 FROM " & tbName2 & " UNION ALL " & _
"SELECT '次级' AS 年初形态,'次级' AS 本期形态,NULL AS 贷款余额 FROM " & tbName2 & " UNION ALL " & _
"SELECT '可疑' AS 年初形态,'可疑' AS 本期形态,NULL AS 贷款余额 FROM " & tbName2 & " UNION ALL " & _
"SELECT '损失' AS 年初形态,'损失' AS 本期形态,NULL AS 贷款余额 FROM " & tbName2 & ") " & _
"GROUP BY 年初形态,本期形态) ORDER BY 次序) " & _
"GROUP BY 次序,年初形态 " & _
"PIVOT 本期形态 IN ('正常','关注','次级','可疑','损失')"

用VB执行ADO查询命令，与刚开始5×5的25次循环结果一样！
SQL中的TRANSFORM--- PIVOT是为了让查询结果中的本年形态转置为横向列标显示。
SWITCH函数是为了让五级形态能按“正常、关注、次级、可疑、损失”的顺序排列（否则SQL不按这5个顺序排列）。
UNION ALL下面联合的五个形态是为了补齐5个形态，否则，如果数据源中没有哪个形态，SQL结果就不显示，不能方便准确填到对应报表位置。
该SQL语句运行效率较高，大约4000条记录，2G内存电脑，瞬间出结果。

[ycyyoulove]

一题 顶

[赵4老师]

[冰不孤独]

路过。表述不全。没有数据库。咋整。呵呵！