统计查询

Question

表一:
ID 日期      部门  部门合同额  部门合同利润  技术取费  委托服务费  部门利润总额
1  2006-1-1  综合      200       200          200         100        100
2  2006-1-3  商务      100       200          100         100        50
3  2006-3-3  销售      100       50           100         200        200
4  2006-1-1  综合      100       20           50          100        200
5  2006-1-8  综合      200       50           100         300        100
6  2006-3-5  销售      100       200          200         100        50
7  2006-3-10 商务      100       200          100         100        50
要求:按月份统计(统计每个月的各部门的值,如无记录则显示为0),按部门查询,如查询部门为"综合",则显示如下表.
表二:
月份  部门合同额  部门合同利润  技术取费  委托服务费  部门利润总额
一月份  500          270         350          500        400
二月份  0             0           0            0          0
....................................................
十二月份0             0           0            0          0
全年    500           270       350          500          400
如查询部门为"商务"则显示如下:
月份  部门合同额   部门合同利润   技术取费  委托服务费   部门利润总额
一月份  100           200         100         100            50
二月份  0             0           0            0              0
三月份  100           200         100          100           50
四月份  0             0           0            0              0
................................................................
十二月份 0            0           0            0              0
全年     200          400         200          200            100
 
大家帮看看,谢谢!

Answer 1

Select 
N'一月份'As 月份,
SUM(部门合同额) As  部门合同额,
SUM(部门合同利润) As 部门合同利润,
SUM(技术取费) As 技术取费    
SUM(委托服务费) As 委托服务费
SUM(部门利润总额) As 部门利润总额
From 表一
Where Month(日期)=1
Union All
Select 
N'二月份'As 月份,
SUM(部门合同额) As  部门合同额,
SUM(部门合同利润) As 部门合同利润,
SUM(技术取费) As 技术取费    
SUM(委托服务费) As 委托服务费
SUM(部门利润总额) As 部门利润总额
From 表一
Where Month(日期)=2
...
Union All
Select 
N'全年'As 月份,
SUM(部门合同额) As  部门合同额,
SUM(部门合同利润) As 部门合同利润,
SUM(技术取费) As 技术取费    
SUM(委托服务费) As 委托服务费
SUM(部门利润总额) As 部门利润总额
From 表一

Answer 2

如果不顯示為“一月份”，而顯示為"1月份 "，可以用動態SQL語句來寫。

Answer 3

谢谢paoluo(一天到晚游泳的鱼)
我先试试,动态SQL还没用过,可否写下代码让小妹看下.谢谢了

Answer 4

create table aa(ID int,日期 datetime,部门 varchar(100),部门合同额 int,部门合同利润 int,技术取费 int,委托服务费 int,部门利润总额 int)
insert aa select 1,'2006-1-1','综合',200,200,200,100,100
union all select 2,'2006-1-3','商务',100,200,100,100,50
union all select 3,'2006-3-3','销售',100,50,100,200,200
union all select 4,'2006-1-1','综合',100,20,50,100,200
union all select 5,'2006-1-8','综合',200,50,100,300,100
union all select 6,'2006-3-5','销售',100,200,200,100,50
union all select 7,'2006-3-10','商务',100,200,100,100,50
create table aaa(id int,月份 varchar(20))
insert aaa select 1,'一月'
union all select 2,'二月'
union all select 3,'三月'
union all select 4,'四月'
union all select 5,'五月'
union all select 6,'六月'
union all select 7,'七月'
union all select 8,'八月'
union all select 9,'九月'
union all select 10,'十月'
union all select 11,'十一月'
union all select 12,'十二月'select * from (
select aaa.月份,sum(isnull(部门合同额,0))部门合同额,sum(isnull(部门合同利润,0))部门合同利润,sum(isnull(技术取费,0))技术取费,
sum(isnull(委托服务费,0))委托服务费,sum(isnull(部门利润总额,0))部门利润总额
from aaa left join aa on aaa.id=month(日期) where 部门='商务' group by 月份) p
union all
select '全年',sum(isnull(部门合同额,0))部门合同额,sum(isnull(部门合同利润,0))部门合同利润,sum(isnull(技术取费,0))技术取费,
sum(isnull(委托服务费,0))委托服务费,sum(isnull(部门利润总额,0))部门利润总额
from aaa left join aa on aaa.id=month(日期) where 部门='商务'

Answer 5

