• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

SELECT INTO结合UNION ALL 的一个业务实例分析

SQLServer eason 2290次浏览 5个评论 扫描二维码

客户需求:
统计经销商代码(DealerCode)为XAD和FZA的两家经销商在2015-9-27到2015-10-3这段时间的销量。

   --创建测试表:
   CREATE TABLE [dbo].[DealerSaleDetails](
   [DealerCode] [nvarchar](255) NULL,
   [Brand] [nvarchar](255) NULL,
   [Date] [nvarchar](255) NULL,
   [1] [float] NULL,
   [2] [float] NULL,
   [3] [float] NULL,
   [4] [float] NULL,
   [5] [float] NULL,
   [6] [float] NULL,
   [7] [float] NULL,
   [8] [float] NULL,
   [9] [float] NULL,
   [10] [float] NULL,
   [11] [float] NULL,
   [12] [float] NULL,
   [13] [float] NULL,
   [14] [float] NULL,
   [15] [float] NULL,
   [16] [float] NULL,
   [17] [float] NULL,
   [18] [float] NULL,
   [19] [float] NULL,
   [20] [float] NULL,
   [21] [float] NULL,
   [22] [float] NULL,
   [23] [float] NULL,
   [24] [float] NULL,
   [25] [float] NULL,
   [26] [float] NULL,
   [27] [float] NULL,
   [28] [float] NULL,
   [29] [float] NULL,
   [30] [float] NULL,
   [31] [float] NULL
   )

   --插入测试数据
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'QDA', N'Jaguar', N'2015-10', 0, 3, 1, 4, 3, 2, 4, 1, 6, 4, 5, 7,
   2, 2, 8, 0, 0, 2, 4, 6, 2, 4, 3, 3, 2, 2, 3, 8, 5, 4, 4);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'QDA', N'LandRover', N'2015-10', 10, 8, 9, 9, 12, 17, 4, 20, 26,
   14, 20, 20, 15, 21, 15, 24, 12, 11, 10, 20, 18, 27, 24, 17, 17, 13, 16, 15, 17, 10, 25);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'FZA', N'Jaguar', N'2015-10', 0, 3, 1, 0, 1, 4, 2, 3, 3, 0, 5, 6,
   1, 2, 3, 4, 1, 3, 4, 4, 0, 8, 4, 3, 3, 5, 5, 3, 3, 4, 3);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'FZA', N'LandRover', N'2015-10', 2, 4, 7, 3, 8, 11, 9, 16, 12,
   22, 13, 24, 18, 23, 14, 27, 17, 11, 17, 21, 18, 15, 16, 21, 10, 14, 16, 20, 16, 18, 10);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'XAD', N'Jaguar', N'2015-10', 1, 3, 3, 2, 1, 2, 2, 2, 1, 2, 2, 2,
   4, 3, 2, 5, 4, 2, 2, 3, 4, 1, 5, 1, 2, 2, 3, 0, 3, 3, 1);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'XAD', N'LandRover', N'2015-10', 20, 21, 19, 15, 12, 18, 24, 25,
   28, 32, 29, 31, 25, 24, 32, 30, 21, 22, 22, 28, 18, 25, 21, 22, 22, 21, 26, 23, 30, 25, 28);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'XAD', N'LandRover', N'2015-9', 27, 34, 25, 30, 26, 23, 30, 24,
   22, 25, 27, 27, 23, 28, 16, 27, 36, 16, 26, 36, 24, 22, 27, 26, 31, 22, 18, 27, 21, 23, 0);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'XAD', N'Jaguar', N'2015-9', 2, 5, 2, 5, 2, 4, 1, 1, 3, 5, 4, 3,
   2, 5, 3, 7, 2, 5, 3, 0, 4, 5, 7, 5, 2, 5, 2, 3, 2, 3, 0);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'FZA', N'LandRover', N'2015-9', 19, 21, 9, 5, 8, 25, 21, 23, 17,
   16, 18, 20, 15, 17, 15, 15, 14, 20, 25, 20, 18, 22, 15, 24, 23, 10, 6, 23, 0, 23, 0);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'FZA', N'Jaguar', N'2015-9', 2, 4, 1, 0, 0, 6, 5, 5, 4, 1, 6, 5,
   3, 4, 5, 2, 4, 6, 4, 6, 5, 3, 2, 7, 4, 0, 0, 6, 0, 7, 0);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'QDA', N'LandRover', N'2015-9', 18, 20, 14, 15, 10, 15, 23, 20,
   24, 18, 19, 14, 16, 19, 22, 12, 16, 11, 19, 9, 12, 9, 16, 12, 12, 9, 6, 25, 34, 22, 0);
   INSERT INTO [dbo].[DealerSaleDetails] VALUES (N'QDA', N'Jaguar', N'2015-9', 5, 4, 5, 2, 4, 3, 8, 4, 5, 6, 5, 2,
   2, 10, 11, 0, 1, 4, 2, 2, 6, 2, 5, 5, 4, 5, 1, 6, 1, 8, 0);

