SQL语句汇总

李维强-15级 · 发表于 2017-3-5 19:34:51

本帖最后由李维强-15级于 2017-3-5 19:38 编辑

----------------------------------------
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)

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

李维强-15级 · 发表于 2017-3-1 14:40:47

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

----------------------------------------
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)

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

李维强-15级 · 发表于 2016-9-5 13:53:13

统计司机上传磅单模板

SELECT a.*,b.driverName,c.PlateNum 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 WHERE NOT img is null AND OrderState>2 AND OrderNumID LIKE '201608%' AND orderID not in(
SELECT orderid FROM T_O_Order WHERE NOT img is null AND OrderState<4 AND OrderNumID LIKE '201608%'
) ORDER BY a.DriverID,StartTime

复制代码

李维强-15级 · 发表于 2016-8-15 23:28:38

汇总表单查询语句

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select left(starttime,10) 日期,sum(case when orderstate=0 and acceptstate=2 then 1 else 0 end) as 拒绝,sum(case when orderstate=0 and acceptstate=1 then 1 else 0 end) as 待确认,sum(case when orderstate in(1,2) then 1 else 0 end) as 已接单未交货,sum(case when OrderState=3 and img is null then 1 else 0 end) as 已交货未传磅单,sum(case when OrderState=3 and not img is null then 1 else 0 end) as 已传磅单,sum(case when OrderState=4 then 1 else 0 end) as 已复核,sum(case when 1=1 then 1 else 0 end) as 合计 from t_o_order where orderid>6371  group by left(starttime,10) having left(starttime,10)>'2016-08'
union select left(starttime,7)+'合计',sum(case when orderstate=0 and acceptstate=2 then 1 else 0 end) as 拒绝,sum(case when orderstate=0 and acceptstate=1 then 1 else 0 end) as 待确认,sum(case when orderstate in(1,2) then 1 else 0 end) as 已确认未交货,sum(case when OrderState=3 and img is null then 1 else 0 end) as 已交货未传磅单,sum(case when OrderState=3 and not img is null then 1 else 0 end) as 已传磅单,sum(case when OrderState=4 then 1 else 0 end) as 已统计,sum(case when 1=1 then 1 else 0 end) as 合计 from t_o_order where orderid>6371 group by left(starttime,7)

李维强-15级 · 发表于 2016-6-11 15:49:36

===销售明细表
SELECT a.ClientName,'售出' AS detail,0 AS payback,a.SellPrice*a.VerifiedNum AS sellnum,0 AS total,a.FactoryTime,d.name AS sellMan
FROM T_O_Order a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE a.OrderState=4 AND a.FactoryTime BETWEEN '2016-01-06 11:03:15' AND '2016-06-05 11:03:23'
-- 按照袋装散装按照业务员按照客户筛选 --按照区域 --按照客户编码
--AND b.packaging=1 AND d.operatorID=48 AND b.clientID=43 AND b.number like '4%' AND b.number>='40317' AND b.number<='40317'

UNION
SELECT e.clientName,'回款' AS detail,e.PaymentMoney,0,0,e.PaymentDate,d.name as sellman
FROM ClientPaymentView e INNER JOIN T_M_ClientInformation b ON e.ClientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE e.PaymentDate BETWEEN '2016-01-06 11:03:15' AND '2016-06-05 11:03:23'

--AND d.operatorID=48 AND b.clientID=43 AND b.number like '40%' AND b.number<='40317'

UNION
SELECT clientName,'期初数据' AS detail,SUM (sumpayback),SUM (sumsellnum),SUM (sumsellnum - sumpayback),'2016-01-06 11:03:15',sellMan
FROM (
SELECT sum(e.paymentmoney) AS sumpayback,0 AS sumsellnum,e.clientName,d.name AS sellMan
FROM ClientPaymentView e INNER JOIN T_M_ClientInformation b ON e.ClientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE e.PaymentDate < '2016-01-06 11:03:15'
-- con
GROUP BY e.clientName,d.name
UNION ALL
SELECT 0 AS sumpayback,SUM(a.sellprice * a.verifiednum) AS sumsellnum,b.clientName,d.name AS sellMan
FROM T_O_Order a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID
INNER JOIN T_M_ClientAndSalesman c ON b.clientID=c.clientID AND c.isMaster=1
INNER JOIN T_P_Operator d ON c.operatorID=d.operatorID
WHERE a.OrderState=4 AND a.FactoryTime < '2016-01-06 11:03:15'
-- con
GROUP BY b.clientName,d.name
) AS a
GROUP BY clientName,sellMan

