重工电子论坛

?找回密码
?立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 活动 交友 discuz
12
返回列表 发新帖
楼主: 李维强-15级
打印 上一主题 下一主题

[数据库] SQL语句汇总

[复制链接]

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
11#
?楼主| 发表于 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)








236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
12#
?楼主| 发表于 2017-3-7 10:50:57 | 只看该作者
本帖最后由 李维强-15级 于 2017-3-7 12:59 编辑

[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

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
13#
?楼主| 发表于 2017-3-7 21:39:26 | 只看该作者
[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

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
14#
?楼主| 发表于 2018-3-27 10:34:50 | 只看该作者
分页查询
[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

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
15#
?楼主| 发表于 2018-6-7 04:53:57 | 只看该作者

环球

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

差关键词

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
16#
?楼主| 发表于 2018-6-7 23:15:18 | 只看该作者
本帖最后由 李维强-15级 于 2018-6-8 00:35 编辑

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

以下为最终版,关键是 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,'')),'','') 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,'')),'','') 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,'')),'','') 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,'')),'','') 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,'')),'','') 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,'')),'','') 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




236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
17#
?楼主| 发表于 2019-5-4 17:05:31 | 只看该作者
这个例子其实是非常的简单了就是要实现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可以实现如果已经存在对应键的记录时,替换该记录的其他字段。

236

主题

580

帖子

3479

积分

学生管理组

Rank: 8Rank: 8

积分
3479
18#
?楼主| 发表于 2019-8-24 00:46:16 | 只看该作者
本帖最后由 李维强-15级 于 2019-8-24 00:58 编辑

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

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

[ WITH [ ,...n ] ]
::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
参数说明:
expression_name:
公用表表达式的有效标识符。 expression_name 必须与在同一 WITH 子句中定义的任何其他公用表表达式的名称不同,但 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

12
返回列表 发新帖
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|cqutlab ( 渝ICP备15004556号 )?

GMT+8, 2019-10-23 11:57 , Processed in 0.121509 second(s), 29 queries .

Powered by Discuz! X3.1

? 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表