22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[FeiYou](
[ID] [int] IDENTITY(1,1) NOT NULL,
[dangwei] [nvarchar](50) NULL,
[bumen] [nvarchar](50) NULL,
[shouru] [float] NULL,
[zhichu] [float] NULL,
[regdate] [datetime] NULL,
CONSTRAINT [PK_FeiYou] PRIMARY KEY CLUSTERED
(
[ID] 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].[FeiYou] ADD CONSTRAINT [DF_FeiYou_regdate] DEFAULT (getdate()) FOR [regdate]
GO
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门一','15','12','2009-10-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门二','30','10','2010-08-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门二','45','25','2010-07-21')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门一','51','21','2010-06-02')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门一','41','11','2010-05-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门二','75','25','2010-04-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','55','14','2010-05-03')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','44','12','2010-07-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','22','10','2010-08-03')
declare @stdate datetime,
@eddate datetime
set @stdate='2009-10-01 00:00:00.000'
set @eddate=GETDATE()
select isnull(dangwei,'总计') as 单位名称,isnull(bumen,'小计') as 部门名称,SUM(shouru) as 收入总和,SUM(zhichu) as 支出总和,
本年累计收入=(select isnull(SUM(shouru),0) from FeiYou
where a.dangwei=dangwei and a.bumen=bumen and regdate between '2010-01-01' and GETDATE()),
本年累计支出=(select isnull(SUM(zhichu),0) from FeiYou
where a.dangwei=dangwei and a.bumen=bumen and regdate between '2010-01-01' and GETDATE())
from FeiYou a
where regdate between @stdate and @eddate
group by dangwei,bumen with rollup
order by dangwei desc,bumen desc
单位名称 部门名称 收入总和 支出总和 本年累计收入 本年累计支出
单位一 部门一 15 12 0 0
单位一 部门二 75 35 75 35
单位一 小计 90 47 0 0
单位三 部门一 121 36 121 36
单位三 小计 121 36 0 0
单位二 部门一 92 32 92 32
单位二 部门二 75 25 75 25
单位二 小计 167 57 0 0
总计 小计 378 140 0 0
declare @T table
(
groups char(10),
Item varchar(10),
Color varchar(10),
Quantity int
)
insert into @T select 'aa','table','blue',124
union all select 'bb','table','red',-23
union all select 'bb','cup','green',-23
union all select 'aa','chair','blue',101
union all select 'aa','chair','red',-90
--select groups,Item,color,sum(Quantity) Quantity,
-- grouping(groups) gp,
-- grouping(Item) Item,
-- grouping(Color) Color
--from @T group by groups,Item,Color with rollup
select case when grouping(groups)=1 then '总计' else groups end as 'groups',
isnull(case when grouping(groups)=0 and grouping(Item)=1 then N'小计'+groups else Item end,'') as 'Item',
isnull(case when grouping(groups)=0 and grouping(Color)=1 and grouping(Item)=0 then Item+N'小计' else Color end,'') as 'Color',
sum(Quantity) Quantity
from @T group by groups,Item,Color with rollup
/*
groups Item Color Quantity
---------- ------------ ------------ -----------
aa chair blue 101
aa chair red -90
aa chair chair小计 11
aa table blue 124
aa table table小计 124
aa 小计aa 135
bb cup green -23
bb cup cup小计 -23
bb table red -23
bb table table小计 -23
bb 小计bb -46
总计 89
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/09/20/4572310.aspx
CREATE TABLE [dbo].[FeiYou](
[ID] [int] IDENTITY(1,1) NOT NULL,
[dangwei] [nvarchar](50) NULL,
[bumen] [nvarchar](50) NULL,
[shouru] [float] NULL,
[zhichu] [float] NULL,
[regdate] [datetime] NULL,
CONSTRAINT [PK_FeiYou] PRIMARY KEY CLUSTERED
(
[ID] 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].[FeiYou] ADD CONSTRAINT [DF_FeiYou_regdate] DEFAULT (getdate()) FOR [regdate]
GO
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门一','15','12','2009-10-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门二','30','10','2010-08-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位一','部门二','45','25','2010-07-21')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门一','51','21','2010-06-02')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门一','41','11','2010-05-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位二','部门二','75','25','2010-04-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','55','14','2010-05-03')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','44','12','2010-07-01')
INSERT INTO [FeiYou]([dangwei],[bumen],[shouru],[zhichu],[regdate])
VALUES('单位三','部门一','22','10','2010-08-03')
select 单位名称,部门名称,sum(收入) as 收入总和,sum(支出) as 支出总和
from tb
group by 单位名称,部门名称