ORDER BY ClientName,FactoryTime

李维强-15级 · 发表于 2016-5-15 13:07:05

inner join on 和where的区别 \\

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

SELECT a.clientname,a.money,b.clientName,b.money 
FROM redu a INNER JOIN my b ON a.clientName=b.clientName WHERE a.money=b.money

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

SELECT a.clientname,a.money,b.clientName,b.money 
FROM redu a INNER JOIN my b ON a.clientName=b.clientName AND a.money=b.money

我原意是  查找 a表和b表里面 clientname 相等的情况下 money 也相等的

然后我用了上面2条语句来查询  查询结果都是一样的但是我想搞清楚其中不同的意义在哪里

答案：

INNER JOIN 是一样的。
如果换成 left join 或right join ，是有区别的；

A left join  B on 后面的语句，是对表 B 数据的过滤，而 where 是对结果集的过滤；

李维强-15级 · 发表于 2016-5-12 10:59:01

本帖最后由李维强-15级于 2016-5-12 11:02 编辑

----增加车辆
INSERT INTO [T_C_Truck] ([PlateNum], [OwnerName], [IDCardNum], [Phone], [ModelType], [LoadCapacity], [FrameNum], [GeneratorNum], [EnviroVerifyDate], [EnviroNum], [EnviroActiveDate], [CIATAVGetDate], [CIATAVPassDate], [InsuranceGetDate], [InsurancePassDate], [TaxGetDate], [RTCGetDate], [RTCPassDate], [MaintainDate], [MaintainPassDate], [YearVerifyDate], [YearVerifyPassDate], [BorrowTimes], [PaybackBeginDate], [PaybackPassDate], [MonthDate], [PaybackMoney], [PaybackTotal], [ContractActDate], [CashDeposit], [ManageCost], [ManagePayDate], [ExtraOilCard], [GPSIMEI], [active], [addDate]) VALUES('桂AD5972', '临时车', '0', '0', '0', '0', '0', '0', '2016-04-30', '0', '2025-04-01', '2016-04-30', '2025-04-23', '2016-04-30', '2025-04-08', '2016-04-30', '2025-04-08', '2025-04-30', '2016-04-30', '2026-04-08', '2016-04-30', '2027-04-05', '1', '2016-04-30', '2025-04-17', '1', '1', '1', '2025-04-03', '1', '1', '2025-04-04', '无', '', '0', '2016-04-30 17:41:36')

--找生成厂家  生成2个结果结果1是不在系统内的  结果2是和系统关联的总数
SELECT a.[生产厂家] FROM temp3 a where a.[生产厂家] not IN(
SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
)
SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address

--把系统里面pickaddressID更新到temp表
UPDATE a SET a.pickaddressID=b.pickaddressID FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address

--查看客户哪些和系统对不起的
SELECT a.* FROM temp3 a where a.[客户] not IN(
SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
)
--改客户名字
--UPDATE temp3 SET [客户]='中铁六局－良庆大道（个人月结）' WHERE [客户]='中铁六局－良庆大道（个人现结）'
--最后扫描一下看客户名字和系统的数据能否对得上
SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
--最后把clientID填到temp3里面去
UPDATE a SET a.clientID=b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName

--检查pickaccountID对不对
SELECT a.* FROM temp3 a where a.[提货账户] NOT IN(
SELECT a.[提货账户] FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT )

--扫描一次提货账户看数量对不对
SELECT a.[提货账户],a.pickAccountID,b.PickAccountId FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT
--把pickaccountID写到temp3里面去
UPDATE a SET a.pickaccountID=b.pickAccountID FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT

--检查规格  相关操作
SELECT a.[生产厂家],a.[水泥规格],a.cementTypeID,b.Address,b.GoodsType,b.GoodsTypeID
FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId
--把cementTypeID通过typeView写入temp里面
UPDATE a SET a.cementTypeID=b.goodsTypeID
FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId

--检查operatorID
SELECT a.* FROM temp3 a WHERE a.[业务员] NOT IN(SELECT a.[业务员] FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name )
--把对不上的业务员纠错
UPDATE temp3 SET [业务员]='陆伟明业务' WHERE [业务员]='陆伟明'
--扫描最终结果  看行数是否一致
SELECT a.[业务员],a.operatorID,b.name,b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name
--把t_p_operator表里面的operatorID写到temp3里面去
UPDATE a SET a.operatorID=b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name

