MSSQL网页编程之自己的原创代码（SQL及T_SQL）

因此我们的方案中要构造这种逆操作。Event_type增加一种FlashBACK_EVENT。这类操作形式与Query_Event相同，都是简单的SQL语句，只是包含了将数据恢复的操作。原创
/*三明电力一时卡处置SQL代码,一时卡为专门一类卡，消耗时五折优惠，月消耗无限额，接纳战略是“钱多存，消耗少报”*/SELECTdbo.T_Station.StatNameAS事情站,dbo.T_Cashiers.NameAS出纳员,lll.OpDtAS日期,lll.InFareAS存款额,lll.InCountAS存款次数,lll.OutFareAS存款额,lll.OutCountAS存款次数,ISNULL(lll.suminfare,0)/2AS让利额,ISNULL(lll.countinfare,0)AS让利次数,(lll.InFare-lll.OutFare)-ISNULL(lll.suminfare,0)/2AS存存款应缴金额FROM(SELECTdbo.T_CashDay.StatID,dbo.T_CashDay.OpDt,dbo.T_CashDay.CashID,dbo.T_CashDay.InFare,dbo.T_CashDay.InCount,dbo.T_CashDay.OutFare,dbo.T_CashDay.OutCount,ll.suminfare,ll.countinfareFROMdbo.T_CashDayLEFTOUTERJOIN/*接纳左外连接查询*/(SELECTcash.StatID,cash.CashID,dbo.T_CashDay.OpDt,dbo.T_CashDay.InFare,dbo.T_CashDay.InCount,dbo.T_CashDay.OutFare,dbo.T_CashDay.OutCount,cash.suminfare,countinfareFROM(SELECTCashrec.StatID,Cashrec.CashID,Cashrec.dt,SUM(Cashrec.InFare)ASSumInfare,COUNT(*)ASCountInfareFROM(SELECTStatID,CashID,RTRIM(CAST(YEAR(CashDt)ASchar))+-+RTRIM(CAST(MONTH(CashDt)ASchar))+-+RTRIM(CAST(DAY(CashDt)ASchar))ASdt/*存款日期*/,InFare,OutFare,CustomerIDFROMdbo.T_CashRecWHEREoutfare=0)Cashrec/*存款详细工夫转换为日期的视图*/INNERJOINdbo.T_CustomersONCashrec.CustomerID=dbo.T_Customers.CustomerIDWHERE(dbo.T_Customers.CardType=1/*一时卡范例*/)GROUPBYCashrec.StatID,Cashrec.CashID,Cashrec.dt)cash/*以StatID,CashID,dt作为分组字段，统计出一时卡的存款总额及存款次数的视图*/INNERJOINdbo.T_CashDayONcash.StatID=dbo.T_CashDay.StatIDANDcash.CashID=dbo.T_CashDay.CashIDANDcash.dt=dbo.T_CashDay.OpDt)ll/*cash视图与T_CashDay表举行连接查询的视图*/ONdbo.T_CashDay.OpDt=ll.OpDtANDdbo.T_CashDay.CashID=ll.CashIDANDll.StatID=dbo.T_CashDay.StatIDWHERE(dbo.T_CashDay.OpDtBETWEEN2004-7-1AND2004-7-1/*一时卡存款的工夫局限*/))lllINNERJOINdbo.T_CashiersONlll.CashID=dbo.T_Cashiers.CashIDANDlll.StatID=dbo.T_Cashiers.StatIDINNERJOINdbo.T_StationONlll.StatID=dbo.T_Station.StatID

