SSE項目小總結

前段時間給一電力公司做一套視窗服務人員星級評等管理系統，類似大家上銀行辦完事後用櫃檯上的按扭對服務人員進行服務評定！
  是朋友接的一項目，我去幫忙，主要是做報表這塊，說到痛點可能就是裡面的對這些資料進行匯總及報表了，由於表結構設計的不同，可能會對查詢帶來不利。在這將自己遇到的問題及解決的辦法記下！
  先看看部門表結構，如下：

在這裡用了一張表存放了各部門及之間的關係，大致分三類，第一類，也就是最大類：為全域！其ID為10
  次之為各市層級的分公司， 可以看到ID為 1010 至1016
  最後為各縣市的營業所，ID為六位 101010至101610，這一整張表存放了所有部門及說明了它們之間的關係，利用的是左右節點Lft,Rgt來歸類。

  再看職工表

可以看到其中有相應的職工ID及所屬部門ID（哪個營業所）

  再看職工業績周匯表
可以看到其中有使用者ID，及所屬部門ID以及服務數，較好，一般，差評，放棄，好評率，評價率，及第幾服務周，月，年等資料！

  涉及到的三張表結構就如此，現在來看下要求功能
  首先是要按營業所得到該營業所各職工的業務報表 UI如下：

點擊後

根據營業所得到報表還是比較容易的，直接通過基業所ID 如（101410）即可得到

  再一個要求按市層級分公司查，得到其下屬各營業所的業務報表 （營業所業績當然又是由職工創造的）

點擊後

這一步根據市層級公司的ID 如（1010）由表一可以知道其為崇陽供電公司，則再根據 1010 在 部門表中 尋找出 其lft rgt左右節點
  2 和 7 再查出左右節點在2和7 之間的部門可以得到  101010（崇陽城關營業所）  及  101011（崇陽天城營業所）然後再根據101010及101011這二個ID查出相應的報表。之後的過程就與功能一 按營業所查相似了！
  可以看到這一步就有點麻煩了～

  最後也即是按全域查尋各市層級分公司的業務報表（分公司又是由營業所組成，營業所業績又與職工業績有關！）

點擊後

 這一步根據 全域的 ID  10查詢，可以在周報表中看到，資料都是以職工儲存的，而職工又都是以營業所劃分，該表中沒有欄位表明其屬於哪家分公司，其營業所ID類似 101010 101011 101111……現在要根據全域ID為 10 得到各分公司的 報表。
  現在粗看要在該表中分組查詢，類似這樣就好，能以 1010**（1010開頭的營業所為崇陽供電公司），1011**（1011開頭的營業所為通山供電公司）類似這樣的部門ID資料的匯總，如下表：

我們要求 部門ID為 101310 和 101311 的資料匯總～前4位元字一樣～似乎有點難了，SQL沒有為我們直接提供這樣的查詢！

  想個辦法以另外一種方式查！
  其實已經有點規律性的東西： 全域ID 為 10  而二級部門 也即為 各市層級分公司的 ID 以 10 開頭 並且只有 4位元據～如1013。營業所ID為 對應的 分公司 1013開頭再+2位，即六位（如101310 和 101311）！現在要根據ID=10查詢出這些營業所來～並且按市公司分組～

主要SQL預存程序如下：

DECLARE @len int
SET @len=LEN(@ObjID)    --得到傳入ID的長度10  為 2
insert into @tblTemp SELECT  a.DepartmentID,LEFT(a.DepartmentID,4) as deID ,--將得到的營業所ID截取到4位長度 如101310和101311的結果為1013
SUM(a.BetterCount) as BetterCount ,SUM(a.GoodCount) as GoodCount,
        SUM(a.BadCount) as BadCount,SUM(a.AbortCount) as AbortCount
    FROM WeekTotal a 
    WHERE a.[Year]=@year 
    AND  LEFT(a.DepartmentID,@len)=@ObjID --營業所ID截取全域ID =10的長度，也即為截取到2位後的結果要與傳入的全域值10相等
    AND LEN(a.DepartmentID)=@len+4        --同時保證取到的為營業所的資料，所以ID長度為 6 （這樣排除了分公司ID為4 的）
    GROUP BY a.DepartmentID

將上面查詢出的資料儲存在暫存資料表中DECLARE @tblTemp TABLE(
ObjID bigint,
deID bigint,
BetterCount int,
GoodCount int,
BadCount int,
AbortCount int
)
insert into @tblTemp SELECT  a.DepartmentID,LEFT(a.DepartmentID,4) as deID ,SUM(a.BetterCount) as BetterCount ,SUM(a.GoodCount) as GoodCount,
        SUM(a.BadCount) as BadCount,SUM(a.AbortCount) as AbortCount
    FROM WeekTotal a 
    WHERE a.[Year]=@year 
    AND  LEFT(a.DepartmentID,@len)=@ObjID AND LEN(a.DepartmentID)=@len+4  
    GROUP BY a.DepartmentID

這樣可以得到類似下面的查詢

可以看到我們得到了按營業所匯總的資料，關鍵在於 部門ID 為101010 101011這樣開頭的記錄中 多了一個欄位為 depID並且值 均為相應部門ID的前四位！如 101010 101011 的depID均為 1010 好即是 崇陽供電公司！因為接下來要做的就是 根據欄位列depID 相同的 再進行匯總。select deID,RepeateObjIDCount=count(deID),BetterCount=sum(BetterCount),GoodCount=sum(GoodCount),
    BadCount=sum(BadCount) , AbortCount=sum(AbortCount)  ,
total=ISNULL(sum(BetterCount)+sum(GoodCount)+sum(BadCount)+sum(AbortCount),0) ,
ISNULL(CAST(CAST((sum(BetterCount)+sum(GoodCount)+sum(BadCount)) AS numeric)/(CASE sum(BetterCount)+sum(GoodCount)+sum(BadCount)+sum(AbortCount) WHEN 0 THEN 1 ELSE sum(BetterCount)+sum(GoodCount)+sum(BadCount)+sum(AbortCount) END) AS numeric(5,2)),0.00) AS Present,
departmentname from   @tblTemp a  join departments b on a.deID=b.departmentID
      group by deID,departmentname having count(*)>0 order by deID 

可以看到按 deID,departmentname （這裡的deID即為剛才的1010 ，1011……而departmentname即為分公司的名稱）進行分組即可得到要求！

需要說明的是類似下面的表

若要按部門名進行摘要資料可以如下查詢