求分月數(shù)據(jù)的技巧（T-SQL）

zhuyiwen

1. /*
   
2. 該生成指定年度的部門每月能耗數(shù)據(jù)收集情況
   
3. @DeviceType 為 0 時，表示所有裝備
   
4. @DeptID 為 '' 或 為 'all'，表示所有部門
   
5. */
   
6. 
   
7. CREATE FUNCTION [dbo].[f部門能耗數(shù)據(jù)收集情況](@YEAR int, @DeptID varchar(20), @DeviceType int)
   
8. RETURNS TABLE
   
9. AS
   
10. RETURN (
    
11.         SELECT TOP 100 PERCENT V.年度, V.月份, V.裝備類型編號, V.裝備類型,
    
12.                 V.裝備序號, V.名稱,
    
13.                 V.部門標識, V.部門,
    
14.                 CASE WHEN R.裝備序號 IS NULL THEN 0 ELSE 1 END AS 數(shù)據(jù), R.已完成
    
15.         FROM (
    
16.                 SELECT O.裝備序號, O.裝備類型編號, O.部門, O.部門標識, O.名稱, O.裝備類型, @YEAR AS 年度, M.月份
    
17.                 FROM (
    
18.                         SELECT D.裝備序號, D.裝備類型編號, P.部門, P.部門標識, L.名稱, T.裝備類型
    
19.                         FROM dbo.t裝備 AS D INNER JOIN
    
20.                                 dbo.v裝備列表 AS L ON D.裝備序號 = L.裝備序號 INNER JOIN
    
21.                                 dbo.t部門 AS P ON L.一級部門標識 = P.部門標識 INNER JOIN
    
22.                                 dbo.t裝備類型 AS T ON D.裝備類型編號 = T.裝備類型編號
    
23.                         WHERE (D.裝備類型編號=@DeviceType OR (@DeviceType=0 AND D.裝備類型編號 IN (1, 2, 3, 4)))
    
24.                                 AND (一級部門標識=@DeptID OR @DeptID='' OR UPPER(@DeptID)='ALL')
    
25.                                 AND (D.審核 = 1) AND (D.報廢 <> 1) AND (D.閑置 <> 1)) AS O CROSS JOIN
    
26.                         (SELECT 1 AS 月份 UNION
    
27.                          SELECT 2 AS 月份 UNION
    
28.                          SELECT 3 AS 月份 UNION
    
29.                          SELECT 4 AS 月份 UNION
    
30.                          SELECT 5 AS 月份 UNION
    
31.                          SELECT 6 AS 月份 UNION
    
32.                          SELECT 7 AS 月份 UNION
    
33.                          SELECT 8 AS 月份 UNION
    
34.                          SELECT 9 AS 月份 UNION
    
35.                          SELECT 10 AS 月份 UNION
    
36.                          SELECT 11 AS 月份 UNION
    
37.                          SELECT 12 AS 月份) AS M
    
38.                 ) AS V LEFT OUTER JOIN (
    
39.                 SELECT 裝備序號, 年度, 月份, 已完成 FROM dbo.t能耗辦公樓 WHERE (年度 = @YEAR) UNION
    
40.                 SELECT 裝備序號, 年度, 月份, 已完成 FROM dbo.t能耗囤船 WHERE (年度 = @YEAR) UNION
    
41.                 SELECT 裝備序號, 年度, 月份, 已完成 FROM dbo.t能耗海巡艇 WHERE (年度 = @YEAR) UNION
    
42.                 SELECT 裝備序號, 年度, 月份, 已完成 FROM dbo.t能耗車輛 WHERE (年度 = @YEAR)
    
43.                 ) AS R
    
44.                 ON V.裝備序號 = R.裝備序號 AND V.年度 = R.年度 AND V.月份 = R.月份
    
45.         ORDER BY V.裝備類型編號, V.裝備序號, V.月份
    
46. )

復制代碼

1. -- 用常量生成月份派生表
   
2. SELECT 1 AS 月份 UNION
   
