本人的原创代码（SQL及T_SQL）

/*三明电力临时卡处理SQL代码,临时卡为专门一类卡，消费时五折优惠，月消费有限额，采取策略是“钱多存，消费少报”*/SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,       lll.OpDt AS 日期, lll.InFare AS 存款额, lll.InCount AS 存款次数, lll.OutFare AS 取款额,       lll.OutCount AS 取款次数, ISNULL(lll.suminfare, 0) / 2 AS 让利额, ISNULL(lll.countinfare,       0) AS 让利次数, (lll.InFare - lll.OutFare) - ISNULL(lll.suminfare, 0)       / 2 AS 存取款应缴金额FROM (SELECT dbo.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.countinfare        FROM dbo.T_CashDay LEFT OUTER JOIN /*采用左外联接查询*/                  (SELECT cash.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,                        countinfare                 FROM (SELECT Cashrec.StatID, Cashrec.CashID, Cashrec.dt,                                SUM(Cashrec.InFare) AS SumInfare, COUNT(*)                                AS CountInfare                         FROM (SELECT StatID, CashID, RTRIM(CAST(YEAR(CashDt)                                        AS char)) + '-' + RTRIM(CAST(MONTH(CashDt) AS char))                                        + '-' + RTRIM(CAST(DAY(CashDt) AS char)) AS dt /*存款日期*/, InFare,                                        OutFare, CustomerID                                 FROM dbo.T_CashRec                                 WHERE outfare = 0) Cashrec /*存款具体时间转换为日期的视图*/ INNER JOIN                               dbo.T_Customers ON                                Cashrec.CustomerID = dbo.T_Customers.CustomerID                         WHERE (dbo.T_Customers.CardType = 1 /*临时卡类型*/ )                          GROUP BY Cashrec.StatID, Cashrec.CashID, Cashrec.dt)                        cash  /*以StatID,CashID,dt作为分组字段，统计出临时卡的存款总额及存款次数的视图*/ INNER JOIN                       dbo.T_CashDay ON cash.StatID = dbo.T_CashDay.StatID AND                        cash.CashID = dbo.T_CashDay.CashID AND                        cash.dt = dbo.T_CashDay.OpDt) ll /*cash视图与T_CashDay表进行联接查询的视图*/ ON               dbo.T_CashDay.OpDt = ll.OpDt AND dbo.T_CashDay.CashID = ll.CashID AND               ll.StatID = dbo.T_CashDay.StatID        WHERE (dbo.T_CashDay.OpDt BETWEEN '2004-7-1' AND '2004-7-1'/*临时卡存款的时间范围*/)) lll INNER JOIN      dbo.T_Cashiers ON lll.CashID = dbo.T_Cashiers.CashID AND       lll.StatID = dbo.T_Cashiers.StatID INNER JOIN      dbo.T_Station ON lll.StatID = dbo.T_Station.StatID

SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,       dbo.T_Group.GrpName AS 食堂经营组, dbo.T_Terms.Port AS 端口,       dbo.T_Terms.TermName AS 窗机名称, yf.SumDt AS 日期,       dbo.T_Meal.MealName AS 餐别, yf.IncomeFare AS 营业额,       yf.IncomeCount AS 营业次数, ISNULL(yf.SumOpfare, 0) / 2 AS 优惠额,       ISNULL(yf.CountOpfare, 0) AS 优惠次数, yf.MngFare AS 管理费,       yf.CorrectFare AS 纠错额, yf.IncomeFare - ISNULL(yf.SumOpfare, 0)       / 2 + yf.MngFare - yf.CorrectFare AS 实际收入FROM (SELECT dbo.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.CorrectFare        FROM dbo.T_IncomeRec LEFT OUTER JOIN                  (SELECT ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,                        ConsumeRec.MealID, dt, SUM(ConsumeRec.OpFare) AS SumOpfare,                        COUNT(*) AS CountOpfare                 FROM (SELECT StatID, Port, Term, CustomerID,                                RTRIM(CAST(YEAR(dbo.T_ConsumeRec.OpDt) AS char))                                + '-' + RTRIM(CAST(MONTH(dbo.T_ConsumeRec.OpDt)                                AS char)) + '-' + RTRIM(CAST(DAY(dbo.T_ConsumeRec.OpDt)                                AS char)) AS dt, CollectDt, MealID, OpFare, MngFare,                                OddFare                         FROM dbo.T_ConsumeRec) AS consumerec INNER JOIN                       dbo.T_Customers ON                        ConsumeRec.CustomerID = dbo.T_Customers.CustomerID                 WHERE (dbo.T_Customers.CardType = 1)                  GROUP BY ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,                        ConsumeRec.MealID, ConsumeRec.dt) s_c_opf ON               s_c_opf.StatID = dbo.T_IncomeRec.StatID AND               s_c_opf.Port = dbo.T_IncomeRec.Port AND               s_c_opf.Term = dbo.T_IncomeRec.Term AND               s_c_opf.MealID = dbo.T_IncomeRec.MealID AND               dbo.T_IncomeRec.SumDt = s_c_opf.dt        WHERE (dbo.T_IncomeRec.SumDt BETWEEN '2004-7-6' AND '2004-7-6'))       yf INNER JOIN      dbo.T_Eatery ON yf.StatID = dbo.T_Eatery.StatID INNER JOIN      dbo.T_Group ON yf.StatID = dbo.T_Group.StatID AND       dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN      dbo.T_Station ON yf.StatID = dbo.T_Station.StatID INNER JOIN      dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND       dbo.T_Group.STID = dbo.T_Terms.STID AND       dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND yf.Port = dbo.T_Terms.Port AND       yf.Term = dbo.T_Terms.Term INNER JOIN      dbo.T_Meal ON yf.MealID = dbo.T_Meal.MealID

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

