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 阅读(974) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Zenxdt

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

表结构：

测试数据：

原数据结构：

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

实现SQL语句：

公告