求分月数据的技巧（T-SQL）

zhuyiwen

1. /*
   
2. 该生成指定年度的部门每月能耗数据收集情况
   
3. @DeviceType 为 0 时，表示所有装备
   
4. @DeptID 为 '' 或 为 'all'，表示所有部门
   
5. */
   
6. 
   
7. CREATE FUNCTION [dbo].[f部门能耗数据收集情况](@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 数据, 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 生成每月装备列表，然后与装备数据左联结。

zhuyiwen

通过自定义函数改进：

1. CREATE FUNCTION dbo.f装备月份能耗数据完成(@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装备年度各月能耗数据完成(@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装备月份能耗数据完成(D.装备序号, @YEAR, 1) AS [1],
    
25.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 2) AS [2],
    
26.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 3) AS [3],
    
27.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 4) AS [4],
    
28.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 5) AS [5],
    
29.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 6) AS [6],
    
30.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 7) AS [7],
    
31.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 8) AS [8],
    
32.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 9) AS [9],
    
33.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 10) AS [10],
    
34.                 dbo.f装备月份能耗数据完成(D.装备序号, @YEAR, 11) AS [11],
    
35.                 dbo.f装备月份能耗数据完成(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

复制代码