这样也凑合了^^set nocount on
if exists(select 1 from sysobjects where id=object_id('TestTable') and Xtype='U') drop table testtable
create table testtable
(
id int,
日期 varchar(10),
部门 varchar(10),
部门合同额 int,
部门合同利润 int,
技术取费 int,
委托服务费 int,
部门利润总额 int
)insert into testtable
select 1,  '2006-1-1','综合',200,200,200,100,100 union all
select 2,  '2006-1-3','商务',100,200,100,100,50 union all
select 3,  '2006-3-3','销售',100,50, 100,200,200 union all
select 4,  '2006-1-1','综合',100,20,50,100, 200 union all
select 5,  '2006-1-8','综合',200,50,100,300,100 union all
select 6,  '2006-3-5','销售',100,200,200,100,50 union all
select 7,  '2006-3-10','商务',100,200,100,100,50declare @calendar table
(日期 varchar(6))
insert into @calendar
select '1月份' union all
select '2月份' union all
select '3月份' union all
select '4月份' union all
select '5月份' union all
select '6月份' union all
select '7月份' union all
select '8月份' union all
select '9月份' union all
select '10月份' union all
select '11月份' union all
select '12月份'declare @branch varchar(100)
set @branch='商务'
select
ca.日期,
isnull((select 部门合同额 from testtable tt1 where ca.日期=cast(datepart(mm,cast(tt1.日期 as datetime))as varchar)+'月份' and 部门=@branch),0) as 部门合同额,
isnull((select 部门合同利润 from testtable tt1 where ca.日期=cast(datepart(mm,cast(tt1.日期 as datetime))as varchar)+'月份' and 部门=@branch),0) as 部门合同利润,
isnull((select 技术取费 from testtable tt1 where ca.日期=cast(datepart(mm,cast(tt1.日期 as datetime))as varchar)+'月份' and 部门=@branch),0) as 技术取费,
isnull((select 委托服务费 from testtable tt1 where ca.日期=cast(datepart(mm,cast(tt1.日期 as datetime))as varchar)+'月份' and 部门=@branch),0) as 委托服务费,
isnull((select 部门利润总额 from testtable tt1 where ca.日期=cast(datepart(mm,cast(tt1.日期 as datetime))as varchar)+'月份' and 部门=@branch),0) as 部门利润总额
from
@calendar ca

Answer 6

Create Table 表一
(ID Int Identity(1,1),
 日期       Varchar(10),
 部门 Nvarchar(10),
 部门合同额 Int,
 部门合同利润 Int,
 技术取费 Int,
 委托服务费 Int,
 部门利润总额 Int)
Insert 表一 Select '2006-1-1',  N'综合',      200,       200,          200,         100,        100
Union All Select '2006-1-3',  N'商务',      100,       200,          100,         100,        50
Union All Select '2006-3-3',  N'销售',      100,       50,            100,         200,       200
Union All Select '2006-1-1',  N'综合',      100,       20,            50,           100,        200
Union All Select '2006-1-8',  N'综合',      200,       50,            100,         300,        100
Union All Select '2006-3-5',  N'销售',      100,       200,          200,         100,        50
Union All Select '2006-3-10', N'商务',      100,       200,          100,         100,        50
GO
--不用動態語句
Select 
N'一月份'As 月份,
IsNull(SUM(部门合同额),0) As  部门合同额,
IsNull(SUM(部门合同利润),0) As 部门合同利润,
IsNull(SUM(技术取费),0) As 技术取费,
IsNull(SUM(委托服务费),0) As 委托服务费,
IsNull(SUM(部门利润总额),0) As 部门利润总额
From 表一
Where Month(日期)=1 And Year(日期)=Year(GetDate()) And 部门=N'综合'
Union All
Select 
N'二月份'As 月份,
IsNull(SUM(部门合同额),0) As  部门合同额,
IsNull(SUM(部门合同利润),0) As 部门合同利润,
IsNull(SUM(技术取费),0) As 技术取费,
IsNull(SUM(委托服务费),0) As 委托服务费,
IsNull(SUM(部门利润总额),0) As 部门利润总额
From 表一
Where Month(日期)=2 And Year(日期)=Year(GetDate()) And 部门=N'综合'
Union All
Select 
N'全年'As 月份,
SUM(部门合同额) As  部门合同额,
SUM(部门合同利润) As 部门合同利润,
SUM(技术取费) As 技术取费,
SUM(委托服务费) As 委托服务费,
SUM(部门利润总额) As 部门利润总额
From 表一  Where Year(日期)=Year(GetDate()) And 部门=N'综合'
Go
Drop Table 表一
GO
/*
月份 部门合同额 部门合同利润 技术取费 委托服务费 部门利润总额
一月份 500 270 350 500 400
二月份 0 0 0 0 0
全年 500 270 350 500 400
*/

Answer 7

Create Table 表一
(ID Int Identity(1,1),
 日期       Varchar(10),
 部门 Nvarchar(10),
 部门合同额 Int,
 部门合同利润 Int,
 技术取费 Int,
 委托服务费 Int,
 部门利润总额 Int)