SELECTdbo.T_Station.StatNameAS事情站,dbo.T_Eatery.STNameAS食堂,dbo.T_Group.GrpNameAS食堂谋划组,dbo.T_Terms.PortAS端口,dbo.T_Terms.TermNameAS窗机称号,yf.SumDtAS日期,dbo.T_Meal.MealNameAS餐别,yf.IncomeFareAS业务额,yf.IncomeCountAS业务次数,ISNULL(yf.SumOpfare,0)/2AS优惠额,ISNULL(yf.CountOpfare,0)AS优惠次数,yf.MngFareAS办理费,yf.CorrectFareAS纠错额,yf.IncomeFare-ISNULL(yf.SumOpfare,0)/2+yf.MngFare-yf.CorrectFareAS实践支出FROM(SELECTdbo.T_IncomeRec.StatID,dbo.T_IncomeRec.MealID,dbo.T_IncomeRec.Port,dbo.T_IncomeRec.Term,dbo.T_IncomeRec.SumDt,dbo.T_IncomeRec.IncomeFare,dbo.T_IncomeRec.IncomeCount,s_c_opf.SumOpfare,s_c_opf.CountOpfare,dbo.T_IncomeRec.MngFare,dbo.T_IncomeRec.CorrectFareFROMdbo.T_IncomeRecLEFTOUTERJOIN(SELECTConsumeRec.StatID,ConsumeRec.Port,ConsumeRec.Term,ConsumeRec.MealID,dt,SUM(ConsumeRec.OpFare)ASSumOpfare,COUNT(*)ASCountOpfareFROM(SELECTStatID,Port,Term,CustomerID,RTRIM(CAST(YEAR(dbo.T_ConsumeRec.OpDt)ASchar))+-+RTRIM(CAST(MONTH(dbo.T_ConsumeRec.OpDt)ASchar))+-+RTRIM(CAST(DAY(dbo.T_ConsumeRec.OpDt)ASchar))ASdt,CollectDt,MealID,OpFare,MngFare,OddFareFROMdbo.T_ConsumeRec)ASconsumerecINNERJOINdbo.T_CustomersONConsumeRec.CustomerID=dbo.T_Customers.CustomerIDWHERE(dbo.T_Customers.CardType=1)GROUPBYConsumeRec.StatID,ConsumeRec.Port,ConsumeRec.Term,ConsumeRec.MealID,ConsumeRec.dt)s_c_opfONs_c_opf.StatID=dbo.T_IncomeRec.StatIDANDs_c_opf.Port=dbo.T_IncomeRec.PortANDs_c_opf.Term=dbo.T_IncomeRec.TermANDs_c_opf.MealID=dbo.T_IncomeRec.MealIDANDdbo.T_IncomeRec.SumDt=s_c_opf.dtWHERE(dbo.T_IncomeRec.SumDtBETWEEN2004-7-6AND2004-7-6))yfINNERJOINdbo.T_EateryONyf.StatID=dbo.T_Eatery.StatIDINNERJOINdbo.T_GroupONyf.StatID=dbo.T_Group.StatIDANDdbo.T_Eatery.STID=dbo.T_Group.STIDINNERJOINdbo.T_StationONyf.StatID=dbo.T_Station.StatIDINNERJOINdbo.T_TermsONdbo.T_Eatery.StatID=dbo.T_Terms.StatIDANDdbo.T_Group.STID=dbo.T_Terms.STIDANDdbo.T_Group.GrpID=dbo.T_Terms.GrpIDANDyf.Port=dbo.T_Terms.PortANDyf.Term=dbo.T_Terms.TermINNERJOINdbo.T_MealONyf.MealID=dbo.T_Meal.MealID

-------------------------------------------------------------------------------------

/*列出未注册卡SQL代码*/selectt_consumerec.customeridas未注册客户号,t_consumerec.oddfareas未注册卡余额,t_consumerec.opdtas未注册卡消耗工夫fromt_consumerecwheret_consumerec.customeridall(selectcustomeridfromt_customers)andt_consumerec.opdtbetween2004-9-1200:00:00and2004-9-1523:59:59-----------------------------------------------------------------------/*门禁体系背景数据库IDCARD中T_customers表的触发器*/createtriggerdeltermsont_customersforupdateasdeclare@idint,@k1int,@k2intselect@k1=count(*)fromt_customerswhereInsertedselect@k2=count(*)fromt_customerswhereDeletedif@k1=@k2and@k1>0beginselect@id=t_customers.customeridfromt_customers,t_termswheret_customers.customerid=t_terms.customeridandt_customers.cardnot_terms.cardnodeletefromt_termswherecustomerid=@idend-----------------------------------------------------------------------/*门禁体系的卡号改正T_SQL代码*/declare@idint,@nointwhile1=1beginselect@id=t_customers.customerid,@no=t_customers.cardnofromt_customers,t_termswhere

t_customers.customerid=t_terms.customeridandt_customers.cardnot_terms.cardnoorderbyt_customers.customeridasc

