SQL语句如下,有什么办法效率可以高一点?BillFlow表记录数在20万条以上,UserInfo表记录数在5万条左右Update tmpRep_Area Set AreaBilled=IsNull(a.AreaBilled,0),AreaNoBill=IsNull(c.AreaNoBill,0),AreaIsCardBilled=d.AreaIsCardBilled,AreaNoCardBilled=e.AreaNoCardBilled
From tmpRep_Area a,
(Select Bill.AreaCode,Count(Bill.AddrCode) As AreaBilled
From (Select b.AreaCode,a.AddrCode From BillFlow a
Left Join UserInfo b On b.AddrCode=a.AddrCode
Left Join AreaInfo c On c.AreaCode=b.AreaCode
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0 And b.UserStatus<>'封表' And b.UserStatus<>'注销'
And a.ID Not In(Select BillFlowID From BillFlowCancel Where Convert(Char(6),CancelDate,112)=@RepDate)
Group By b.AreaCode,a.AddrCode) Bill
Group By Bill.AreaCode) b,
(Select AreaCode,Count(AddrCode) As AreaNoBill From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CreatDate,112)<=@RepDate
And AddrCode
Not In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) c,
(Select AreaCode,Count(AddrCode) As AreaIsCardBilled From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CardCreatDate,112)<=@RepDate
And IsUseCard=1 And AddrCode
In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) d,
(Select AreaCode,Count(AddrCode) As AreaNoCardBilled From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CreatDate,112)<=@RepDate
And (IsUseCard=0 Or IsUseCard=1 And Convert(Char(6),CardCreatDate,112)>=@RepDate)
And AddrCode
In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) e
Where a.AreaCode=b.AreaCode And c.AreaCode=a.AreaCode And a.AreaCode=d.AreaCode And a.AreaCode=e.AreaCode
From tmpRep_Area a,
(Select Bill.AreaCode,Count(Bill.AddrCode) As AreaBilled
From (Select b.AreaCode,a.AddrCode From BillFlow a
Left Join UserInfo b On b.AddrCode=a.AddrCode
Left Join AreaInfo c On c.AreaCode=b.AreaCode
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0 And b.UserStatus<>'封表' And b.UserStatus<>'注销'
And a.ID Not In(Select BillFlowID From BillFlowCancel Where Convert(Char(6),CancelDate,112)=@RepDate)
Group By b.AreaCode,a.AddrCode) Bill
Group By Bill.AreaCode) b,
(Select AreaCode,Count(AddrCode) As AreaNoBill From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CreatDate,112)<=@RepDate
And AddrCode
Not In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) c,
(Select AreaCode,Count(AddrCode) As AreaIsCardBilled From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CardCreatDate,112)<=@RepDate
And IsUseCard=1 And AddrCode
In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) d,
(Select AreaCode,Count(AddrCode) As AreaNoCardBilled From UserInfo
Where UserStatus<>'封表' And UserStatus<>'注销'
And Convert(Char(6),CreatDate,112)<=@RepDate
And (IsUseCard=0 Or IsUseCard=1 And Convert(Char(6),CardCreatDate,112)>=@RepDate)
And AddrCode
In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
Group By AreaCode) e
Where a.AreaCode=b.AreaCode And c.AreaCode=a.AreaCode And a.AreaCode=d.AreaCode And a.AreaCode=e.AreaCode
解决方案 »
- 求助!!!SQL如何根据一个表中字典项查询另一个表中的字段,并修改相应的数据
- sql server里写个函数遇到几个问题
- 每天1萜
- sql问题
- 怎样用SQL语句让SQL每天完全备份?
- sql2000在高级服务器上无法安装,每次到最后安装我配置的服务的时候就出错,在线等
- 一个不大的新软件,我想把已有的数据到入新软件中,但不知道扩展名为CDS和IUP的文件是什么文件?
- Transaction cannot start while in firehose 问题
- 请大家指点一下,我这个统计要求还有其他方法统计得更快吗?
- SQL SERVER 2000/2005/2008/2012各版本存储过程有什么区别?
- 求个SQL语句啊。。大侠们。。。
- 【求助】数据库里某个字段被恶意修改
另外还有一个SQL语句,这两天写SQL语句写的我头晕眼花,还望各位给予帮助CREATE PROCEDURE Rep_AddhsCount
@RepDate Char(6)
ASTRUNCATE TABLE tmpRep_AreaInsert Into tmpRep_Area(RepDate,AreaCode,AreaStationID,AreaStationName,AreaCount,AreaDisable,AreaWriteOff,AreaNoDwell,BillerCode,BillerName,CreatCount,AreaIsCard,AreaNoCard)
SELECT @RepDate,a.AreaCode,
a.AreaStationID,
d.StationName As AreaStationName,
COUNT(b.AddrCode) AS AreaCount,
IsNull(e.AreaDisable,0) As AreaDisable, --封表户
IsNull(f.AreaWriteOff,0) As AreaWriteOff, --注销户
IsNull(g.AreaNoDwell,0) As AreaNoDwell, --无人户
a.AreaBiller,
c.BillerName,
IsNull(h.CreatCount,0) As CreatCount,
IsNull(i.AreaIsCard,0) As AreaIsCard,
IsNull(j.AreaNoCard,0) As AreaNoCard
FROM AreaInfo a
Left Outer Join UserInfo b On b.AreaCode=a.AreaCode
Left Outer Join BillUser c On c.BillerCode=a.AreaBiller
Left Outer Join StationInfo d ON a.AreaStationID = d.StationID
Left Outer Join (Select AreaCode,Count(addrcode) As AreaDisable From UserInfo Where UserStatus='封表' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) e On e.AreaCode=a.AreaCode --封表户
Left Outer Join (Select AreaCode,Count(addrcode) As AreaWriteOff From UserInfo Where UserStatus='注销' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) f On f.AreaCode=a.AreaCode --注销户
Left Outer Join (Select AreaCode,Count(addrcode) As AreaNoDwell From UserInfo Where UserStatus='无人' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) g On f.AreaCode=a.AreaCode --无人户
Left Outer Join (Select AreaCode,Count(addrcode) As CreatCount From UserInfo Where Convert(Char(6),CreatDate,112)=@RepDate Group By AreaCode) h On h.AreaCode=a.AreaCode --新建户数
Left Outer Join (Select AreaCode,Count(addrcode) As AreaIsCard From UserInfo Where Convert(Char(6),CardCreatDate,112)<=@RepDate And IsUseCard=1 And UserStatus<>'封表' And UserStatus<>'注销' Group By AreaCode) i On i.AreaCode=a.AreaCode --用卡户数,不包括封表户和注销户
Left Outer Join (Select AreaCode,Count(addrcode) As AreaNoCard From UserInfo Where Convert(char(6),CreatDate,112)<=@RepDate And IsUseCard=0 And UserStatus<>'封表' And UserStatus<>'注销' Group By AreaCode) j On j.AreaCode=a.AreaCode --无卡户数,不包括封表户和注销户
Where Convert(char(6),b.CreatDate,112)<=@RepDate
Group By a.AreaCode,a.AreaStationID,d.StationName,e.AreaDisable,f.AreaWriteOff,g.AreaNoDwell,a.AreaBiller,c.BillerName,h.CreatCount,i.AreaIsCard,j.AreaNoCard
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))
只写在一条T-SQL里面,关联太多,本身效率就降低了~
你还是试试把重复的语句用临时表代替吧,这样可能会减少几次查询时间!