SQL语句汇总

李维强-15级

----------------------------------------
t_m_pickaddress

PickAddressADDTrigger after  insert

insert into t_log (type,oplog,optime,opid)
select top 1  10,'trigger-新增厂家地址 Pickaddressid='+convert(varchar(100),PickAddressId)+',Address='+Address+',number='+convert(varchar(100),number),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

PickAddressUpdateTrigger after update

declare @log varchar(2000);
set @log='修改生产厂家: ';
select @log+= case when i.address<>d.address then ' address='+convert(varchar(100),i.address) else '' end from inserted i left join deleted d on i.PickAddressId=d.PickAddressId;
select @log+= case when i.number<>d.number then  ' number='+i.number else '' end from inserted i left join deleted d on i.PickAddressId=d.PickAddressId;
insert into t_log (type,oplog,optime,opid) values (10,@log, CONVERT(varchar(100), GETDATE(), 120),0)

-------------------------------------------------------------------------
t_m_PickAccount

PickAccountAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  20,'trigger-新增提货账户 PickAccountId='+convert(varchar(100),PickAccountId)+
',PickAddressId='+convert(varchar(100),PickAddressId)+',PickAccounT='+convert(varchar(100),PickAccounT)+
',number='+convert(varchar(100),number)+',packaging='+convert(varchar(100),packaging),CONVERT(varchar(100), GETDATE(), 120),0
from inserted


PickAccountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-编辑提货账户: PickAccountId='+convert(varchar(100),PickAccountId) from inserted;
select top 1 @log+= case when i.PickAddressId<>d.PickAddressId then ' PickAddressId='+convert(varchar(100),i.PickAddressId) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.PickAccounT<>d.PickAccounT then ' PickAccounT='+convert(varchar(100),i.PickAccounT) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.number<>d.number then ' number='+convert(varchar(100),i.number) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;
select top 1 @log+= case when i.packaging<>d.packaging then ' packaging='+convert(varchar(100),i.packaging) else '' end from inserted i left join deleted d on i.PickAccountId=d.PickAccountId;

insert into t_log (type,oplog,optime,opid) values (20,@log, CONVERT(varchar(100), GETDATE(), 120),0);
-----------------------------------------------------------------------------
t_m_TypePrice

TypePriceAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  30,'trigger-新增规格 GoodsTypeID='+convert(varchar(100),GoodsTypeID)+
',PickAccountId='+convert(varchar(100),PickAccountId)+',GoodsType='+convert(varchar(100),GoodsType)+
',number='+convert(varchar(100),number)+',typeName='+convert(varchar(100),typeName),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TypePriceUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-修改规格: GoodsTypeID='+convert(varchar(100),GoodsTypeID) from inserted;
select top 1 @log+= case when i.PickAccountId<>d.PickAccountId then ' PickAccountId='+convert(varchar(100),i.PickAccountId) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.GoodsType<>d.GoodsType then ' GoodsType='+convert(varchar(100),i.GoodsType) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.Unit<>d.Unit then ' Unit='+convert(varchar(100),i.Unit) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.number<>d.number then ' number='+convert(varchar(100),i.number) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
select top 1 @log+= case when i.typeName<>d.typeName then ' typeName='+convert(varchar(100),i.typeName) else '' end from inserted i left join deleted d on i.GoodsTypeID=d.GoodsTypeID;
insert into t_log (type,oplog,optime,opid) values (30,@log, CONVERT(varchar(100), GETDATE(), 120),0);
---------------------------------------------------------------------------------
t_m_transRecomFee

TransRecomFeeAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  50,'trigger-运费单价新增 transRecomFeeID='+convert(varchar(100),transRecomFeeID)+
',ClientID='+convert(varchar(100),ClientID)+',PickAddressId='+convert(varchar(100),PickAddressId)+
',RecommendFee='+convert(varchar(100),RecommendFee)+',StartTime='+convert(varchar(100),StartTime),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TransRecomFeeDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价 删除: transRecomFeeID='+convert(varchar(100),transRecomFeeID) from deleted;
select top 1 @log+=',ClientID='+convert(varchar(100),d.ClientID)+',PickAddressId='+convert(varchar(100),d.PickAddressId)+',RecommendFee='+convert(varchar(100),d.RecommendFee)+
',StartTime='+convert(varchar(100),d.StartTime) from deleted as d
insert into t_log (type,oplog,optime,opid) values (50,@log, CONVERT(varchar(100), GETDATE(), 120),0);


TransRecomFeeUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价 编辑: transRecomFeeID='+convert(varchar(100),transRecomFeeID) from inserted;
select top 1 @log+= case when i.ClientID<>d.ClientID then ' ClientID='+convert(varchar(100),i.ClientID) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.PickAddressId<>d.PickAddressId then ' PickAddressId='+convert(varchar(100),i.PickAddressId) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.RecommendFee<>d.RecommendFee then ' RecommendFee='+convert(varchar(100),i.RecommendFee) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
select top 1 @log+= case when i.StartTime<>d.StartTime then ' StartTime='+convert(varchar(100),i.StartTime) else '' end from inserted i left join deleted d on i.transRecomFeeID=d.transRecomFeeID;
insert into t_log (type,oplog,optime,opid) values (50,@log, CONVERT(varchar(100), GETDATE(), 120),0);

----------------------------------------------------------------------------------------------------------------------------
t_m_companyAccount

CompanyAccountAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  60,'trigger-公司账户新增 companyAccountID='+convert(varchar(100),companyAccountID)+
',name='+convert(varchar(100),name)+',accountNum='+convert(varchar(100),accountNum),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

CompanyAccountDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-运费单价 删除: companyAccountID='+convert(varchar(100),companyAccountID) from deleted;
insert into t_log (type,oplog,optime,opid) values (60,@log, CONVERT(varchar(100), GETDATE(), 120),0);

CompanyAccountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-公司账户 编辑: companyAccountID='+convert(varchar(100),companyAccountID) from inserted;
select top 1 @log+= case when i.name<>d.name then ' name='+convert(varchar(100),i.name) else '' end from inserted i left join deleted d on i.companyAccountID=d.companyAccountID;
select top 1 @log+= case when i.accountNum<>d.accountNum then ' accountNum='+convert(varchar(100),i.accountNum) else '' end from inserted i left join deleted d on i.companyAccountID=d.companyAccountID;
insert into t_log (type,oplog,optime,opid) values (60,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------------------------------------------------------
T_M_recommSellPrice

RecommSellPriceAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  70,'trigger-推荐销售单价 新增 id='+convert(varchar(100),id)+
',PickAccountId='+convert(varchar(100),PickAccountId)+',clientID='+convert(varchar(100),clientID)+',price='+convert(varchar(100),price)+',optime='+convert(varchar(100),optime)+',GoodsTypeID='+convert(varchar(100),GoodsTypeID),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

RecommSellPriceDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-推荐销售单价 删除: id='+convert(varchar(100),id) from deleted;
insert into t_log (type,oplog,optime,opid) values (70,@log, CONVERT(varchar(100), GETDATE(), 120),0);

RecommSellPriceUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-公司账户 编辑: id='+convert(varchar(100),id) from inserted;
select top 1 @log+= case when i.PickAccountId<>d.PickAccountId then ' PickAccountId='+convert(varchar(100),i.PickAccountId) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.clientID<>d.clientID then ' clientID='+convert(varchar(100),i.clientID) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.price<>d.price then ' price='+convert(varchar(100),i.price) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.optime<>d.optime then ' optime='+convert(varchar(100),i.optime) else '' end from inserted i left join deleted d on i.id=d.id;
select top 1 @log+= case when i.GoodsTypeID<>d.GoodsTypeID then ' GoodsTypeID='+convert(varchar(100),i.GoodsTypeID) else '' end from inserted i left join deleted d on i.id=d.id;
insert into t_log (type,oplog,optime,opid) values (70,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------
t_m_Clientinformation

ClientInformationAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  80,'trigger-客户管理 新增 clientID='+convert(varchar(100),clientID)+',clientName='+convert(varchar(100),clientName)+',number='+convert(varchar(100),number)+',getStuffAddress='+convert(varchar(100),getStuffAddress),CONVERT(varchar(100), GETDATE(), 120),0 from inserted

t_m_clientinformation_update after update
declare @log varchar(2000);
declare @cc int;
declare @UpFlag int;
select top 1 @log='Trigger-客户管理 编辑: clientID='+convert(varchar(100),clientID) from inserted;
select top 1 @log+= case when i.contactNum<>d.contactNum then ' contactNum='+convert(varchar(100),i.contactNum) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.getStuffNum<>d.getStuffNum then ' getStuffNum='+convert(varchar(100),i.getStuffNum) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @UpFlag= case when i.getStuffNum<>d.getStuffNum then 1 else 0 end from inserted i left join deleted d on i.clientID=d.clientID;
if (@UpFlag>0)
        begin
                update t_o_order set getstuffnum=a.getstuffnum from inserted a where a.clientid=t_o_order.clientID and OrderState<3;
                            set @UpFlag=0;
        end
select top 1 @log+= case when i.clientName<>d.clientName then ' clientName='+convert(varchar(100),i.clientName) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.getStuffAddress<>d.getStuffAddress then ' getStuffAddress='+convert(varchar(100),i.getStuffAddress) else '' end from inserted i left join deleted d on i.clientID=d.clientID;
select top 1 @log+= case when i.packaging<>d.packaging then ' packaging='+convert(varchar(100),i.packaging) else '' end from inserted i left join deleted d on i.clientID=d.clientID;


select top 1 @UpFlag= case when i.isDelete<>d.isDelete then 1 else 0 end from inserted i left join deleted d on i.clientID=d.clientID;

if (@UpFlag>0)
        begin
                select top 1 @log='Trigger-客户管理 删除: clientID='+convert(varchar(100),clientID) from deleted;
                            insert into t_log (type,oplog,optime,opid) values (80,@log, CONVERT(varchar(100), GETDATE(), 120),0);
                            set @UpFlag=0;
                           
        end
else
              begin
                            insert into t_log (type,oplog,optime,opid) values (80,@log, CONVERT(varchar(100), GETDATE(), 120),0);
              end

-------------------------------------------------------------------------------------------------
t_c_truck

TruckAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  130,'trigger-车辆管理 新增 truckID='+convert(varchar(100),truckID)+
',ownerName='+convert(varchar(100),ownerName)+',phone='+convert(varchar(100),phone)+',PlateNum='+convert(varchar(100),PlateNum)+',carType='+convert(varchar(100),carType),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

TruckUpdateTrigger after update

declare @log varchar(2000);
declare @UpFlag int;
select top 1 @log='Trigger-车辆管理 编辑: truckID='+convert(varchar(100),truckID) from inserted;
select top 1 @log+= case when i.ownerName<>d.ownerName then ' ownerName='+convert(varchar(100),i.ownerName) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.phone<>d.phone then ' phone='+convert(varchar(100),i.phone) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.PlateNum<>d.PlateNum then ' PlateNum='+convert(varchar(100),i.PlateNum) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.carType<>d.carType then ' carType='+convert(varchar(100),i.carType) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @log+= case when i.GPSIMEI<>d.GPSIMEI then ' GPSIMEI='+convert(varchar(100),i.GPSIMEI) else '' end from inserted i left join deleted d on i.truckID=d.truckID;
select top 1 @UpFlag= case when i.isDelete<>d.isDelete then 1 else 0 end from inserted i left join deleted d on i.truckID=d.truckID;
if (@UpFlag>0)
        begin
                select top 1 @log='Trigger-车辆管理 删除: truckID='+convert(varchar(100),truckID) from deleted;
                            insert into t_log (type,oplog,optime,opid) values (130,@log, CONVERT(varchar(100), GETDATE(), 120),0);
                            set @UpFlag=0;
        end
else
              begin
                            insert into t_log (type,oplog,optime,opid) values (130,@log, CONVERT(varchar(100), GETDATE(), 120),0);
              end

--------------------------------------------------------------------------------------------------
t_c_driver

DriverAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  120,'trigger-运输单位 新增 driverID='+convert(varchar(100),driverID)+
',driverName='+convert(varchar(100),driverName)+',email='+convert(varchar(100),email)+',licensePassTime='+convert(varchar(100),licensePassTime),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

DriverUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-运输单位管理 编辑: driverID='+convert(varchar(100),driverID) from inserted;
select top 1 @log+= case when i.driverName<>d.driverName then ' driverName='+convert(varchar(100),i.driverName) else '' end from inserted i left join deleted d on i.driverID=d.driverID;
select top 1 @log+= case when i.licensePassTime<>d.licensePassTime then ' licensePassTime='+convert(varchar(100),i.licensePassTime) else '' end from inserted i left join deleted d on i.driverID=d.driverID;
select top 1 @log+= case when i.email<>d.email then ' email='+convert(varchar(100),i.email) else '' end from inserted i left join deleted d on i.driverID=d.driverID;

insert into t_log (type,oplog,optime,opid) values (120,@log, CONVERT(varchar(100), GETDATE(), 120),0);

-----------------------------------------------------------------------------------------------------
t_c_driverOwnerBinding

DriverOwnerBindingAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  120,'trigger-司机车辆绑定 新增 BindingID='+convert(varchar(100),BindingID)+
',DriverID='+convert(varchar(100),DriverID)+',TruckID='+convert(varchar(100),TruckID),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

-----------------------------------------------------------------------------------------------------  3-4 21:00
t_o_demand

DemandUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-需求修改 : demandID='+convert(varchar(100),demandID) from inserted;
select top 1 @log+= case when i.clientID<>d.clientID then ' clientID='+convert(varchar(100),i.clientID) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.pickAccount<>d.pickAccount then ' pickAccount='+convert(varchar(100),i.pickAccount) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.pickAddress<>d.pickAddress then ' pickAddress='+convert(varchar(100),i.pickAddress) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.state<>d.state then ' state='+convert(varchar(100),i.state) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.cementType<>d.cementType then ' cementType='+convert(varchar(100),i.cementType) else '' end from inserted i left join deleted d on i.demandID=d.demandID;
select top 1 @log+= case when i.price<>d.price then ' price='+convert(varchar(100),i.price) else '' end from inserted i left join deleted d on i.demandID=d.demandID;

insert into t_log (type,oplog,optime,opid) values (160,@log, CONVERT(varchar(100), GETDATE(), 120),0);

--------------------------------------------------------------------------------------------------------
t_o_demandCount

DemandCountUpdateTrigger after update

declare @log varchar(2000);
select top 1 @log='Trigger-DemandCount 更新: demandCountID='+convert(varchar(100),demandCountID) from inserted;
select top 1 @log+= case when i.Amount<>d.Amount then ' Amount='+convert(varchar(100),i.Amount) else '' end from inserted i left join deleted d on i.demandCountID=d.demandCountID;
select top 1 @log+= case when i.state<>d.state then ' state='+convert(varchar(100),i.state) else '' end from inserted i left join deleted d on i.demandCountID=d.demandCountID;

insert into t_log (type,oplog,optime,opid) values (160,@log, CONVERT(varchar(100), GETDATE(), 120),0);

--------------------------------------------------------------------------------------------------------------
t_c_ClientPayment

ClientPaymentAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  260,'trigger-客户回款 新增 ClientPaymentID='+convert(varchar(100),ClientPaymentID)+
',ClientID='+convert(varchar(100),ClientID)+',companyAccountID='+convert(varchar(100),companyAccountID)+',PaymentMoney='+convert(varchar(100),PaymentMoney)
+',PaymentDate='+convert(varchar(100),PaymentDate)+',memo='+convert(varchar(100),memo)+',type='+convert(varchar(100),type),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

ClientPaymentDelTrigger after delete

declare @log varchar(2000);
select top 1 @log='Trigger-客户回款 删除: ClientPaymentID='+convert(varchar(100),ClientPaymentID) from deleted;
insert into t_log (type,oplog,optime,opid) values (260,@log, CONVERT(varchar(100), GETDATE(), 120),0);

ClientPaymentUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-客户回款 编辑: ClientPaymentID='+convert(varchar(100),ClientPaymentID) from inserted;
select top 1 @log+= case when i.ClientID<>d.ClientID then ' ClientID='+convert(varchar(100),i.ClientID) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.PaymentMoney<>d.PaymentMoney then ' PaymentMoney='+convert(varchar(100),i.PaymentMoney) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.PaymentDate<>d.PaymentDate then ' PaymentDate='+convert(varchar(100),i.PaymentDate) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.companyAccountID<>d.companyAccountID then ' companyAccountID='+convert(varchar(100),i.companyAccountID) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.memo<>d.memo then ' memo='+convert(varchar(100),i.memo) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
select top 1 @log+= case when i.type<>d.type then ' type='+convert(varchar(100),i.type) else '' end from inserted i left join deleted d on i.ClientPaymentID=d.ClientPaymentID;
insert into t_log (type,oplog,optime,opid) values (260,@log, CONVERT(varchar(100), GETDATE(), 120),0);

---------------------------------------------------------------------------------------------------------------------------
t_c_AccountPayment

AccountPaymentAddTrigger after insert

insert into t_log (type,oplog,optime,opid)
select top 1  270,'trigger-账户付款 新增 AccountPaymentID='+convert(varchar(100),AccountPaymentID)+
',PickAccountID='+convert(varchar(100),PickAccountID)+',PaymentMoney='+convert(varchar(100),PaymentMoney)+',PaymentDate='+convert(varchar(100),PaymentDate)
+',companyAccountID='+convert(varchar(100),companyAccountID)+',memo='+convert(varchar(100),memo)+',type='+convert(varchar(100),type),CONVERT(varchar(100), GETDATE(), 120),0
from inserted

AccountPaymentDelTrigger after Delete

declare @log varchar(2000);
select top 1 @log='Trigger-账户付款 删除: AccountPaymentID='+convert(varchar(100),AccountPaymentID) from deleted;
insert into t_log (type,oplog,optime,opid) values (270,@log, CONVERT(varchar(100), GETDATE(), 120),0);

AccountPaymentUpdateTrigger after Update

declare @log varchar(2000);
select top 1 @log='Trigger-账户付款 编辑: AccountPaymentID='+convert(varchar(100),AccountPaymentID) from inserted;
select top 1 @log+= case when i.PickAccountID<>d.PickAccountID then ' PickAccountID='+convert(varchar(100),i.PickAccountID) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.PaymentMoney<>d.PaymentMoney then ' PaymentMoney='+convert(varchar(100),i.PaymentMoney) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.PaymentDate<>d.PaymentDate then ' PaymentDate='+convert(varchar(100),i.PaymentDate) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.companyAccountID<>d.companyAccountID then ' companyAccountID='+convert(varchar(100),i.companyAccountID) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.memo<>d.memo then ' memo='+convert(varchar(100),i.memo) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
select top 1 @log+= case when i.type<>d.type then ' type='+convert(varchar(100),i.type) else '' end from inserted i left join deleted d on i.AccountPaymentID=d.AccountPaymentID;
insert into t_log (type,oplog,optime,opid) values (270,@log, CONVERT(varchar(100), GETDATE(), 120),0);

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


SQLTrigger.rar (2.61 KB, 下载次数: 0)

2017-3-5 19:34 上传

点击文件名下载附件

拟定修改目录日志.rar (7.9 KB, 下载次数: 0)

2017-3-5 19:38 上传

点击文件名下载附件

李维强-15级

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select case when d.DecuctNum is NOT null 
then 
      case when CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>d.DecuctNum then CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice) else 0 end
else 
      case when a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>=0.2      
           THEN
                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)
           WHEN a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)<0.2
           THEN 
                0
           ELSE
                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)
      END 
