資源描述:
《簡單分析sql統(tǒng)計查詢》由會員上傳分享,免費在線閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、示例表關(guān)系如下[v_MatchInReward]視圖如卜CTEATEVIEW[dbo].[v_MatchInReward]ASSELECTdbo.Matchinfo.DemandID,dbo.Matchinfo.DemandUserID,dbo.Matchinfo.MatchType,dbo.Matchinfo.IsReadByDemandProvider,dbo?Matchinfo.Status,dbo.MAtchlnfo.OpertorlD,dbo.Matchinfo.IsAudited,dbo.Matchinfo.IsDeleted,dbo?Reward?BountyAmoun
2、tFROMdbo.MatchinfoINNERJOINdbo.RewardONdbo.MatchInfo.DemandID=dbo.Reward.RewardlD要求按照Matchinfo.DemandUserID分紐.統(tǒng)計Status=4的次數(shù)IsReadByDemandProvider=lfKj^&BountyAmount金額的總數(shù)以及在MatchlnfolH現(xiàn)的數(shù)據(jù)條數(shù)setstatisticstimeoffsetstatistiesioon/版本一錯誤用法,先使用casewhen再使用count,就相當于先根據(jù)casewhen分組,然后才統(tǒng)計,不是真正的按條件/SELECTD
3、emandUserID,IsNull(COUNT(1),0)asInRewardCount,IsNull(SUM(BountyAmount),0)ASBountyAmountCount,IsNull(CASEIsReadByDemandProviderWHEN0THENCOUNT(1)END,0)ASNotSeeCount.IsNull(CASE[Status]WHEN4THENCOUNT(1)END,0)ASCandidateCountFROMv_MatchInRewardWHEREDemandUserID=1ANDMatchType=3GROUPBYDemandUserID,Is
4、ReadByDemandProvider,[Status]/版本二累贅用法,使用主查詢分組,多個子查詢來統(tǒng)計,造成多次表掃描/SELECTm.DemandUserIDfIsNull(COUNT(1),0)asInRewardCountzIsNull((selectSUM(BountyAmount)fromRewardinnerjoinmatchinfoonRewardID=matchinfo.DemandIDwherematchinfo.DemandUserID=m?DemendUserlD),0)ASBountyAmountCountzIsNull((selectcount(1)f
5、rommatchinfowhereIsReadByDemandProvider=0anddemandUserId=m?DemandUserlD)z0)ASNotSeeCount,IsNull((selectcount(l)frommatchinfowhere[Status]=4anddemandUserId=m?DemandUserlD)z0)ASCandidateCountFROMmatchinfoasmWHEREm.DemandUserID=1ANDm.MatchType=3GROUPBYm?DemandUserlD版本三止確用法,在count函數(shù)屮使用casewhen,不會導(dǎo)致
6、多層級分組,得到的是真實的數(shù)據(jù)缺陷是,count雖然沒有統(tǒng)計ssewhen得到的rw□值(不管設(shè)定沒設(shè)定),但是消息框中還是有整告,盡管沒有很實際的數(shù)據(jù)說明這種情況的不妥,但還是要避免SELECTm?DemandUserlD,IsNull(COUNT(1),0)asInRewardCount,IsNull(SUM(BountyAmount)z0)ASBountyAmountCount,IsNull(count(caseIsReadByDemandProviderwhen0then1end),0)ASNotSeeCount,IsNull(count(case[Status]when4t
7、hen1end),0)ASCandidateCountFROMmetchinfoasminnerjoinRewardasronr.RewardID=m.DemAndlDWHEREm.DemandUserID=1ANDm.MatchType=3GROUPBYm.DemandUserlD/版本四優(yōu)化-,結(jié)合視圖在sum函數(shù)屮使用casewhen和count的原理是一樣的,不會導(dǎo)致多層級分紐.,得到的是真實的數(shù)據(jù),而且sum函數(shù)也很好的避免了rm丄丄值統(tǒng)計時的警