自己常用sql维护校检数据脚本集合

作者: 去皮哥哥 分类: sql维护 发布时间: 2019-10-28 15:41

——-根据A流水表,更新B结果表字段逻辑

select AgentID,ProID,SUM(Count) as count into #temp from whmp_web_stock_records
where IsVirtual =1 and GID=2
group by AgentID ,ProID

update whmp_web_stock set Count=#temp.count from #temp where whmp_web_stock.AgentID = #temp.AgentID
and whmp_web_stock.ProID=#temp.ProID and whmp_web_stock.IsVirtual=1

select * from #temp
drop table #temp

 

——联三张表更新某个字段。

select ID,Name,MoneyBalance into #temp from whmp_web_users

select AgentsID,sum(AmountOfChange) as sum into #temp2 from whmp_sys_BalanceDetails
group by AgentsID

select a.ID,a.UserID,UserName,b.MoneyBalance into #temp3 from whmp_web_agents as a,#temp as b where a.UserID = b.ID

select * into #temp4 from #temp3 as c,#temp2 as d where c.ID=d.AgentsID

select * from #temp2
select * from #temp3
select * from #temp
select * from #temp4
drop table #temp
drop table #temp2
drop table #temp3
drop table #temp4

–update whmp_web_users set MoneyBalance = #temp4.sum from #temp4 where whmp_web_users.ID = #temp4.UserID

——查询某个表中,某个字段重复的有哪些数据

select ordercode from whmp_web_order group by ordercode having count(ordercode )>1

 

—–根据返利流水表,更新用户收入

select * from whmp_web_users where id in (select UserID from whmp_web_agents where id in (select AgentID from whmp_web_rebate_records where RebateType=0))

select AgentID,AgentName,Money into #temp from whmp_web_rebate_records where RebateType=0
select sum(Money) as money,AgentID,AgentName into #money from #temp group by AgentID,AgentName
–drop table #temp
select * from #temp
select UserID,UserName,#money.AgentID,#money.AgentName,#money.money into #ua from whmp_web_agents as ag,#money where ag.ID=#money.AgentID
–drop table #ua
select * from #ua
–drop table #money
select * from #money

update whmp_web_users set InCome = InCome-#ua.money from #ua where id = #ua.UserID

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注