--查看发货时间是否唯一
--SELECT COUNT(发货时间) as aaa,发货时间 FROM temp3 GROUP BY 发货时间 ORDER BY aaa DESC
--SELECT * FROM temp3 where 发货时间='2016-03-31 10:40:23'
--
--DELETE FROM temp3 WHERE 发货时间='2016-03-31 10:40:23'
--确定时间唯一了过后用replace 转换成需要格式
--SELECT a.[发货时间],replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','') FROM temp3 a
--最后把demandID写入temp3里面
--UPDATE temp3 SET demandID=replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','')

--查找运输单位不在系统里面的1
SELECT 运输单位,车牌号 FROM temp3 WHERE [运输单位]='自提1'
--更新自提1
UPDATE temp3 SET [运输单位]='自提1' WHERE [运输单位]='自提'

SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN T_C_Driver b ON a.[运输单位]=b.driverName
)

--查找车牌号不在系统里面的
SELECT a.* FROM temp3 a where a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
)

--
--查找运输单位和车牌绑定不符合系统规则的
SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
) or
a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)
--一下两条select出两个结果  对比
SELECT a.* FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
SELECT * FROM temp3

--更新driverID 和truckID
UPDATE a SET a.driverID=b.driverID,a.truckID=b.truckID
FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum

--更新T_O_Demand表
INSERT INTO T_O_Demand
SELECT a.demandID,a.demandID,'2',a.cementTypeID,销售单价,a.clientID,a.pickaccountID,a.PickaddressID,业务员,b.getstuffNum,3,null,CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,发货时间,发货时间,a.operatorID
from temp3 a INNER JOIN T_M_ClientInformation b ON a.clientID=b.clientID

--更新T_O_demandCount
INSERT INTO T_O_demandCount(amount,demandID,releaseDate,operatorID,sransportType,state,alreadyTransport)
SELECT  CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,demandID,发货时间,operatorID,1,1,CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END FROM temp3

--更新INTO T_O_Order 表注意 a.demandID+'00'这个后面的数字可以变更
INSERT INTO T_O_Order
(orderNumID,demandcountID,getstuffAddress,clientName,getStuffNum,pickaddress,quantity,startTime,cementType,sellprice,transprice,salesmanName,pickaccount,defaultprctime,startTranstime,endtranstime,verifiedTime,remark,factoryGetNum,verifiedNum,verifiedName,driverID,factoryPrice,factoryTime,truckID,orderType,acceptState,orderState,clientID,pickaccountID,produceNum,diaoduyuanID,tongjiyuanID)
SELECT a.demandID+'00',b.demandCountID,c.getstuffAddress,c.clientName,c.GetStuffNum,a.[生产厂家],CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,a.发货时间,水泥规格,销售单价,运费,业务员,提货账户,30,发货时间,发货时间,发货时间,备注,交货单号,验收数量,'3月导入（2次问题修改后）',driverID,出厂单价,发货时间,truckID,1,3,4,a.clientID,pickaccountID,出库数量,107,103 FROM temp3 a
INNER JOIN T_O_demandCount b ON a.demandID=b.demandID
INNER JOIN T_M_ClientInformation c ON a.clientID=c.clientID

--账户付款，看那些公司账户不在系统内
SELECT a.* FROM temp3_pay a WHERE a.[付款账户] NOT IN(
SELECT a.付款账户 FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name
)
--修改公司账户信息
UPDATE temp3_pay SET 付款账户='热度南宁市区农村信用合作联社石埠信用社' WHERE 付款账户='热度南宁市区农村信用合作联社石埠信用'
--扫描一次公司账户
SELECT a.付款账户 FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name
--更新companyaccID
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp3_pay a INNER JOIN T_M_companyAccount b ON a.付款账户=b.name