3. SELECT 2 AS 月份 UNION
   
4. SELECT 3 AS 月份 UNION
   
5. SELECT 4 AS 月份 UNION
   
6. SELECT 5 AS 月份 UNION
   
7. SELECT 6 AS 月份 UNION
   
8. SELECT 7 AS 月份 UNION
   
9. SELECT 8 AS 月份 UNION
   
10. SELECT 9 AS 月份 UNION
    
11. SELECT 10 AS 月份 UNION
    
12. SELECT 11 AS 月份 UNION
    
13. SELECT 12 AS 月份

復制代碼

CROSS JOIN 生成每月裝備列表，然后與裝備數(shù)據(jù)左聯(lián)結(jié)。

zhuyiwen

通過自定義函數(shù)改進：

1. CREATE FUNCTION dbo.f裝備月份能耗數(shù)據(jù)完成(@DeviceID int, @YEAR int, @MONTH int)
   
2. RETURNS NVARCHAR(3) AS
   
3. BEGIN
   
4.         DECLARE @RET NVARCHAR(3)
   
5.         DECLARE @R bit
   
6.         SELECT TOP 1 @R=已完成
   
7.         FROM (
   
8.                 SELECT 已完成 FROM dbo.t能耗辦公樓 WHERE (裝備序號=@DeviceID AND 年度 = @YEAR AND 月份=@MONTH) UNION
   
9.                 SELECT 已完成 FROM dbo.t能耗囤船 WHERE (裝備序號=@DeviceID AND 年度 = @YEAR AND 月份=@MONTH) UNION
   
10.                 SELECT 已完成 FROM dbo.t能耗海巡艇 WHERE (裝備序號=@DeviceID AND 年度 = @YEAR AND 月份=@MONTH) UNION
    
11.                 SELECT 已完成 FROM dbo.t能耗車輛 WHERE (裝備序號=@DeviceID AND 年度 = @YEAR AND 月份=@MONTH)
    
12.                 ) AS E
    
13.        
    
14.         SET @RET= CASE WHEN @R=1 THEN N'已完成' WHEN @R=0 THEN N'未完成' ELSE N'未填報' END
    
15.         RETURN @RET
    
16. END
    
17. GO
    
18. 
    
19. CREATE FUNCTION dbo.f裝備年度各月能耗數(shù)據(jù)完成(@YEAR int, @DeptID varchar(20), @DeviceType int)
    
20. RETURNS TABLE AS
    
21. RETURN
    
22. (
    
23.         SELECT D.裝備序號, D.裝備類型編號, P.部門, P.部門標識, L.名稱, T.裝備類型, @YEAR AS 年度,
    
24.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 1) AS [1],
    
25.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 2) AS [2],
    
26.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 3) AS [3],
    
27.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 4) AS [4],
    
28.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 5) AS [5],
    
29.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 6) AS [6],
    
30.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 7) AS [7],
    
31.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 8) AS [8],
    
32.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 9) AS [9],
    
33.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 10) AS [10],
    
34.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 11) AS [11],
    
35.                 dbo.f裝備月份能耗數(shù)據(jù)完成(D.裝備序號, @YEAR, 12) AS [12]
    
36.         FROM dbo.t裝備 AS D INNER JOIN
    
37.                 dbo.v裝備列表 AS L ON D.裝備序號 = L.裝備序號 INNER JOIN
    
38.                 dbo.t部門 AS P ON L.一級部門標識 = P.部門標識 INNER JOIN
    
39.                 dbo.t裝備類型 AS T ON D.裝備類型編號 = T.裝備類型編號
    
40.         WHERE (D.裝備類型編號=@DeviceType OR (@DeviceType=0 AND D.裝備類型編號 IN (1, 2, 3, 4)))
    
41.                 AND (一級部門標識=@DeptID OR @DeptID='' OR UPPER(@DeptID)='ALL')
    
42.                 AND (D.審核 = 1) AND (D.報廢 <> 1) AND (D.閑置 <> 1)
    
43. )
    
44. GO

復制代碼