END as decountFee,
CONVERT(DECIMAL(18,2),-1*(a.produceNum-a.VerifiedNum)) as costNum,
d.DecuctNum,a.VerifiedNum*a.TransPrice as HJJE,
a.FactoryTime,b.driverName,c.PlateNum,a.PickAddress,a.ClientName,a.CementType,a.produceNum,a.VerifiedNum,a.TransPrice
,a.FactoryGetNum,NULL AS YFDetails,NULL AS AccountName,a.Remark,d.state,'吨' as unit


FROM T_O_Order a INNER JOIN T_C_Driver b ON a.DriverID=b.driverID 
INNER JOIN T_C_Truck c ON a.truckID=c.truckID
LEFT JOIN T_M_transFeeAudit d ON a.transFeeAuditNum=d.auditNum

where  a.OrderState=4 AND a.FactoryTime BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'


UNION 
SELECT NULL AS decountFee,NULL as costNum,NULL AS DecuctNum,NULL as HJJE,payDate as FactoryTime,a.driverName,NULL as PlateNum,NULL as PickAddress,NULL AS ClientName,NULL AS CementType,NULL AS produceNum,NULL AS VerifiedNum,NULL AS TransPrice,c.transFeeNum AS FactoryGetNum,Paymoney AS YFDetails,b.name AS AccountName,Remark,NULL AS state,NULL as unit
from T_M_transFeeDetails c INNER JOIN T_M_companyAccount b ON c.companyAccountID=b.companyAccountID
LEFT JOIN (SELECT driverID,transFeeNum,driverName FROM transFeeView GROUP BY transFeeNum,driverName,driverID) AS a ON a.transFeeNum=c.transFeeNum