数据预览:

SELECT INTO结合UNION ALL 的一个业务实例分析

需求分析:

1. 原表中每个经销商每个品牌每个月只有一条记录,而且每条记录只有本月的数据,而客户需求中有跨月取数,所以必须先将跨月区间每天的数据取出来。

        select [DealerCode],[Brand],[27],[28],[29],[30],NULL AS [1],NULL AS [2],NULL AS [3]
        from DealerSaleDetails 
        where [Date] ='2015-9' and [DealerCode] in('XAD','FZA')
        union all
        select [DealerCode],[Brand],NULL AS [27],NULL AS [28],NULL AS [29],NULL AS [30],[1],[2],[3]
        from DealerSaleDetails 
        where [Date] ='2015-10' and [DealerCode] in('XAD','FZA')

结果如下:

SELECT INTO结合UNION ALL 的一个业务实例分析

2. 按经销商(DealerCode)和品牌(Brand)汇总并存入临时表#tmp

   select t.* INTO #tmp from
(       select a.[DealerCode],a.[Brand],
                        sum(a.[27]) AS [27],
                        sum(a.[28]) AS [28],
                        sum(a.[29]) AS [29],
                        sum(a.[30]) AS [30],
                        sum(a.[1]) AS [1],
                        sum(a.[2]) AS [2], 
                        sum(a.[3]) AS [3]
        from
        (
         select [DealerCode],[Brand],[27],[28],[29],[30],NULL AS [1],NULL AS [2],NULL AS [3]
         from DealerSaleDetails 
         where [Date] ='2015-9' and [DealerCode] in('XAD','FZA')
         union all
         select [DealerCode],[Brand],NULL AS [27],NULL AS [28],NULL AS [29],NULL AS [30],[1],[2],[3]
         from DealerSaleDetails 
         where [Date] ='2015-10' and [DealerCode] in('XAD','FZA')
        )a 
        group by [DealerCode],[Brand] order by [DealerCode]
)t

结果如下:

SELECT INTO结合UNION ALL 的一个业务实例分析

3. 对临时表#tmp按区间汇总

  select b.[DealerCode],b.[Brand],sum(b.[Total]) AS [Total] from
(
        select [DealerCode],[Brand],[27] AS Total from #tmp
        union ALL
        select [DealerCode],[Brand],[28] AS Total from #tmp
        union ALL
        select [DealerCode],[Brand],[29] AS Total from #tmp
        union ALL
        select [DealerCode],[Brand],[30] AS Total from #tmp     
        union ALL
        select [DealerCode],[Brand],[1] AS Total from #tmp      
        union ALL
        select [DealerCode],[Brand],[2] AS Total from #tmp
        union ALL
        select [DealerCode],[Brand],[3] AS Total from #tmp
) b group by [DealerCode],[Brand] order by [DealerCode]

得到最后结果:

SELECT INTO结合UNION ALL 的一个业务实例分析


金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明SELECT INTO结合UNION ALL 的一个业务实例分析
喜欢 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(5)个小伙伴在吐槽
  1. Wow, great blog post.Thanks Again. Want more.
    ottoman coffee table2016-02-10 16:50 Reply Windows XP | Firefox 3.5.3
  2. Fantastic blog post.Really thank you!
    Wyposazenie gabinetu kosmetycznego2016-02-10 15:31 Reply Windows 7 | Chrome 4.0.221.7
  3. Say, you got a nice blog post.Really thank you!
    24 passenger bus rental2016-02-07 20:24 Reply Windows XP | Internet Explorer 7.0
  4. I cannot thank you enough for the blog post.Really thank you! Really Great.
    eebest82016-02-02 04:24 Reply Windows 7 | Chrome 4.0.221.7
    • you are welcome*^_^*
      wangyisheng2016-02-02 09:18 Reply Android 4.4.4 | Chrome 33.0.0.0