--账户付款，查找提货账户哪些不在系统里面
SELECT * FROM temp3_pay a WHERE a.[提货账户] NOT IN(
SELECT 提货账户 FROM temp3_pay a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.pickaccounT
)
--修改提货账户
UPDATE temp3_pay SET 提货账户='南宁热度（袋）' WHERE 提货账户='南宁厂热度（袋）'
--更新 pickaccountID
UPDATE a set a.pickaccountID=b.pickaccountID FROM temp3_pay a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.pickaccounT
--查找操作员不在系统里面的
SELECT * FROM temp3_pay a WHERE a.[操作员] NOT IN(
SELECT 操作员 FROM temp3_pay a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--更新operatorID
UPDATE a SET a.operatorID=b.operatorID FROM temp3_pay a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
--更新addDate
UPDATE temp3_pay SET addDate='2016-03-10 03:03:03'
--更新paymentDate
UPDATE a SET  a.paymentDate=CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]),20) FROM temp3_pay a
--把结果插入到t_c_accountPayment 里面
INSERT INTO T_C_AccountPayment
(pickaccountID,paymentMoney,paymentDate,operatorID,addDate,companyAccountID,memo,type)
SELECT pickaccountID,总金额,paymentDate,operatorID,addDate,companyaccID,memo,type
FROM temp3_pay  --temp3_pay要改哦

--客户回款  更新type和memo
UPDATE temp3_get SET type=[收款方式],memo=[票号]
--客户回款改变回款时间为唯一值
UPDATE a SET a.paymentDate=CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20) FROM temp3_get a
--客户回款  找到客户回款里面客户名与系统不相同的
SELECT a.* FROM temp3_get a WHERE a.[往来单位] not IN(
SELECT 往来单位 FROM temp3_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
)
--修改客户名称
UPDATE temp3_get SET [往来单位]='周小华－陆屋工地（公司现结）' WHERE [往来单位]='周小华（公司现结）'
--更新客户回款 clientID
UPDATE a SET a.clientID=b.clientID FROM temp3_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
--找到系统里面没有的公司账户
SELECT 收款账户 FROM temp3_get WHERE 收款账户 NOT IN(
SELECT 收款账户 FROM temp3_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
)
--客户回款  更新companyaccountID到temp3_get
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp3_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
--查看操作员是否都在系统里面
SELECT 操作员 FROM temp3_get a WHERE 操作员 not IN(
SELECT 操作员 FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--查看操作员是否都在系统里面
SELECT 操作员 FROM temp3_get a WHERE 操作员 not IN(
SELECT 操作员 FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
)
--更新operatorID到temp3_get里面
UPDATE a set a.operatorID=b.operatorID FROM temp3_get a INNER JOIN T_P_Operator b ON a.[操作员]=b.name
--插入到t_c_clientPayment表里面
INSERT INTO T_C_ClientPayment
(clientID,PaymentMoney,paymentdate,OperatorID,AddDate,companyAccountID,memo,type)
SELECT a.clientID,a.[总金额],CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20),a.operatorID,'2016-03-10 03:03:03',a.companyaccID,a.memo,a.type
FROM temp3_get a  --temp3_get要改哦

temp.rar (3.02 KB, 下载次数: 6)

李维强-15级 · 发表于 2016-5-3 02:59:38

本帖最后由李维强-15级于 2016-5-3 05:34 编辑

导入大量数据用到的sql

----------------------------------------------------------------------2016-5-2

--查看生产厂家哪些和系统对不起的。update把对不起的改成对得起的
--SELECT a.PickAddressId,a.[生产厂家],b.PickAddressId FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
--ORDER BY a.PickAddressId
--UPDATE a SET a.[生产厂家]='南宁华润' FROM temp3 a where a.[生产厂家] not in(
--SELECT a.* FROM temp3 a WHERE a.[生产厂家] NOT IN(
--SELECT a.[生产厂家] FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address
--)
--把系统里面pickaddressID更新到temp表
--UPDATE a SET a.pickaddressID=b.pickaddressID FROM temp3 a INNER JOIN T_M_PickAddress b ON a.[生产厂家]=b.Address

--查看客户哪些和低筒对不起的
--SELECT a.[客户],a.clientID,b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName
--SELECT a.* FROM temp3 a WHERE a.[客户] not IN(SELECT a.[客户] FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName)
--结合t_m_clientinfomation修改对不起的客户名
--UPDATE temp3 SET [客户]='龙岗合景天峻广场（个人现结）' WHERE [客户]='龙岗合景天骏广场（个人现结）'
--最后把clientID填到temp3里面去
--UPDATE a SET a.clientID=b.clientID FROM temp3 a INNER JOIN T_M_ClientInformation b ON a.[客户]=b.clientName

--检查pickaccountID对不对
--SELECT a.* FROM temp3 a where a.[提货账户] NOT IN(
--SELECT a.[提货账户] FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT )
--扫描一次
--SELECT a.[提货账户],a.pickAccountID,b.PickAccountId FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT
--把pickaccountID写到temp3里面去
--UPDATE a SET a.pickaccountID=b.pickAccountID FROM temp3 a INNER JOIN T_M_PickAccount b ON a.[提货账户]=b.PickAccounT

--检查规格  相关操作
--SELECT a.[生产厂家],a.[水泥规格],a.cementTypeID,b.Address,b.GoodsType,b.GoodsTypeID FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]