where c.payDate BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'

ORDER BY FactoryTime ASC

李维强-15级

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select SUM(a.VerifiedNum*a.TransPrice)-SUM(case when d.DecuctNum is NOT null
then
      case when CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>d.DecuctNum then CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice) else 0 end
else
      case when a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)>=0.2      
           THEN
                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)
           WHEN a.CementType LIKE '%散%' AND  CONVERT(DECIMAL(18,2),a.produceNum-a.VerifiedNum)<0.2
           THEN
                0
           ELSE
                CONVERT(DECIMAL(18,2),(a.produceNum-a.VerifiedNum)*a.FactoryPrice)
      END
END) as ToPay,
b.driverName,left(a.FactoryTime,10) as DayTime,NULL AS HasPay


FROM T_O_Order a INNER JOIN T_C_Driver b ON a.DriverID=b.driverID LEFT JOIN T_M_transFeeAudit d ON a.transFeeAuditNum=d.auditNum
where a.TransPrice<>0 AND a.OrderState=4 AND a.FactoryTime BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'
GROUP BY b.driverName,left(a.FactoryTime,10)

UNION

SELECT NULL AS ToPay,a.driverName,left(c.payDate,10) as DayTime,SUM(c.Paymoney) as HasPay
from T_M_transFeeDetails c INNER JOIN T_M_companyAccount b ON c.companyAccountID=b.companyAccountID
LEFT JOIN (SELECT driverID,transFeeNum,driverName FROM transFeeView GROUP BY transFeeNum,driverName,driverID) AS a ON a.transFeeNum=c.transFeeNum

where c.payDate BETWEEN '2017-02-01 21:53:03' AND '2017-03-15 21:53:03'
GROUP BY a.driverName,left(c.payDate,10)

ORDER BY DayTime

李维强-15级

分页查询