Insert 表一 Select '2006-1-1',  N'综合',      200,       200,          200,         100,        100
Union All Select '2006-1-3',  N'商务',      100,       200,          100,         100,        50
Union All Select '2006-3-3',  N'销售',      100,       50,            100,         200,       200
Union All Select '2006-1-1',  N'综合',      100,       20,            50,           100,        200
Union All Select '2006-1-8',  N'综合',      200,       50,            100,         300,        100
Union All Select '2006-3-5',  N'销售',      100,       200,          200,         100,        50
Union All Select '2006-3-10', N'商务',      100,       200,          100,         100,        50
GO
--動態語句
Create ProceDure SP_TEST(@Dept Nvarchar(10))
As
Begin
Declare @I Int
Declare @S Nvarchar(4000)
Set @I=1
Set @S=''
While @I<=12
Begin
Select @S=@S+' Union All Select N'''+Rtrim(@I)+N'月份''As 月份,IsNull(SUM(部门合同额),0) As  部门合同额,IsNull(SUM(部门合同利润),0) As 部门合同利润,IsNull(SUM(技术取费),0) As 技术取费,IsNull(SUM(委托服务费),0) As 委托服务费,IsNull(SUM(部门利润总额),0) As 部门利润总额 From 表一 Where Month(日期)='+Rtrim(@I)+N' And Year(日期)=Year(GetDate()) And 部门=N'''+@Dept+''''
Set @I=@I+1
End
Select @S=Stuff(@S,1,10,'')+N' Union All Select  N''全年''As 月份,SUM(部门合同额) As  部门合同额,SUM(部门合同利润) As 部门合同利润,SUM(技术取费) As 技术取费,SUM(委托服务费) As 委托服务费,SUM(部门利润总额) As 部门利润总额 From 表一  Where Year(日期)=Year(GetDate()) And 部门=N'''+@Dept+''''
EXEC(@S)
End
GO
EXEC SP_TEST N'综合'
GO
Drop ProceDure SP_TEST
Drop Table 表一
--Result
/*
月份 部门合同额 部门合同利润 技术取费 委托服务费 部门利润总额
1月份 500 270 350 500 400
2月份 0 0 0 0 0
3月份 0 0 0 0 0
4月份 0 0 0 0 0
5月份 0 0 0 0 0
6月份 0 0 0 0 0
7月份 0 0 0 0 0
8月份 0 0 0 0 0
9月份 0 0 0 0 0
10月份 0 0 0 0 0
11月份 0 0 0 0 0
12月份 0 0 0 0 0
全年 500 270 350 500 400
*/

Answer 8

create table #1(yearmonth nchar(7),monthname nchar(4))insert into #1
select '2006-01','一月份'
union all
select '2006-02','二月份'
union all
select '2006-03','三月份'
union all
select '2006-04','四月份'
union all
select '2006-05','五月份'
union all
select '2006-06','六月份'
union all
select '2006-07','七月份'
union all
select '2006-08','八月份'
union all
select '2006-09','九月份'
union all
select '2006-10','十月份'
union all
select '2006-12','十一月份'
union all
select '2006-12','十二月份'select * from #1
select 月份=a.monthname,部门合同额=sum(部门合同额),技术取费=sum(技术取费),委托服务费=sum(委托服务费),部门利润总额=sum(部门利润总额) from  #1 a
left join aa  b on convert(nchar(7),日期,120)=a.yearmonth and 部门='综合'
group by  monthname  with rollup

Answer 9

declare @t table (ID int,日期 datetime ,部門 varchar(50),部門合同額 int,部門合同利潤 int,技術取費 int,委託服務費 int,部門利潤總額 int)
insert @t select '1','2006-1-1','綜合',200,200,200,100,100 union all
select '2','2006-1-3','商務',100,200,100,100,50 union all
select '3','2006-3-3','銷售',100,50,100,200,200 union all
select '4','2006-3-1','綜合',100,20,50,100,200 union all
select '5','2006-3-8','綜合',200,50,100,300,100 union all
select '6','2006-3-5','銷售',100,200,200,100,50 union all
select '7','2006-3-10','商務',100,200,100,100,50 declare @tt table (月份 varchar(50))
insert @tt select '1月份'
union all
select '2月份'
union all
select '3月份'
union all
select '4月份'
union all
select '5月份'
union all
select '6月份'
union all
select '7月份'
union all
select '8月份'
union all
select '9月份'
union all
select '10月份'
union all
select '11月份'
union all
select '12月份'
select cast(datepart(mm,日期) as varchar(5))+'月份'日期,部門合同額,部門合同利潤,技術取費,委託服務費,部門利潤總額 into t from @t  where 部門='綜合' 
select 月份,sum(isnull(部門合同額,0))部門合同額,sum(isnull(部門合同利潤,0))部門合同利潤,sum(isnull(技術取費,0)) 技術取費,sum(isnull(委託服務費,0))委託服務費,sum(isnull(部門利潤總額,0))部門利潤總額 from t right join @tt b on t.日期=b.月份   group by b.月份 
union all
select '全年'月份,sum(部門合同額)部門合同額,sum(部門合同利潤)部門合同利潤,sum(技術取費) 技術取費,sum(委託服務費)委託服務費,sum(部門利潤總額)部門利潤總額  from t 
go
drop table t
go