=b.GoodsType AND a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId
--把cementTypeID通过typeView写入temp里面
--UPDATE a SET a.cementTypeID=b.goodsTypeID FROM temp3 a INNER JOIN TypeView b ON a.[水泥规格]=b.GoodsType AND

a.pickaddressID=b.PickAddressId AND a.pickAccountID=b.PickAccountId

--检查operatorID
--SELECT a.* FROM temp3 a WHERE a.[业务员] NOT IN(SELECT a.[业务员] FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name )
--把对不上的业务员纠错
--UPDATE temp3 SET [业务员]='陆伟明业务' WHERE [业务员]='陆伟明'
--扫描最终结果  看行数是否一致
--SELECT a.[业务员],a.operatorID,b.name,b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name
--吧t_p_operator表里面的operatorID写到temp3里面去
--UPDATE a SET a.operatorID=b.operatorID FROM temp3 a INNER JOIN T_P_Operator b ON a.[业务员]=b.name

--查看发货时间是否唯一
--SELECT COUNT(发货时间) as aaa,发货时间 FROM temp3 GROUP BY 发货时间 ORDER BY aaa DESC
--SELECT * FROM temp3 where 发货时间='2016-03-31 10:40:23'
--
--DELETE FROM temp3 WHERE 发货时间='2016-03-31 10:40:23'
--确定时间唯一了过后用replace 转换成需要格式
--SELECT a.[发货时间],replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','') FROM temp3 a
--最后把demandID写入temp3里面
--UPDATE temp3 SET demandID=replace(REPLACE(REPLACE(发货时间, '-', ''),':',''),' ','')

--查找运输单位不在系统里面的
--SELECT 运输单位 FROM temp3 WHERE [运输单位]='自提'
--UPDATE temp3 SET [运输单位]='自提1' WHERE [运输单位]='自提'
--SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
--SELECT a.[运输单位] FROM temp3 a INNER JOIN T_C_Driver b ON a.[运输单位]=b.driverName
--)

--查找车牌号不在系统里面的
--SELECT a.* FROM temp3 a where a.[车牌号] NOT IN(
--SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
--)
--更新车牌
--UPDATE temp3 SET 车牌号='桂AC7365'  WHERE  车牌号='AC7365'
--删除不在系统内的车牌号
--DELETE FROM temp3  where 车牌号 NOT IN(
--SELECT a.[车牌号] FROM temp3 a INNER JOIN T_C_truck b ON a.[车牌号]=b.plateNum
--)

--查找运输单位和车牌绑定不符合系统规则的
SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
) AND
a.[车牌号] NOT IN(
SELECT a.[车牌号] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)

--删除运输单位和车牌绑定不符合系统规则的
--DELETE FROM temp3 where 运输单位 NOT IN(
--SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
--)

SELECT a.* FROM temp3 a where a.[运输单位] NOT IN(
SELECT a.[运输单位] FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
)

SELECT a.* FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌号]=b.PlateNum
--UPDATE a SET a.driverID=b.driverID,a.truckID=b.truckID FROM temp3 a INNER JOIN DriverCarView b ON a.[运输单位]=b.driverName AND a.[车牌

号]=b.PlateNum

--更新T_O_Demand
INSERT INTO T_O_Demand
SELECT a.demandID,a.demandID,'2',a.cementTypeID,销售单价,a.clientID,a.pickaccountID,a.PickaddressID,业务员,b.getstuffNum,3,null,CASE WHEN

水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,发货时间,发货时间,a.operatorID from temp3 a INNER JOIN T_M_ClientInformation b ON

a.clientID=b.clientID