[C#] syntaxhighlighter_viewsource syntaxhighlighter_copycode

                sql1 += " select top " + rows.ToString() + " * from ";
                sql2 += " ( SELECT ROW_NUMBER() OVER (order by " + sort + " " + order + ") as rowNumber,ISNULL(SUM(b.quantity),0) AS dispatchNum, a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate";
                sql2 += " FROM DemandCountDetails a LEFT JOIN T_O_Order b ON a.demandcountid=b.demandcountid";
                sql2 += " where " + condition + " GROUP BY a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate)as s";
                sql3 += " where rownumber>" + (rows * (page - 1)).ToString();

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select top 20 * from  ( SELECT ROW_NUMBER() OVER (order by a.demandCountID desc) as rowNumber,ISNULL(SUM(b.quantity),0) AS dispatchNum, 
a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate FROM DemandCountDetails a LEFT JOIN T_O_Order b ON 
a.demandcountid=b.demandcountid where  1=1 and state=0 and companyManageID='2' and dstate=3 GROUP BY 
a.demandCountID,a.dstate,a.salesmanName,a.clientName,a.GoodsType,a.Amount,a.alreadyTransport,a.sransportType,a.state,a.releaseDate)as s where rownumber>0

李维强-15级

环球

SELECT top 20 * FROM (
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.Title LIKE '%法%'
UNION
--作者
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId where b.AuthorName LIKE '%法%'
) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype
ORDER BY posttime DESC

差关键词

李维强-15级

SELECT top 20 * FROM (
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,'' as test
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture',''
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.Title LIKE '%法%'
UNION
--作者
SELECT ArticleId,Title1,b.AuthorName,PostTime,'article',''
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture',''
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP',''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where b.AuthorName LIKE '%法%'
--关键词
UNION
SELECT b.ArticleId,c.Title1,d.AuthorName,c.PostTime,'article' as srcType,''
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_article c ON c.ArticleId=b.ArticleId
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=1

) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype,test
ORDER BY posttime DESC


SELECT REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword where Name like '%法%' for xml path('')),1,0,'')),'<name>','')

以下为最终版，关键是 LIKE '%法%'-----------------------------------------------------------------
SELECT top 20 * FROM (
SELECT a.ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=a.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Title1 LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=a.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND Name LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.Title LIKE '%法%'
UNION
--作者
SELECT a.ArticleId,Title1,b.AuthorName,PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=a.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
from t_d_article a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT a.pictureid,a.name,b.AuthorName,PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=a.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_picture a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where a.IsDelete<>1 AND b.AuthorName LIKE '%法%'
UNION
SELECT splistid,title,b.authorname,posttime,'SP' as srcType,''
FROM t_d_SPList a INNER JOIN t_d_author b ON a.AuthorId=b.AuthorId
where b.AuthorName LIKE '%法%'
--关键词
UNION
SELECT b.ArticleId,c.Title1,d.AuthorName,c.PostTime,'article' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.ArticleId=b.ArticleId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_article c ON c.ArticleId=b.ArticleId
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=1
UNION
SELECT b.pictureid,c.name,d.AuthorName,c.PostTime,'picture' as srcType,
REPLACE((SELECT STUFF((SELECT name FROM t_d_keyword x INNER JOIN t_d_KWRelation y ON x.KeyWordId=y.KeyWordId  where y.PictureId=b.PictureId for xml path('')),1,0,'')),'<name>','') as kw
FROM t_d_keyword a INNER JOIN t_d_KWRelation b ON a.KeyWordId=b.KeyWordId
INNER JOIN t_d_picture c ON c.pictureid=b.pictureid
INNER JOIN t_d_author d ON c.AuthorId=d.AuthorId
where a.Name like '%法%' AND a.type=2

) AS s
GROUP BY Articleid,title1,authorname,posttime,srctype,kw
ORDER BY posttime DESC

李维强-15级

这个例子其实是非常的简单了就是要实现sql插入数据时，如果已经存在，则执行update更新了，这样对于sql与程序来讲是非常的简洁的，下面就和小编一起来深入的分析一下吧．
在很多项目中，我们需要对数据进行不断的调用和更新，如果有新的数据过来，还要把它加入到数据库中。其中非常重要的一种情况就是，我们不知道传过来的数据是不是原本数据库中就已经有了的记录，所以我们常常需要先通过抓取数据，判断是否存在，如果存在执行update，如果不存在执行insert，这样就要进行两次数据库操作，第一次是查询，第二次是更新或插入，有没有一种方法可以只需要执行一次操作即可呢？答案是有的。

INSERT ... ON DUPLICATE KEY UPDATE 方法可以帮助我们非常好的解决这一问题。让我们来看一个例子：

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE b=b-1,c=c+1;

这一个语句就可以实现，在插入(1,2,3)这条记录的时候，检查是否存在a=1，如果有a=1的记录，那么更新a=1这条记录，相当于执行了一次下面这个语句：

UPDATE table SET b=b-1,c=c+1 WHERE a=1;

如果数据库中不存在a=1这条记录，那么就插入新的记录。

使用INSERT ... ON DUPLICATE KEY UPDATE的前提是，a字段被设置为“唯一键”索引，否则该方法是无效的。

再看一些例子

该语句是基于唯一索引或主键使用，比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1，下面两个语句会有相同的效果：

