try:
select IsNull(a.部门,b.部门),count(*) as 申报员工人次,sum(IsNull(a.申报金额,0)) as 申报金额
,sum(IsNull(b.实际金额,0)) as 实际金额
from a表 a
full join b表 b on a.部门=b.部门 and a.工号=b.工号
where a.日期 between 开始日期 and 结束日期
and b.日期 between 开始日期 and 结束日期
group by IsNull(a.部门,b.部门)
select IsNull(a.部门,b.部门),count(*) as 申报员工人次,sum(IsNull(a.申报金额,0)) as 申报金额
,sum(IsNull(b.实际金额,0)) as 实际金额
from a表 a
full join b表 b on a.部门=b.部门 and a.工号=b.工号
where a.日期 between 开始日期 and 结束日期
and b.日期 between 开始日期 and 结束日期
group by IsNull(a.部门,b.部门)
set @要查询的日期=getdate()select 部門=isnull(a.部門,b.部門)
,申報員工人次=count(*)
,累計實際金額=sum(b.實際金額)
,累計申報金額=sum(a.申報金額)
from a表 a
full join b表 b on a.部門=b.部門
where a.日期=@要查询的日期 and b.日期=@要查询的日期
group by isnull(a.部門,b.部門)
結果是,除實際金額外,申報員工人次與申報金額的值重複累加
結果同txlicenhe(马可)一樣.To: All
我想要的結果是,如1~15號某部門的申報人次為多少?實際金額是多少?申報金額是多少?
a表 工號, 部門 日期 申報金額
F1 A 2004/01/1 10
F2 A 2004/01/1 10
F3 A 2004/01/1 10
F1 A 2004/01/5 10
F2 A 2004/01/5 10
F3 A 2004/01/5 10
F1 A 2004/01/15 10
F2 A 2004/01/15 10
F3 A 2004/01/15 10
b表 工號 部門 日期 實際金額
F1 A 2004/01/1 8
F2 A 2004/01/1 20
F3 A 2004/01/1 5
F1 A 2004/01/5 10
F2 A 2004/01/5 15
F3 A 2004/01/5 10
F1 A 2004/01/15 18
F2 A 2004/01/15 20
F3 A 2004/01/15 5
,MAX(IsNull(b.实际金额,0)) as 实际金额
from a表 a
full join b表 b on a.部门=b.部门 and a.工号=b.工号
where a.日期 between 开始日期 and 结束日期
and b.日期 between 开始日期 and 结束日期
group by A.工號,IsNull(a.部门,b.部门)
declare @开始日期 datetime,@结束日期 datetime,@部门 char(1)
select @开始日期='2004/01/01'
,@结束日期='2004/01/15'
,@部门='A'--查询
select 申報人次=count(*)
,實際金額=(select sum(實際金額) from b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,申報金額=sum(申報金額)
from a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门--如果工號相同的多次申报只算一次
select 申報人次=count(distinct 工號)
,實際金額=(select sum(實際金額) from b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,申報金額=sum(distinct 申報金額)
from a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门
declare @a表 table(工號 char(2),部門 char(1),日期 datetime,申報金額 int)
insert into @a表
select 'F1','A','2004/01/1',10
union all select 'F2','A','2004/01/1',10
union all select 'F3','A','2004/01/1',10
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',10
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',10
union all select 'F2','A','2004/01/15',10
union all select 'F3','A','2004/01/15',10declare @b表 table(工號 char(2),部門 char(1),日期 datetime,實際金額 int)
insert into @b表
select 'F1','A','2004/01/1',8
union all select 'F2','A','2004/01/1',20
union all select 'F3','A','2004/01/1',5
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',15
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',18
union all select 'F2','A','2004/01/15',20
union all select 'F3','A','2004/01/15',5--定义查询的起始时间
declare @开始日期 datetime,@结束日期 datetime,@部门 char(1)
select @开始日期='2004/01/01'
,@结束日期='2004/01/15'
,@部门='A'--查询
select 申報人次=count(*)
,實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,申報金額=sum(申報金額)
from @a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门--如果工號相同的多次申报只算一次
select 申報人次=count(distinct 工號)
,實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,申報金額=sum(distinct 申報金額)
from @a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门/*--测试结果申報人次 實際金額 申報金額
----------- ----------- -----------
9 111 90(所影响的行数为 1 行)
申報人次 實際金額 申報金額
----------- ----------- -----------
3 111 10(所影响的行数为 1 行)
--*/
F1 A 2004/01/1 10
F2 A 2004/01/1 10
F3 A 2004/01/1 10
F1 A 2004/01/5 10
F2 A 2004/01/5 10
F3 A 2004/01/5 10
F1 A 2004/01/15 10
F2 A 2004/01/15 10
F3 A 2004/01/15 10
b表 工號 部門 日期 實際金額
F1 A 2004/01/1 8
F2 A 2004/01/1 20
F3 A 2004/01/1 5
F1 A 2004/01/5 10
F2 A 2004/01/5 15
F3 A 2004/01/5 10
F1 A 2004/01/15 18
F2 A 2004/01/15 20
F3 A 2004/01/15 5
我想要的結果是:
部門 日期 申報員工人次 累計實際金額 累計申報金額
A 2004/01/1 ~ 2004/1/15 9 111 90
,累計實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,累計申報金額=sum(申報金額)
from @a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门
,累計實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,累計申報金額=sum(申報金額)
from @a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门
declare @开始日期 datetime,@结束日期 datetime,@部门 char(1)
select @开始日期='2004/01/01'
,@结束日期='2004/01/15'
,@部门='A'--查询
select 部門=@部门
,日期=convert(char(10),@开始日期,111)+' ~ '+convert(char(10),@结束日期,111)
,申報員工人次=count(*)
,累計實際金額=(select sum(實際金額) from b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,累計申報金額=sum(申報金額)
from a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门
--下面是测试:--测试数据
declare @a表 table(工號 char(2),部門 char(1),日期 datetime,申報金額 int)
insert into @a表
select 'F1','A','2004/01/1',10
union all select 'F2','A','2004/01/1',10
union all select 'F3','A','2004/01/1',10
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',10
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',10
union all select 'F2','A','2004/01/15',10
union all select 'F3','A','2004/01/15',10declare @b表 table(工號 char(2),部門 char(1),日期 datetime,實際金額 int)
insert into @b表
select 'F1','A','2004/01/1',8
union all select 'F2','A','2004/01/1',20
union all select 'F3','A','2004/01/1',5
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',15
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',18
union all select 'F2','A','2004/01/15',20
union all select 'F3','A','2004/01/15',5--定义查询的起始时间
declare @开始日期 datetime,@结束日期 datetime,@部门 char(1)
select @开始日期='2004/01/01'
,@结束日期='2004/01/15'
,@部门='A'--查询
select 部門=@部门
,日期=convert(char(10),@开始日期,111)+' ~ '+convert(char(10),@结束日期,111)
,申報員工人次=count(*)
,累計實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=@部门)
,累計申報金額=sum(申報金額)
from @a表
where 日期 between @开始日期 and @结束日期 and 部門=@部门/*--测试结果部門 日期 申報員工人次 累計實際金額 累計申報金額
---- ----------------------- ----------- ----------- -----------
A 2004/01/01 ~ 2004/01/15 9 111 90(所影响的行数为 1 行)
--*/
如果我想一次將所有的部門都統計出來怎麼辦呢?
create proc p_qry
@开始日期 datetime=null, --查询的开始日期,如果不指定,则不限制开始日期
@结束日期 datetime=null, --查询的结束日期,如果不指定,则不限制结束日期
@部门 char(1)=null --要查询的部门,如果不指定,则查询所有部门
as
declare @dt char(23)
set @dt=case when @开始日期 is null then ''
else convert(char(10),@开始日期,111) end
+' ~ '+
case when @结束日期 is null then ''
else convert(char(10),@结束日期,111) end --查询
select 部門=isnull(a.部門,b.部門)
,日期=@dt
,申報員工人次,累計實際金額,累計申報金額
from(
select 部門,申報員工人次=count(*),累計申報金額=sum(申報金額)
from a表
where 日期 between isnull(@开始日期,日期) and isnull(@结束日期,日期)
and 部門=isnull(@部门,部門)
group by 部門
)a full join(
select 部門,累計實際金額=sum(實際金額)
from b表
where 日期 between isnull(@开始日期,日期) and isnull(@结束日期,日期)
and 部門=isnull(@部门,部門)
group by 部門
) b on a.部門=b.部門
go
查询所有部门,你在调用时不指定要查询的部门就行了.
查询所有期间数据,不指定日期就行了.
--*/--下面是测试:--测试数据
create table a表(工號 char(2),部門 char(1),日期 datetime,申報金額 int)
insert into a表
select 'F1','A','2004/01/1',10
union all select 'F2','A','2004/01/1',10
union all select 'F3','A','2004/01/1',10
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',10
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',10
union all select 'F2','A','2004/01/15',10
union all select 'F3','A','2004/01/15',10create table b表(工號 char(2),部門 char(1),日期 datetime,實際金額 int)
insert into b表
select 'F1','A','2004/01/1',8
union all select 'F2','A','2004/01/1',20
union all select 'F3','A','2004/01/1',5
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',15
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',18
union all select 'F2','A','2004/01/15',20
union all select 'F3','A','2004/01/15',5
go--查询的存储过程
create proc p_qry
@开始日期 datetime=null, --查询的开始日期,如果不指定,则不限制开始日期
@结束日期 datetime=null, --查询的结束日期,如果不指定,则不限制结束日期
@部门 char(1)=null --要查询的部门,如果不指定,则查询所有部门
as
declare @dt char(23)
set @dt=case when @开始日期 is null then ''
else convert(char(10),@开始日期,111) end
+' ~ '+
case when @结束日期 is null then ''
else convert(char(10),@结束日期,111) end --查询
select 部門=isnull(a.部門,b.部門)
,日期=@dt
,申報員工人次,累計實際金額,累計申報金額
from(
select 部門,申報員工人次=count(*),累計申報金額=sum(申報金額)
from a表
where 日期 between isnull(@开始日期,日期) and isnull(@结束日期,日期)
and 部門=isnull(@部门,部門)
group by 部門
)a full join(
select 部門,累計實際金額=sum(實際金額)
from b表
where 日期 between isnull(@开始日期,日期) and isnull(@结束日期,日期)
and 部門=isnull(@部门,部門)
group by 部門
) b on a.部門=b.部門
go--调用存储过程
exec p_qry '2004/01/01','2004/01/15'exec p_qry @结束日期='2004/01/15',@部门='A'
go--删除测试环境
drop table a表,b表
drop proc p_qry/*--测试结果部門 日期 申報員工人次 累計實際金額 累計申報金額
---- ----------------------- ----------- ----------- -----------
A 2004/01/01 ~ 2004/01/15 9 111 90(所影响的行数为 1 行)
--*/
insert into a表
select 'F1','A','2004/01/1',10
union all select 'F2','A','2004/01/1',10
union all select 'F3','A','2004/01/1',10
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',10
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',10
union all select 'F2','A','2004/01/15',10
union all select 'F3','A','2004/01/15',10
go
create table b表(工號 char(2),部門 char(1),日期 datetime,實際金額 int)
insert into b表
select 'F1','A','2004/01/1',8
union all select 'F2','A','2004/01/1',20
union all select 'F3','A','2004/01/1',5
union all select 'F1','A','2004/01/5',10
union all select 'F2','A','2004/01/5',15
union all select 'F3','A','2004/01/5',10
union all select 'F1','A','2004/01/15',18
union all select 'F2','A','2004/01/15',20
union all select 'F3','A','2004/01/15',5
go--另一种方法
select x.部門, '2004/01/01'+'~'+'2004/01/15' as 日期, sum(x.申報員工人次) 申報員工人次, sum(x.累計實際金額) 累計實際金額, sum(x.累計申報金額) 累計申報金額
from
(
select 部門, count(*) as 申報員工人次, sum(申報金額) as 累計申報金額, 0 as 累計實際金額
from a表
where 日期>='2004/01/01' and 日期<='2004/01/15'
group by 部門
union all
select 部門, 0 as 申報員工人次, 0 as 累計申報金額, sum(實際金額) as 累計實際金額
from b表
where 日期>='2004/01/01' and 日期<='2004/01/15'
group by 部門
) as x
group by x.部門--结果
部門 日期 申報員工人次 累計實際金額 累計申報金額
---- --------------------- ----------- ----------- -----------
A 2004/01/01~2004/01/15 9 111 90
,申報員工人次=count(*)
,累計實際金額=(select sum(實際金額) from @b表
where 日期 between @开始日期 and @结束日期 and 部門=a.部门)
,累計申報金額=sum(申報金額)
from @a表 a
where 日期 between @开始日期 and @结束日期
group by 部門
1、某些部门在a表中有记录,在b表中没有的情况
2、某些部门在b表中有记录,在a表中没有的情况