ifexists(selectt_customers.customeridfromt_customers,t_termswheret_customers.customerid=t_terms.customeridandt_customers.cardnot_terms.cardno)beginupdatet_termssetcardno=@nowherecustomerid=@idendelsebeginprint更新终了！breakendend-----------------------------------------------------------------------/*扫除抵触津贴月份的T_SQL代码*/declare@idintdeclare@monthdatetimewhile1=1beginSELECT@id=T_SubsidyNotPutOut.CustomerID,@month=dbo.T_SubsidyNotPutOut.[Month]FROMdbo.T_SubsidyNotPutOutINNERJOINdbo.T_SubsidyPutOutONdbo.T_SubsidyNotPutOut.CustomerID=dbo.T_SubsidyPutOut.CustomerIDANDdbo.T_SubsidyNotPutOut.[Month]=dbo.T_SubsidyPutOut.[Month]if(exists(select*FROMT_SubsidyNotPutOutWHERE(CustomerID=@idand[Month]=@month)))beginDELETEFROMT_SubsidyNotPutOutWHERE(CustomerID=@idand[Month]=@month)continueendelsebeginprint没有相干客户的相干津贴发放抵触月份！或津贴抵触月份已清算终了！breakendend-----------------------------------------------------------------------/*后期津贴没法发放名单SQL代码*/SELECTdbo.T_Customers.NameAS姓名,dbo.T_SubsidyNotPutOut.[Month]AS月份FROMdbo.T_SubsidyNotPutOutINNERJOINdbo.T_SubsidyPutOutONdbo.T_SubsidyNotPutOut.CustomerID=dbo.T_SubsidyPutOut.CustomerIDANDdbo.T_SubsidyNotPutOut.[Month]=dbo.T_SubsidyPutOut.[Month]INNERJOINdbo.T_CustomersONdbo.T_SubsidyNotPutOut.CustomerID=dbo.T_Customers.CustomerID--------------------------------------------------------------------------------/*显现职员消耗情形及接洽SQL代码*/SELECTdbo.T_Customers.NameAS姓名,dbo.T_ConsumeRec.OpDtAS消耗工夫,dbo.T_ConsumeRec.OpFareAS消耗额,dbo.T_ConsumeRec.OddFareAS余额,ISNULL(dbo.T_Department.DpName1,)+ISNULL(dbo.T_Department.DpName2,)+ISNULL(dbo.T_Department.DpName3,)AS部门,dbo.T_Station.StatNameAS事情站,dbo.T_Eatery.STNameAS食堂,dbo.T_Group.GrpNameAS食堂组,dbo.T_Terms.PortAS端标语,dbo.T_Terms.TermNameAS窗机称号FROMdbo.T_ConsumeRecINNERJOINdbo.T_CustomersINNERJOINdbo.T_DepartmentONSUBSTRING(dbo.T_Customers.Account,1,2)=dbo.T_Department.DpCode1ANDSUBSTRING(dbo.T_Customers.Account,3,2)=dbo.T_Department.DpCode2ANDSUBSTRING(dbo.T_Customers.Account,5,3)=dbo.T_Department.DpCode3ONdbo.T_ConsumeRec.CustomerID=dbo.T_Customers.CustomerIDINNERJOINdbo.T_EateryONdbo.T_ConsumeRec.StatID=dbo.T_Eatery.StatIDINNERJOINdbo.T_GroupONdbo.T_ConsumeRec.StatID=dbo.T_Group.StatIDANDdbo.T_Eatery.STID=dbo.T_Group.STIDINNERJOINdbo.T_StationONdbo.T_ConsumeRec.StatID=dbo.T_Station.StatIDINNERJOINdbo.T_TermsONdbo.T_Eatery.StatID=dbo.T_Terms.StatIDANDdbo.T_Eatery.STID=dbo.T_Terms.STIDANDdbo.T_Group.GrpID=dbo.T_Terms.GrpIDANDdbo.T_ConsumeRec.Port=dbo.T_Terms.PortANDdbo.T_ConsumeRec.Term=dbo.T_Terms.Term----------------------------------------------------------------------------------------/*列出存存款及接洽SQL代码*/SELECTdbo.T_Customers.NameAS姓名,ISNULL(dbo.T_Department.DpName1,)+ISNULL(dbo.T_Department.DpName2,)+ISNULL(dbo.T_Department.DpName3,)AS部门,dbo.T_CashRec.CashDtAS存存款工夫,dbo.T_CashRec.InFareAS存款额,dbo.T_CashRec.OutFareAS存款额,dbo.T_CashRec.OddFareAS余额,dbo.T_Station.StatNameAS事情站,dbo.T_Cashiers.NameAS出纳员,dbo.T_CashRec.PortAS出纳机端口,dbo.T_CashRec.TermAS出纳机呆板号FROMdbo.T_StationINNERJOINdbo.T_CashiersONdbo.T_Station.StatID=dbo.T_Cashiers.StatIDINNERJOINdbo.T_CustomersINNERJOINdbo.T_DepartmentONSUBSTRING(dbo.T_Customers.Account,1,2)=dbo.T_Department.DpCode1ANDSUBSTRING(dbo.T_Customers.Account,3,2)=dbo.T_Department.DpCode2ANDSUBSTRING(dbo.T_Customers.Account,5,3)=dbo.T_Department.DpCode3INNERJOINdbo.T_CashRecONdbo.T_Customers.CustomerID=dbo.T_CashRec.CustomerIDONdbo.T_Cashiers.CashID=dbo.T_CashRec.CashIDANDdbo.T_Station.StatID=dbo.T_CashRec.StatID-----------------------------------------------------------------------------------------/*列出incomerec账本中与现有窗机对照后不存在的窗机*/SELECTPort,TermFROMT_IncomeRecWHERE(TermALL(SELECTtermFROMt_termsWHEREstatid=2))AND(StatID=2)----------------------------------------------------------------------------------------/*公安专发放津贴前处置的SQL代码(当月津贴构成后)*/ifexists(selectcustomeridfromt_subsidynotputoutwhere(cardtype=5orcardtype=6)andsubsidy=0groupbycustomeridhavingcount(*)>1)begin/*判别5、6类卡是不是存在一人两条后期纪录即存在客岁没领津贴的教员*/selectmonth,customeridas客岁没领津贴的教员的客户号,cardtype,subsidyfromt_subsidynotputoutwherecustomerid=any(selectcustomeridfromt_subsidynotputoutwhere(cardtype=5orcardtype=6)andsubsidy=0groupbycustomeridhavingcount(*)>1)endelsebegin--goinsertintot_subsidymonthplan(month,customerid,cardtype,subsidy)select*fromt_subsidynotputoutwhere(cardtype=5orcardtype=6)andsubsidy=0/*公安专在当月津贴构成后（5、6类卡为教员卡，一年只领一次津贴），发津贴前：向表monthplan拔出切合在表notputout中5、6类卡subsidy为0的纪录，注重是不是存在一人两条后期纪录(一样平常不会呈现这类况，除非客岁没领津贴)，不然在monthplan表中前面的更新工夫会堕落！！*/--godeletet_subsidynotputoutwhere(cardtype=5orcardtype=6)andsubsidy=0/*删除notputout被复制的纪录*/--goupdatet_subsidymonthplansetmonth=2004-12-1where(cardtype=5orcardtype=6)andsubsidy=0/*变动表monthplan中的month日期，月份依据实践定*/--goupdatet_customerssetsubsidydt=2004-12-1where(cardtype=5orcardtype=6)andcursubsidyfare>0/*变动t_customers表中的津贴工夫，注重与t_subsidymonthplan表中的month日期值坚持分歧！！*/endgoselectsum(cursubsidyfare)as客户账本的津贴总额fromt_customersgoselectsum(subsidy)as后期津贴总额fromt_subsidynotputoutgoselectsum(subsidy)as当月津贴总额fromt_subsidymonthplan/*查询客户账本的津贴总额是不是即是后期津贴总额与当月津贴总额之和！*/--------------------------------------------------------------------------------------------------/*师年夜津贴月报表信息改正T_SQL代码*/declare@kint,@k1int,@k2int,@monthdatetime,@subsidyFaremoney,@cardtypetinyint,@subsidyFare1money,@subsidyFare2money