INSERT INTO table (a,b,c) VALUES (1,2,3)  
  ON DUPLICATE KEY UPDATE c=c+1;  
  
UPDATE table SET c=c+1 WHERE a=1;

ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。使用ON DUPLICATE KEY UPDATE，最终如果插入了一个新行，则受影响的行数是1，如果修改了已存在的一行数据，则受影响的行数是2。

如果字段b也被加上了unique index，则该语句和下面的update语句是等效的：

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2匹配了多行，则只有一行会被修改。通常的，在ON DUPLICATE KEY UPDATE语句中，我们应该避免多个唯一索引的情况。如果需要插入或更新多条数据，并且更新的字段需要根据其它字段来运算时，可以使用如下语句：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  
       在ON DUPLICATE KEY UPDATE后面使用VALUES()方法，这个语句等同于下面的两个语句：

INSERT INTO table (a,b,c) VALUES (1,2,3)  
  ON DUPLICATE KEY UPDATE c=3;--1+2  
INSERT INTO table (a,b,c) VALUES (4,5,6)  
  ON DUPLICATE KEY UPDATE c=9;--4+5

如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果通过INSERT ... ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是4。如果想解决该问题，可以通过如下语句：

INSERT INTO table (a,b,c) VALUES (1,2,3)  
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;  
       重点是这句id=LAST_INSERT_ID(id)。

还有一种方法是使用ignore，我们来看一个例子：

INSERT ignore INTO a(id, type)  VALUES  ( 11, 22)

这个语句中使用了ignore，意思是：如果数据库中存在一条记录id=11，那么就不执行insert操作（忽略），只有上述条件不满足时才执行插入操作。ignore方法也要求这里的id为唯一键（主键默认就是是唯一键，因此id可以是主键）

另外，还有一种方法是replace into，它的使用方法和insert into一样，但是和上面的ignore效果不同，如果数据库中已经存在id=11，那么强制替换id=11这条记录的type为22。

看个例子


下面通过代码说明之间的区别，如下：

create table testtb(
id int not null primary key,
name varchar(50),
age int
);
insert into testtb(id,name,age)values(1,"bb",13);
select * from testtb;
insert ignore into testtb(id,name,age)values(1,"aa",13);
select * from testtb;//仍是1，“bb”,13，因为id是主键，出现主键重复但使用了ignore则错误被忽略
replace into testtb(id,name,age)values(1,"aa",12);
select * from testtb; //数据变为1,"aa",12


总结一下：

如果要实现插入数据时检查是否已经存在某个唯一键的数据，如果存在，则替换该记录的其他字段，我们可以使用三种方法来实现插入数据时判断是否存在对应键的记录，分别是INSERT ... ON DUPLICATE KEY UPDATE、insert gnore into和replace into。其中INSERT ... ON DUPLICATE KEY UPDATE和replace into可以实现如果已经存在对应键的记录时，替换该记录的其他字段。

李维强-15级

在sqlserver2005之前，要实现递归功能比较麻烦，比如可能会要用到临时表与while语句来循环。自sqlserver2005之后，新增了with as功能语法，即 公用表达式(CTE),让递归实现起来变的简单了。

本章我们主要演示如何利用with as功能实现一个简单的递归功能。
在这之前先看一下cte的语法：

[ WITH <common_table_expression > [ ,...n ] ]
<common_table_expression >::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
参数说明：
expression_name:
公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression > 子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name:
在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与CTE_query_definition结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition:
指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition的 SELECT 语句必须满足与创建视图时相同的要求。
如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。

--开始实例演示--

先创建一个仓库表，表名为Storage_Depository，该表有三个字段ID(仓库编号),DName(仓库名称),PID(父仓库编号).
通过这样一个简单表，就可以将所有仓库信息，通过DID与PID字段来创建一个树型结构。
创建表的sql语句：

Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
然后往该表插入演示数据：

insert into Storage_Depository(DID,DName,PID)
select 'A','A仓库',null
union all
select 'A-1','A-1仓库','A'
union all
select 'A-2','A-2仓库','A'
union all
select 'A-1-1','A-1-1仓库','A-1'
union all
select 'B','B仓库',null
从上面的数据可以看的出来，A的子仓为A-1与A-2仓，而A-1-1为A-1的子仓，B仓是一个独立的仓库，与A仓平级。
下面，我们通过with as功能，查出A仓下面的所有子仓：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID='A'
union all
select A.DID,A.DName,A.PID from Storage_Depository A,w_Storage_Depository B where A.PID=B.DID
)
select * from w_Storage_Depository
代码很简短，也非常容易让人理解.

反过来，比如我们要查出A-1-1仓的所有上级仓，稍稍改一下上面的sql语句就可以了：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID='A-1-1'
union all
select A.DID,A.DName,A.PID from Storage_Depository A,
w_Storage_Depository B where A.DID=B.PID
)
select * from w_Storage_Depository
很简单吧，熟练使用CTE后，会发现它会给我们的工作带来很大的便利.
--------------------------------------------------

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