--更新T_O_demandCount
--INSERT INTO T_O_demandCount(amount,demandID,releaseDate,operatorID,sransportType,state,alreadyTransport)
--SELECT  CASE WHEN 水泥规格 LIKE'%散%' THEN 1 ELSE 出库数量 END,demandID,发货时间,operatorID,1,1,CASE WHEN 水泥规格 LIKE'%散%' THEN 1

ELSE 出库数量 END FROM temp3

--更新INTO T_O_Order 表
INSERT INTO T_O_Order
(orderNumID,demandcountID,getstuffAddress,clientName,getStuffNum,pickaddress,quantity,startTime,cementType,sellprice,transprice,salesmanN

ame,pickaccount,defaultprctime,startTranstime,endtranstime,verifiedTime,remark,factoryGetNum,verifiedNum,verifiedName,driverID,factoryPri

ce,factoryTime,truckID,orderType,acceptState,orderState,clientID,pickaccountID,produceNum,diaoduyuanID,tongjiyuanID)
SELECT a.demandID+'00',b.demandCountID,c.getstuffAddress,c.clientName,c.GetStuffNum,a.[生产厂家],CASE WHEN 水泥规格 LIKE'%散%' THEN 1

ELSE 出库数量 END,a.发货时间,水泥规格,销售单价,运费,业务员,提货账户,30,发货时间,发货时间,发货时间,备注,交货单号,验收数量,'3月导

入',driverID,出厂单价,发货时间,truckID,1,3,4,a.clientID,pickaccountID,出库数量,107,103 FROM temp3 a
INNER JOIN T_O_demandCount b ON a.demandID=b.demandID
INNER JOIN T_M_ClientInformation c ON a.clientID=c.clientID

--客户回款改变回款时间为唯一值
SELECT DATEADD(ss, id, a.[单据日期]) as paytime,a.* FROM temp2_get a
--客户回款  找到客户回款里面客户名与系统不相同的
SELECT a.* FROM temp2_get a WHERE a.[往来单位] not IN(
SELECT 往来单位 FROM temp2_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName
)
--更新客户回款 clientID
UPDATE a SET a.clientID=b.clientID FROM temp2_get a INNER JOIN T_M_ClientInformation b ON a.[往来单位]=b.clientName

--客户回款  更新type和memo
UPDATE temp2_get SET type=[收款方式],memo=[票号]

--找到系统里面没有的公司账户
SELECT 收款账户 FROM temp2_get WHERE 收款账户 NOT IN(
SELECT 收款账户 FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
)
--把没有的账户更改名字
UPDATE temp2_get SET 收款账户='热度南宁市区农村信用合作联社石埠信用社' WHERE 收款账户='热度南宁市区农村信用合作联社石埠信用'
--看下公司账户在目标表里面的名字
SELECT 收款账户 FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name

--扫描下公司账户和临时表里面的账户
SELECT 收款账户,a.companyaccID,b.name,b.companyAccountID FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name
--客户回款  更新companyaccountID到temp2_get
UPDATE a SET a.companyaccID=b.companyaccountID FROM temp2_get a INNER JOIN T_M_companyAccount b ON a.[收款账户]=b.name

--正式更新客户回款信息
INSERT INTO T_C_ClientPayment
(clientID,PaymentMoney,paymentdate,OperatorID,AddDate,companyAccountID,memo,type)
SELECT a.clientID,a.[总金额],CONVERT(varchar(100), DATEADD(ss, a.id, a.[单据日期]), 20),107,'2016-02-10 02:02:02',a.companyaccID,memo,type FROM temp2_get a

SQL语句.rar (2.7 KB, 下载次数: 1)

李维强-15级 · 发表于 2016-4-29 10:56:42

convert()第一个是varchar字符数，第二个是表里面的一个字段名，第3个是参数，代表不同的类型后面是示例
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513

李维强-15级 · 发表于 2016-4-1 04:03:12

like 的通配符有两种

%（百分号）：代表零个、一个或者多个字符。

_（下划线）：代表一个数字或者字符。

1. name以"李"开头

where name like '李%'

2. name中包含"云"，“云”可以在任何位置

where name like '%云%'

3. 第二个和第三个字符是0的值

where salary like '_00%'

4. 条件匹配以2开头，而且长度至少为3的值：

where salary like '2_%_%'

5. 以2结尾

where salary like '%2'

6. 第2个位置是2，以3结尾

where salary like '_2%3'

		自动登录	找回密码
密码			立即注册

[数据库] SQL语句汇总

浏览过的版块