/*列出未注册卡SQL代码*/select t_consumerec.customerid as 未注册客户号,t_consumerec.oddfare as 未注册卡余额,t_consumerec.opdt as 未注册卡消费时间 from t_consumerec where t_consumerec.customerid<>all(select customerid from t_customers) and t_consumerec.opdt between '2004-9-12 00:00:00' and '2004-9-15 23:59:59' -----------------------------------------------------------------------/*门禁系统后台数据库IDCARD中T_customers表的触发器*/create trigger delterms on t_customers for update as declare @id int,@k1 int,@k2 intselect @k1=count(*) from  t_customers where Insertedselect @k2=count(*) from  t_customers where Deletedif @k1=@k2 and @k1>0begin select  @id=t_customers.customerid from t_customers,t_terms where t_customers.customerid=t_terms.customerid and          t_customers.cardno<>t_terms.cardno          delete from t_terms where customerid=@id end-----------------------------------------------------------------------/*门禁系统的卡号纠正T_SQL代码*/declare @id int, @no int while 1=1 begin    select @id=t_customers.customerid,@no=t_customers.cardno  from t_customers,t_terms where      

t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno      order by t_customers.customerid asc

   if exists(select t_customers.customerid from t_customers,t_terms  where t_customers.customerid=t_terms.customerid and    t_customers.cardno<>t_terms.cardno )     begin        update t_terms set cardno=@no where customerid=@id     end    else     begin       print '更新完毕！'       break     end     end----------------------------------------------------------------------- /*清除冲突补助月份的T_SQL代码*/declare @id int declare @month datetime while 1=1 begin          SELECT @id=T_SubsidyNotPutOut.CustomerID ,@month=dbo.T_SubsidyNotPutOut.[Month]          FROM dbo.T_SubsidyNotPutOut INNER JOIN                dbo.T_SubsidyPutOut ON                dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND                 dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] if (exists(select * FROM T_SubsidyNotPutOut     WHERE (CustomerID=@id and [Month] = @month))) begin    DELETE FROM T_SubsidyNotPutOut    WHERE (CustomerID=@id and [Month] = @month)    continue end else   begin     print '没有相关客户的相关补助发放冲突月份！或补助冲突月份已经清理完毕！'     break   end end----------------------------------------------------------------------- /*前期补助无法发放名单SQL代码*/SELECT dbo.T_Customers.Name AS 姓名, dbo.T_SubsidyNotPutOut.[Month] AS 月份 FROM dbo.T_SubsidyNotPutOut INNER JOIN       dbo.T_SubsidyPutOut ON       dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND       dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] INNER JOIN       dbo.T_Customers ON       dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_Customers.CustomerID--------------------------------------------------------------------------------/*显示人员消费情况及联系SQL代码*/SELECT dbo.T_Customers.Name AS 姓名, dbo.T_ConsumeRec.OpDt AS 消费时间,       dbo.T_ConsumeRec.OpFare AS 消费额, dbo.T_ConsumeRec.OddFare AS 余额,       ISNULL(dbo.T_Department.DpName1, '') + ISNULL(dbo.T_Department.DpName2, '')       + ISNULL(dbo.T_Department.DpName3, '') AS 部门,       dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,       dbo.T_Group.GrpName AS 食堂组, dbo.T_Terms.Port AS 端口号,       dbo.T_Terms.TermName AS 窗机名称FROM dbo.T_ConsumeRec INNER JOIN      dbo.T_Customers INNER JOIN      dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)       = dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)       = dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)       = dbo.T_Department.DpCode3 ON       dbo.T_ConsumeRec.CustomerID = dbo.T_Customers.CustomerID INNER JOIN      dbo.T_Eatery ON dbo.T_ConsumeRec.StatID = dbo.T_Eatery.StatID INNER JOIN      dbo.T_Group ON dbo.T_ConsumeRec.StatID = dbo.T_Group.StatID AND       dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN      dbo.T_Station ON dbo.T_ConsumeRec.StatID = dbo.T_Station.StatID INNER JOIN      dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND       dbo.T_Eatery.STID = dbo.T_Terms.STID AND       dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND       dbo.T_ConsumeRec.Port = dbo.T_Terms.Port AND       dbo.T_ConsumeRec.Term = dbo.T_Terms.Term----------------------------------------------------------------------------------------/*列出存取款及联系SQL代码*/SELECT dbo.T_Customers.Name AS 姓名, ISNULL(dbo.T_Department.DpName1, '') + ISNULL(dbo.T_Department.DpName2, '')       + ISNULL(dbo.T_Department.DpName3, '') AS 部门,       dbo.T_CashRec.CashDt AS 存取款时间, dbo.T_CashRec.InFare AS 存款额,       dbo.T_CashRec.OutFare AS 取款额, dbo.T_CashRec.OddFare AS 余额,       dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,       dbo.T_CashRec.Port AS 出纳机端口, dbo.T_CashRec.Term AS 出纳机机器号 FROM dbo.T_Station INNER JOIN       dbo.T_Cashiers ON dbo.T_Station.StatID = dbo.T_Cashiers.StatID INNER JOIN       dbo.T_Customers INNER JOIN       dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)       = dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)       = dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)       = dbo.T_Department.DpCode3 INNER JOIN       dbo.T_CashRec ON dbo.T_Customers.CustomerID = dbo.T_CashRec.CustomerID ON       dbo.T_Cashiers.CashID = dbo.T_CashRec.CashID AND       dbo.T_Station.StatID = dbo.T_CashRec.StatID-----------------------------------------------------------------------------------------/*列出incomerec帐本中与现有窗机比较后不存在的窗机*/SELECT Port, TermFROM T_IncomeRecWHERE (Term <> ALL          (SELECT term         FROM t_terms         WHERE statid = 2)) AND (StatID = 2)----------------------------------------------------------------------------------------/*公安专发放补助前处理的SQL代码(当月补助形成后)*/if exists(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by customerid having count(*)>1)  begin /*判断5、6类卡是否存在一人两条前期记录即存在去年没领补助的教师*/    select month ,customerid as 去年没领补助的教师的客户号,cardtype,subsidy from t_subsidynotputout where     customerid    =any(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by     customerid having count(*)>1)  endelse begin--go  insert into t_subsidymonthplan(month,customerid,cardtype,subsidy)  select * from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0/*公安专在当月补助形成后（5、6类卡为教师卡，一年只领一次补助），发补助前：向表monthplan插入符合在表notputout中5、6类卡subsidy为0的记录，注意是否存在一人两条前期记录(一般不会出现这种况，除非去年没领补助)，否则在monthplan表中后面的更新时间会出错！！*/--go  delete t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0/*删除notputout被复制的记录*/--go  update t_subsidymonthplan set month='2004-12-1' where (cardtype=5 or cardtype=6) and subsidy=0 /*更改表monthplan中的month日期，月份根据实际定*/--go  update t_customers set subsidydt='2004-12-1' where (cardtype=5 or cardtype=6) and cursubsidyfare>0/*更改t_customers表中的补助时间，注意与t_subsidymonthplan表中的month日期值保持一致！！*/ end  go  select sum(cursubsidyfare) as 客户帐本的补助总额 from t_customers  go  select sum(subsidy) as 前期补助总额 from t_subsidynotputout  go select sum(subsidy) as 当月补助总额 from t_subsidymonthplan/*查询客户帐本的补助总额是否等于前期补助总额与当月补助总额之和！*/--------------------------------------------------------------------------------------------------                 /*师大补助月报表信息纠正T_SQL代码*/declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money

   /*清除冲突补助月份的T_SQL代码开始*/declare @id int while 1=1 begin          SELECT @id=T_SubsidyNotPutOut.CustomerID ,@month=dbo.T_SubsidyNotPutOut.[Month]          FROM dbo.T_SubsidyNotPutOut INNER JOIN                dbo.T_SubsidyPutOut ON                dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND                 dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] if (exists(select * FROM T_SubsidyNotPutOut     WHERE (CustomerID=@id and [Month] = @month))) begin    DELETE FROM T_SubsidyNotPutOut    WHERE (CustomerID=@id and [Month] = @month)    end else   begin     print '没有相关客户的相关补助发放冲突月份！或补助冲突月份已经清理完毕！'     break   end end   /**/ while 1=1 begin          SELECT @id=T_Subsidymonthplan.CustomerID ,@month=dbo.T_Subsidymonthplan.[Month]          FROM dbo.T_Subsidymonthplan INNER JOIN                dbo.T_SubsidyPutOut ON                dbo.T_Subsidymonthplan.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND                 dbo.T_Subsidymonthplan.[Month] = dbo.T_SubsidyPutOut.[Month] if (exists(select * FROM T_Subsidymonthplan     WHERE (CustomerID=@id and [Month] = @month))) begin    DELETE FROM T_Subsidymonthplan    WHERE (CustomerID=@id and [Month] = @month)    end else   begin     print '没有相关客户的本月补助冲突！或本月补助冲突已经清理完毕！'     break   end end

