比较两条记录中那些字段不相等
<p>比较两条记录中那些字段不相等，即某个字段的值是否有改变！</p><p>例子如下：</p><p>select nGoodsID, sGoodsDesc, sGoodsName, sSpec , sBrand , nPack , sUnit ,nCaseUnits ,<br/> nCategoryID ,sHome ,sNationID , sNation ,sGoodsLevelID ,sGoodsLevel ,<br/> sGoodTypeID ,sGoodType ,sTradeModeID ,sTradeMode ,sDepositTypeID , sDepositType ,<br/> nMinLineUnit ,nMinLineL ,nShelfLife ,nMinDueDay ,sNote ,nLength ,nWidth ,<br/> nHeight ,nWeight ,nCaseLength ,nCaseWidth ,nCaseHeight ,nCaseWeight ,<br/> dBeginUseDate ,dEndUseDate ,sMemo1 ,sMemo2 ,sMemo3 ,sMemo4 ,sMemo5 ,<br/> nLineSize ,nSafeStockDay ,nOPLM ,sOrderModeID ,sOrderMode , nSalePrice ,<br/> nVipPrice,nSaleTaxRate , sMainBarcode ,nTag ,sChangeUser ,dChangeDate ,dLastUpdateTime<br/> into #Goods001<br/> from tGoods<br/> where nGoodsID = 211</p><p>select nGoodsID, sGoodsDesc, sGoodsName, sSpec , sBrand , nPack , sUnit ,nCaseUnits ,<br/> nCategoryID ,sHome ,sNationID , sNation ,sGoodsLevelID ,sGoodsLevel ,<br/> sGoodTypeID ,sGoodType ,sTradeModeID ,sTradeMode ,sDepositTypeID , sDepositType ,<br/> nMinLineUnit ,nMinLineL ,nShelfLife ,nMinDueDay ,sNote ,nLength ,nWidth ,<br/> nHeight ,nWeight ,nCaseLength ,nCaseWidth ,nCaseHeight ,nCaseWeight ,<br/> dBeginUseDate ,dEndUseDate ,sMemo1 ,sMemo2 ,sMemo3 ,sMemo4 ,sMemo5 ,<br/> nLineSize ,nSafeStockDay ,nOPLM ,sOrderModeID ,sOrderMode , nSalePrice ,<br/> nVipPrice,nSaleTaxRate , sMainBarcode ,nTag ,sChangeUser ,dChangeDate ,dLastUpdateTime<br/> into #Goods002<br/> from tGoods<br/> where nGoodsID = 211</p><p>select id=identity(4), name ,dd = convert(varchar(255), ''),cc=convert(varchar(255), '') <br/> into #Goods003 from tempdb..syscolumns where id = object_id('#Goods001')<br/> <br/> declare @Num1 int<br/> declare @i int<br/> declare @Name varchar(20)<br/> declare @GoodsID varchar(8)</p><p> select @GoodsID ='211'<br/> select @i = 1<br/> select @Num1 = max(id) from #Goods003</p><p>while @i < @Num1+ 1<br/>begin<br/> select @Name = name from #Goods003 where id = @i<br/> exec ( 'update #Goods003 set dd = convert(varchar(255), (select ' + @Name+ ' from #Goods001 where nGoodsID = convert(numeric(8,0) , ' + @GoodsID + ' ) ) )where name = <a href="mailto:'''+@Name+''''">'''+@Name+''''</a> )<br/> exec ( 'update #Goods003 set cc = convert(varchar(255), (select ' + @Name+ ' from #Goods002 where nGoodsID = convert(numeric(8,0) , ' + @GoodsID + ' ) ) )where name = <a href="mailto:'''+@Name+''''">'''+@Name+''''</a> )<br/> select @i = @i + 1 <br/>end</p><p>select * from #Goods003 where dd<>cc</p><p>drop table #Goods001<br/>drop table #Goods002<br/>drop table #Goods003</p> <p>将结果插入一个新表：</p><p>create table tAlterLog(<br/>nID int identity not null,/*ID,序号，没有实际意义*/<br/>dTradeDate datetime not null,/*时间*/<br/>sTableName varchar(30) not null,/*表名*/<br/>sFieldName varchar(30) not null,/*栏位名*/<br/>sOldValue varchar(255) null,/*原值，如果新增资料，此栏位为空*/<br/>sNewValue varchar(255) not null,/*新值*/<br/>sModuleID varchar(20) null,/*产生这条记录的模块编号*/<br/>sVersion varchar(10) null,/*模块对应的版本号*/<br/>sEditer varchar(20) null,/*产生这条记录的操作人*/<br/>constraint PK_ALTERLOG primary key clustered (nID)<br/>on "default"</p><p>)<br/>go</p>
页:
[1]