SQLSERVER 使用 ROLLUP 汇总数据，实现分组统计，合计，小计

表结构：

/****** Object:  Table [dbo].[AnnualSalesSummary]    Script Date: 2017-1-5 11:07:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AnnualSalesSummary](
    [assId] [nvarchar](38) NOT NULL,
    [SalesPeriod] [nvarchar](100) NULL,
    [CounterName] [nvarchar](50) NULL,
    [Season] [nvarchar](50) NULL,
    [SalesVolume] [int] NULL,
    [Turnover] [int] NULL,
    [NoneOtherAmount] [int] NULL,
    [MaoriAmount] [int] NULL,
    [TurnoverRatio] [decimal](18, 1) NULL,
    [NoneOtherRate] [decimal](18, 1) NULL,
    [MaoriRate] [decimal](18, 1) NULL,
 CONSTRAINT [PK_AnnualSalesSummary] PRIMARY KEY CLUSTERED 
(
    [assId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AnnualSalesSummary] ADD  CONSTRAINT [DF_AnnualSalesSummary_assId]  DEFAULT (newid()) FOR [assId]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'assId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'销售期间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'SalesPeriod'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'专柜' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'CounterName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'季节' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'Season'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'销售量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'SalesVolume'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'成交金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'Turnover'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'让利金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'NoneOtherAmount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'毛利金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'MaoriAmount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'成交金额占比' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'TurnoverRatio'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'让利率' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'NoneOtherRate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'毛利率' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnnualSalesSummary', @level2type=N'COLUMN',@level2name=N'MaoriRate'
GO

 

测试数据：

insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('10A5F365-A5E5-4046-A10D-9B282948FD7D', '2014-03/2015-02', '1.2男休闲', '[C]秋', 22310, 1336453, 189831, 189831, 3.3, 6.2, 20.8)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('1641D492-A90F-4BD2-BA2B-66B599DFB4B0', '2014-03/2015-02', '1.2男休闲', '[B]夏', 108108, 7181409, 1663791, 1663791, 17.6, 9.4, 19.3)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('22D91E10-42A0-49EF-9BE8-86CDF8E3DC04', '2014-03/2015-02', '1.2男休闲', '[E]四季=春夏', 1, 5, 0, 0, 0, 0, 6.5)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('481B8ACB-385C-4D2E-84A8-94C824CC7077', '2014-03/2015-02', '男正装', '秋', 44620, 2672905, 379662, 379662, 6.6, 12.4, 41.7)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('4EF5E514-7EB6-48B8-A38E-385096753A67', '2014-03/2015-02', '1.2男休闲', '[H]冬2', 72180, 8014356, 2543257, 2543257, 19.6, 12, 17.9)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('554D249D-7347-42F4-BF75-E0423584CABB', '2014-03/2015-02', '男正装', '春', 40189, 3768598, 686935, 686935, 9.2, 15.4, 38.7)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('5A85A143-6D94-4C8D-A8A3-29742693EADF', '2014-03/2015-02', '男正装', '冬', 68, 3835, 88, 88, 0, 2.2, -48.3)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('952BAA0D-CAA2-4E4C-AB6B-593DF939309A', '2014-03/2015-02', '男正装', '夏', 216216, 14362818, 3327581, 3327581, 35.2, 18.8, 38.6)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('95CDEFF7-AE8B-47D5-AE53-ACC1B47B2090', '2014-03/2015-02', '男正装', '冬2', 144360, 16028712, 5086514, 5086514, 39.3, 24.1, 37.8)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('A3500304-19EB-461E-8C05-9FBD9E3CCA68', '2014-03/2015-02', '1.2男休闲', '[A]春', 20095, 1884299, 343468, 343468, 4.6, 7.7, 19.3)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('DA9F269A-2439-4F1D-8995-2DD603FEF30D', '2014-03/2015-02', '1.2男休闲', '[D]冬', 34, 1918, 44, 44, 0, 1.1, -24.1)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('E2F5BC6B-5B84-4F80-AADB-317F83AD056E', '2014-03/2015-02', '男正装', '冬1', 48125, 3935104, 361488, 361488, 9.7, 8.4, 37.8)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('ED2E55D0-B169-4F65-B89F-CC5E7F85169D', '2014-03/2015-02', '1.2男休闲', '[G]冬1', 24063, 1967552, 180744, 180744, 4.8, 4.2, 18.9)
insert into AnnualSalesSummary(assId, SalesPeriod, CounterName, Season, SalesVolume, Turnover, NoneOtherAmount, MaoriAmount, TurnoverRatio, NoneOtherRate, MaoriRate) values ('FB058C51-C21C-4AF5-AD80-3B8757CA50E7', '2014-03/2015-02', '男正装', '四季=春夏', 2, 10, 0, 0, 0, 0, 13)

原数据结构：

10A5F365-A5E5-4046-A10D-9B282948FD7D 2014-03/2015-02 1.2男休闲 [C]秋 22310 1336453 189831 189831 3.3 6.2 20.8
1641D492-A90F-4BD2-BA2B-66B599DFB4B0 2014-03/2015-02 1.2男休闲 [B]夏 108108 7181409 1663791 1663791 17.6 9.4 19.3
22D91E10-42A0-49EF-9BE8-86CDF8E3DC04 2014-03/2015-02 1.2男休闲 [E]四季=春夏 1 5 0 0 0.0 0.0 6.5
481B8ACB-385C-4D2E-84A8-94C824CC7077 2014-03/2015-02 男正装 秋 44620 2672905 379662 379662 6.6 12.4 41.7
4EF5E514-7EB6-48B8-A38E-385096753A67 2014-03/2015-02 1.2男休闲 [H]冬2 72180 8014356 2543257 2543257 19.6 12.0 17.9
554D249D-7347-42F4-BF75-E0423584CABB 2014-03/2015-02 男正装 春 40189 3768598 686935 686935 9.2 15.4 38.7
5A85A143-6D94-4C8D-A8A3-29742693EADF 2014-03/2015-02 男正装 冬 68 3835 88 88 0.0 2.2 -48.3
952BAA0D-CAA2-4E4C-AB6B-593DF939309A 2014-03/2015-02 男正装 夏 216216 14362818 3327581 3327581 35.2 18.8 38.6
95CDEFF7-AE8B-47D5-AE53-ACC1B47B2090 2014-03/2015-02 男正装 冬2 144360 16028712 5086514 5086514 39.3 24.1 37.8
A3500304-19EB-461E-8C05-9FBD9E3CCA68 2014-03/2015-02 1.2男休闲 [A]春 20095 1884299 343468 343468 4.6 7.7 19.3
DA9F269A-2439-4F1D-8995-2DD603FEF30D 2014-03/2015-02 1.2男休闲 [D]冬 34 1918 44 44 0.0 1.1 -24.1
E2F5BC6B-5B84-4F80-AADB-317F83AD056E 2014-03/2015-02 男正装 冬1 48125 3935104 361488 361488 9.7 8.4 37.8
ED2E55D0-B169-4F65-B89F-CC5E7F85169D 2014-03/2015-02 1.2男休闲 [G]冬1 24063 1967552 180744 180744 4.8 4.2 18.9
FB058C51-C21C-4AF5-AD80-3B8757CA50E7 2014-03/2015-02 男正装 四季=春夏 2 10 0 0 0.0 0.0 13.0
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

 

分组小计、合计后的结果：

实现SQL语句：

 SELECT  CASE WHEN GROUPING(SalesPeriod) =1 THEN '合计' ELSE SalesPeriod  END AS 销售期间 /*合计所放在哪个字段上*/
       ,CASE WHEN GROUPING(CounterName)=0 AND GROUPING(Season)=1 THEN '小计' ELSE CounterName END AS 专柜 /*小计所放在哪个字段上*/
       ,Season as 季节
       ,SUM(SalesVolume) as 销售
       ,SUM(Turnover) as 成交金额
       ,SUM(NoneOtherAmount) as 让利金额
       --,GROUPING(SalesPeriod) as SalesPeriod_G
       --,GROUPING(CounterName) as CounterName_G
       --,GROUPING(Season) as Season_G
from AnnualSalesSummary
group by CounterName,SalesPeriod,Season with rollup
having  GROUPING(SalesPeriod)=0 or GROUPING(CounterName)=1 or GROUPING(Season)=0
ORDER BY CounterName DESC

 

 

 

 

 

 

posted @ 2017-01-05 11:18  Zenxdt  阅读(1026)  评论(0)  编辑  收藏  举报