/*清除冲突补助月份的T_SQL代码结束*/

set @month='2004-9-1' /*补助的月份,根据实际定*/set @cardtype=4       /*卡的类别，根据实际定*/   select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidypre where cardtype=@cardtype and month=@month /*统计当月补助计划人数及金额，以t_subsidypre帐本为准*/if exists(select * from t_subsidymonth where plancount=@k and plansubsidy=@subsidyFare and month=@month　and cardtype=@cardtype)   /*判断当月补助计划数及金额是否正确*/ begin   select @k1=count(*),@subsidyFare1=sum(subsidy) from t_subsidymonthplan where   cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/   if @subsidyFare1 is null    begin      set @subsidyFare1=0    end   set @k2=@k-@k1    set @subsidyFare2=@subsidyFare-@subsidyFare1   update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype    and    month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/   print '更改当月的补助信息完成!' endelse begin  print '计划总数不一致!'  end

select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where cardtype=@cardtype and month<@month /*统计前期补助未发人数及金额*/select @k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where cardtype=@cardtype and month=@month /*统计当月前期补助计划人数及金额*/set @k2=@k1-@kset @subsidyFare2=@subsidyFare1-@subsidyFareif @subsidyFare2 is null    begin      set @subsidyFare2=0    endupdate t_subsidymonth set putoutprecount=@k2,putoutpre=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/print '更改当月的前期补助信息完成!'-------------------------------------------------------------------------------------------------           /*清除管理费的触发器及生成t_mngfarelog表的脚本*/create trigger tr_mngfarelog on t_incomerec for insert,update as if exists(select * from t_incomerec where mngfare>0)    begin      declare @statid tinyint,@mealid tinyint,@port tinyint,@term tinyint,         @sumdt datetime,@incomefare money,@mngfare money,@avginc money,         @incomecount int      select @statid=statid,@mealid=mealid,@port=port,@term=term,      @sumdt=sumdt, @incomefare=incomefare,@incomecount=incomecount,      @mngfare=mngfare from t_incomerec where mngfare>0      update t_incomerec set mngfare=0 where statid=@statid and mealid=@mealid and             port=@port and term=@term and sumdt=@sumdt      set @avginc=@incomefare/@incomecount      if @avginc>5/*平均消费值，根据实际定*/        begin          update t_incomerec set incomefare=4.5*incomecount where          statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt        end        insert into t_mngfarelog values(@statid,@mealid,@port,@term,                  @sumdt,@incomefare,@incomecount,@mngfare)      end go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_mngfarelog]') andOBJECTPROPERTY(id, N'IsUserTable') = 1) /*生成t_mngfarelog表的脚本*/ drop table [dbo].[t_mngfarelog] GO

CREATE TABLE [dbo].[t_mngfarelog] (     [statid] [tinyint] NOT NULL ,     [mealid] [tinyint] NOT NULL ,     [port] [tinyint] NOT NULL ,     [term] [tinyint] NOT NULL ,     [sumdt] [datetime] NOT NULL ,     [incomefare] [money] NOT NULL ,     [incomecount] [int] NOT NULL ,     [mngfare] [money] NULL ) ON [PRIMARY] GO