/*扫除抵触津贴月份的T_SQL代码入手下手*/declare@idintwhile1=1beginSELECT@id=T_SubsidyNotPutOut.CustomerID,@month=dbo.T_SubsidyNotPutOut.[Month]FROMdbo.T_SubsidyNotPutOutINNERJOINdbo.T_SubsidyPutOutONdbo.T_SubsidyNotPutOut.CustomerID=dbo.T_SubsidyPutOut.CustomerIDANDdbo.T_SubsidyNotPutOut.[Month]=dbo.T_SubsidyPutOut.[Month]if(exists(select*FROMT_SubsidyNotPutOutWHERE(CustomerID=@idand[Month]=@month)))beginDELETEFROMT_SubsidyNotPutOutWHERE(CustomerID=@idand[Month]=@month)endelsebeginprint没有相干客户的相干津贴发放抵触月份！或津贴抵触月份已清算终了！breakendend/**/while1=1beginSELECT@id=T_Subsidymonthplan.CustomerID,@month=dbo.T_Subsidymonthplan.[Month]FROMdbo.T_SubsidymonthplanINNERJOINdbo.T_SubsidyPutOutONdbo.T_Subsidymonthplan.CustomerID=dbo.T_SubsidyPutOut.CustomerIDANDdbo.T_Subsidymonthplan.[Month]=dbo.T_SubsidyPutOut.[Month]if(exists(select*FROMT_SubsidymonthplanWHERE(CustomerID=@idand[Month]=@month)))beginDELETEFROMT_SubsidymonthplanWHERE(CustomerID=@idand[Month]=@month)endelsebeginprint没有相干客户的本月津贴抵触！或本月津贴抵触已清算终了！breakendend

