MSSQL网页编程之T-SQL，静态聚合查询

MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。静态
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME=AccountMessage)DROPTABLEAccountMessageGO

CREATETABLEAccountMessage(FFundCodeVARCHAR(6)NOTNULL,FAccNameVARCHAR(20)NOTNULL,FAccNumINTNOTNULL);

IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME=AccountBalance)DROPTABLEAccountBalanceGO

CREATETABLEAccountBalance(FFundCodeVARCHAR(6)NOTNULL,FAccNumINTNOTNULL,FDateDATETIMEDEFAULT(getdate())NOTNULL,FBalNUMERIC(10,2)NOTNULL);

INSERTINTOAccountMessageVALUES(000001,北京存款,1)INSERTINTOAccountMessageVALUES(000001,上海存款,2)INSERTINTOAccountMessageVALUES(000001,深圳存款,3)INSERTINTOAccountMessageVALUES(000002,北京存款,1)INSERTINTOAccountMessageVALUES(000002,上海存款,2)INSERTINTOAccountMessageVALUES(000002,天津存款,3)INSERTINTOAccountMessageVALUES(000003,上海存款,1)INSERTINTOAccountMessageVALUES(000003,福州存款,2)

INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000001,1,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000001,2,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000001,3,1120.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000002,1,2000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000002,2,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000002,3,1000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000003,1,2000.00)INSERTINTOAccountBalance(FDate,FFundCode,FAccNum,FBal)VALUES(2004-07-28,000003,2,1000.00)go

两种分歧的办法

declare@snvarchar(4000)set@s=select@s=@s+,+quotename(FAccName)+=isnull(sum(casea.FAccNamewhen+quotename(FAccName,)+thenb.FBalend),0)fromAccountMessagegroupbyFAccNameexec(select基金代码=a.FFundCode+@s+fromAccountMessagea,AccountBalancebwherea.FFundCode=b.FFundCodeanda.FAccNum=b.FAccNumgroupbya.FFundCode)go

select*into#tfrom(selecta.*,b.fbalfromAccountMessageajoinAccountBalancebona.ffundcode=b.ffundcodeanda.faccnum=b.faccnum)tDECLARE@SQLVARCHAR(8000)SET@SQL=SELECTffundcodeSELECT@SQL=@SQL+,sum(CASEWHENFAccName=+tt+THENFBalelse0END)[+tt+]FROM(SELECTDISTINCTFAccNameasttFROM#t)ASET@SQL=@SQL+FROM#tgroupbyffundcodeexec(@SQL)
有了rowbase的binlog后，我们来分析一下怎么实现闪回。平时的DML无非三种操作，增删改，先说三种操作的日志格式。