BEGIN 
with cte as(
select UserName,PhoneNum,UpUserId,UserId 
FROM t_d_user where UserId=5
union ALL
SELECT a.UserName,a.PhoneNum,a.UpUserId,a.UserId 
FROM t_d_User a INNER JOIN cte b ON a.UserId=b.UpUserId
)
SELECT * FROM cte
END

李维强-15级

exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：
  select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要
exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
  select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)  
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

而not exists 和not in 分别是exists 和 in 的 对立面。

exists （sql 返回结果集为真）  
not exists (sql 不返回结果集为真）

下面详细描述not exists的过程：

如下：
表A
ID NAME  
1   A1
2   A2
3   A3

表B
ID AID NAME
1   1     B1
2   2     B2  
3   2     B3

表A和表B是１对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)


有时候我们会遇到要选出某一列不重复,某一列作为选择条件,其他列正常输出的情况.

如下面的表table:

Id  Name  Class Count  Date

1   苹果    水果    10     2011-7-1

1   桔子    水果    20     2011-7-2

1   香蕉    水果    15     2011-7-3

2   白菜    蔬菜    12     2011-7-1

2   青菜    蔬菜    19     2011-7-2

如果想要得到下面的结果Id唯一,Date选最近的一次)

1   香蕉    水果    15     2011-7-3

2   青菜    蔬菜    19     2011-7-2

正确的SQL语句是:

SELECT Id, Name, Class, Count, Date
FROM table t
WHERE (NOT EXISTS
          (SELECT Id, Name, Class, Count, Date FROM table
         WHERE Id = t.Id AND Date > t.Date))

如果用distinct,得不到这个结果, 因为distinct是作用与所有列的

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

结果是表table的所有不同列都显示出来,如下所示:

1   苹果     水果    10     2011-7-1

1   桔子    水果    20     2011-7-2

1   香蕉    水果    15     2011-7-3

2   白菜    蔬菜    12     2011-7-1

2   青菜    蔬菜    19     2011-7-2

如果用Group by也得不到需要的结果,因为Group by 要和聚合函数共同使用,所以对于Name,Class和Count列要么使用Group by,要么使用聚合函数. 如果写成

SELECT Id, Name, Class, Count, MAX(Date)
FROM table
GROUP BY Id, Name, Class, Count

得到的结果是

1   苹果     水果    10    2011-7-1

1   桔子    水果    20     2011-7-2

1   香蕉    水果    15     2011-7-3

2   白菜    蔬菜    12     2011-7-1

2   青菜    蔬菜    19     2011-7-2

如果写成

SELECT Id, MAX(Name), MAX(Class), MAX(Count), MAX(Date)
FROM table
GROUP BY Id

得到的结果是:

1   香蕉    水果    20     2011-7-3

2   青菜    蔬菜    19     2011-7-2

如果用in有时候也得不到结果,（有的时候可以得到，如果Date都不相同（没有重复数据），或者是下面得到的Max（Date）只有一个值）

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

WHERE (Date IN
          (SELECT MAX(Date)
         FROM table
         GROUP BY Id))

得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）

1   桔子    水果    20     2011-7-2

1   香蕉    水果    15     2011-7-3

2   青菜    蔬菜    19     2011-7-2

注意in只允许有一个字段返回

有一种方法可以实现：

SELECT Id, Name, Class, COUNT, Date
FROM table1 t
WHERE (Date =
          (SELECT MAX(Date)
         FROM table1
         WHERE Id = t .Id))

李维强-15级

在不知道表名、字段名的情况下，只知道某个值，查找出关于这个值所在的表与字段的位置

以下在ZZWine这个数据库测试的，应用时需要更换数据库名称

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

declare @key varchar(30)
set @key = '李维强' --替换为要查找的字符串
DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)
DECLARE @sql VARCHAR(2000)
declare @tsql varchar(8000)
DECLARE tabCursor CURSOR FOR
SELECT name from ZZWine.dbo.sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'
OPEN tabCursor
FETCH NEXT from tabCursor INTO @tabName
WHILE @@fetch_status = 0
BEGIN
set @tsql = ''
DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) --and xtype=167
OPEN colCursor
FETCH NEXT from colCursor INTO @colName
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'if(exists(select * from ' + @tabName + ' where '
SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from '
set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''
+ @tabName + ''' as TableName end'
set @tsql = @tsql + @sql + ';'
print @tsql
FETCH NEXT from colCursor INTO @colName
END
exec(@tsql)
CLOSE colCursor
DEALLOCATE colCursor
FETCH NEXT from tabCursor INTO @tabName
END
CLOSE tabCursor
DEALLOCATE tabCursor