/*扫除抵触津贴月份的T_SQL代码停止*/

set@month=2004-9-1/*津贴的月份,依据实践定*/set@cardtype=4/*卡的种别，依据实践定*/select@k=count(*),@subsidyFare=sum(subsidy)fromt_subsidyprewherecardtype=@cardtypeandmonth=@month/*统计当月津贴企图人数及金额，以t_subsidypre账本为准*/ifexists(select*fromt_subsidymonthwhereplancount=@kandplansubsidy=@subsidyFareandmonth=@month　andcardtype=@cardtype)/*判别当月津贴企图数及金额是不是准确*/beginselect@k1=count(*),@subsidyFare1=sum(subsidy)fromt_subsidymonthplanwherecardtype=@cardtypeandmonth=@month/*统计当月津贴未发人数及金额*/if@subsidyFare1isnullbeginset@subsidyFare1=0endset@k2=@k-@k1set@subsidyFare2=@subsidyFare-@subsidyFare1updatet_subsidymonthsetputoutcount=@k2,putoutsubsidy=@subsidyFare2wherecardtype=@cardtypeandmonth=@month/*当月已发人数及金额=当月津贴企图人数及金额-当月津贴未发人数及金额*/print变动当月的津贴信息完成!endelsebeginprint企图总数纷歧致!end

select@k=count(*),@subsidyFare=sum(subsidy)fromt_subsidynotputoutwherecardtype=@cardtypeandmonth<@month/*统计后期津贴未发人数及金额*/select@k1=planprecount,@subsidyFare1=planprefromt_subsidymonthwherecardtype=@cardtypeandmonth=@month/*统计当月后期津贴企图人数及金额*/set@k2=@k1-@kset@subsidyFare2=@subsidyFare1-@subsidyFareif@subsidyFare2isnullbeginset@subsidyFare2=0endupdatet_subsidymonthsetputoutprecount=@k2,putoutpre=@subsidyFare2wherecardtype=@cardtypeandmonth=@month/*当月后期已发人数及金额=当月后期津贴企图人数及金额-后期津贴未发人数及金额*/print变动当月的后期津贴信息完成!-------------------------------------------------------------------------------------------------/*扫除办理费的触发器及天生t_mngfarelog表的剧本*/createtriggertr_mngfarelogont_incomerecforinsert,updateasifexists(select*fromt_incomerecwheremngfare>0)begindeclare@statidtinyint,@mealidtinyint,@porttinyint,@termtinyint,@sumdtdatetime,@incomefaremoney,@mngfaremoney,@avgincmoney,@incomecountintselect@statid=statid,@mealid=mealid,@port=port,@term=term,@sumdt=sumdt,@incomefare=incomefare,@incomecount=incomecount,@mngfare=mngfarefromt_incomerecwheremngfare>0updatet_incomerecsetmngfare=0wherestatid=@statidandmealid=@mealidandport=@portandterm=@termandsumdt=@sumdtset@avginc=@incomefare/@incomecountif@avginc>5/*均匀消耗值，依据实践定*/beginupdatet_incomerecsetincomefare=4.5*incomecountwherestatid=@statidandmealid=@mealidandport=@portandterm=@termandsumdt=@sumdtendinsertintot_mngfarelogvalues(@statid,@mealid,@port,@term,@sumdt,@incomefare,@incomecount,@mngfare)endgo

ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[t_mngfarelog])andOBJECTPROPERTY(id,NIsUserTable)=1)/*天生t_mngfarelog表的剧本*/droptable[dbo].[t_mngfarelog]GO

CREATETABLE[dbo].[t_mngfarelog]([statid][tinyint]NOTNULL,[mealid][tinyint]NOTNULL,[port][tinyint]NOTNULL,[term][tinyint]NOTNULL,[sumdt][datetime]NOTNULL,[incomefare][money]NOTNULL,[incomecount][int]NOTNULL,[mngfare][money]NULL)ON[PRIMARY]GO
由MySQL用来存储数据的文件格式以已经被广泛地测试过，但是总是有外部情况可以导致数据库表被破坏：

仓酷云

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

大侠们有推荐的书籍和学习方法写下吧。

分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。