求统计的SQL

Question

 表a：
id   name  chargename  date            account  
1      a      加班费      2007－12－1      100
2      b      加班费      2007－12－3       50
3      a      过夜费      2007－12－5       80
4      c      误餐费      2007－11－3       20
5      a      交通费      2007－12－3       30
6      a      办公费      2007－12－10     100
7      b      交通费      2007－12－7      30。。表b：id   name  chargename  date            account  
1      a      加班费      2007－12－1       80
2      b      加班费      2007－12－3       40
3      a      过夜费      2007－12－5       60
4      c      误餐费      2007－11－3       20
5      a      交通费      2007－12－3       30
6      a      办公费      2007－12－10      90
7      b      交通费      2007－12－7       30。。想得到：
按月份（比如2007－12）计算，得到结果如下（排除加班费，过夜费）：
name   应发(表a）account      实发（表b）account     待发a            130                      120           10
b            30                        30            0
c             20                       20            0合计：        180                       170           10

Answer 1

name       应发(表a）account             实发（表b）account           待发 a                         130             120                         10                        
----------------------------------------怎么算的？

Answer 2

select name,(select sum(account) from a where a.name=t.name) as 应发account,(select sum(account) from b where b.name=t.name) as 实发account,(select sum(account) from a where a.name=t.name) - (select sum(account) from b where b.name=t.name) as 待发 from (select name from a union select name from b) t

Answer 3

select   name,(select   sum(account)   from   a   where   a.name=t.name)   as   应发account,
(select   sum(account)   from   b   where   b.name=t.name)   as   实发account,
(select   sum(account)   from   a   where   a.name=t.name)   -   (select   sum(account)   from   b   where   b.name=t.name)   as   待发   
from   (select   name   from   a   union   select   name   from   b)   t

Answer 4

select c.name,应发,实发,待发=应发-实发 from
(select name,sum(account) 应发 from a where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费') group by name) c,
(select name,sum(account) 实发 from b where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费') group by name)d 
where c.name=d.name

Answer 5

declare @a table(id int,name varchar(10),chargename varchar(20),date smalldatetime,account int)
insert @a select 1,'a','加班费','2007-12-1',100 
union all select 2,'b','加班费','2007-12-3',50 
union all select 3,'a','过夜费','2007-12-5',80 
union all select 4,'c','误餐费','2007-12-3',20 
union all select 5,'a','交通费','2007-12-3',30 
union all select 6,'a','办公费','2007-12-10',100 
union all select 7,'b','交通费','2007-12-7',30 declare @b table(id int,name varchar(10),chargename varchar(20),date smalldatetime,account int)
insert @b select 1,'a','加班费','2007-12-1',80 
union all select 2,'b','加班费','2007-12-3',40 
union all select 3,'a','过夜费','2007-12-5',60 
union all select 4,'c','误餐费','2007-12-3',20 
union all select 5,'a','交通费','2007-12-3',30 
union all select 6,'a','办公费','2007-12-10',90 
union all select 7,'b','交通费','2007-12-7',30 
select case when grouping(name)=1 then '合计:' else  name end name,sum(应发) 应发,sum(实发) 实发,sum(待发) 待发
from(
select  
name,
[应发]=sum(account),
[实发]=(select sum(account) from @b where name=a.name and chargename not in('加班费','过夜费') and convert(varchar(6),date,112)='200712'),
[待发]=sum(account)-(select sum(account) from @b where name=a.name and chargename not in('加班费','过夜费') and convert(varchar(6),date,112)='200712') 
from @a a where chargename not in('加班费','过夜费') and convert(varchar(6),date,112)='200712'
group by name
)aa group by name with rollup
--result
/*name       应发          实发          待发          
---------- ----------- ----------- ----------- 
a          130         120         10
b          30          30          0
c          20          20          0
合计:        180         170         10
*/

Answer 6

select isnull(m.name,n.name) name , isnull(m.应发,0) 应发 , isnull(n.实发,0) 实发, isnull(m.应发,0) - isnull(n.实发,0) 待发 from
(select name , sum(account) 应发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt) m
full join
(select name , sum(account) 实发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt) n
on m.name = n.name

Answer 7

select isnull(m.name,n.name) name , isnull(m.应发,0) 应发 , isnull(n.实发,0) 实发, isnull(m.应发,0) - isnull(n.实发,0) 待发 from
(select name , sum(account) 应发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt) m
full join
(select name , sum(account) 实发 from B where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt) n
on m.name = n.name

Answer 8

哦，少了合计。declare @a table(id int,name varchar(10),chargename varchar(20),date smalldatetime,account int)
insert @a select 1,'a','加班费','2007-12-1',100 
union all select 2,'b','加班费','2007-12-3',50 
union all select 3,'a','过夜费','2007-12-5',80 
union all select 4,'c','误餐费','2007-12-3',20 
union all select 5,'a','交通费','2007-12-3',30 
union all select 6,'a','办公费','2007-12-10',100 
union all select 7,'b','交通费','2007-12-7',30 declare @b table(id int,name varchar(10),chargename varchar(20),date smalldatetime,account int)
insert @b select 1,'a','加班费','2007-12-1',80 
union all select 2,'b','加班费','2007-12-3',40 
union all select 3,'a','过夜费','2007-12-5',60 
union all select 4,'c','误餐费','2007-12-3',20 
union all select 5,'a','交通费','2007-12-3',30 
union all select 6,'a','办公费','2007-12-10',90 
union all select 7,'b','交通费','2007-12-7',30 
select c.name,应发,实发,待发=应发-实发 from
(select name,sum(account) 应发 from @a where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费') group by name) c,
(select name,sum(account) 实发 from @b where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费') group by name)d 
where c.name=d.name
union all
select '合计',应发,实发,待发=应发-实发 from
(select sum(account) 应发 from @a where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费')) c,
(select sum(account) 实发 from @b where convert(varchar(7),date,120)='2007-12'
and chargename not in('加班费','过夜费'))d 

Answer 9

--C不在2007-12
create table A(id int, name varchar(10) , chargename varchar(10),date datetime, account int)
insert into A values(1, 'a', '加班费', '2007-12-1 ' , 100 )
insert into A values(2, 'b', '加班费', '2007-12-3 ' , 50 )
insert into A values(3, 'a', '过夜费', '2007-12-5 ' , 80 )
insert into A values(4, 'c', '误餐费', '2007-11-3 ' , 20 )
insert into A values(5, 'a', '交通费', '2007-12-3 ' , 30 )
insert into A values(6, 'a', '办公费', '2007-12-10' , 100 )
insert into A values(7, 'b', '交通费', '2007-12-7 ' , 30 )
create table B(id int, name varchar(10) , chargename varchar(10),date datetime, account int)
insert into B values(1, 'a', '加班费', '2007-12-1 ' , 80 )
insert into B values(2, 'b', '加班费', '2007-12-3 ' , 40 )
insert into B values(3, 'a', '过夜费', '2007-12-5 ' , 60 )
insert into B values(4, 'c', '误餐费', '2007-11-3 ' , 20 )
insert into B values(5, 'a', '交通费', '2007-12-3 ' , 30 )
insert into B values(6, 'a', '办公费', '2007-12-10' , 90 )
insert into B values(7, 'b', '交通费', '2007-12-7 ' , 30 )
godeclare @dt as varchar(7)
set @dt = '2007-12'select isnull(m.name,n.name) name , isnull(m.应发,0) 应发 , isnull(n.实发,0) 实发, isnull(m.应发,0) - isnull(n.实发,0) 待发 from
(select name , sum(account) 应发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) m
full join
(select name , sum(account) 实发 from B where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) n
on m.name = n.namedrop table A,B/*
name       应发          实发          待发          
---------- ----------- ----------- ----------- 
a          130         120         10
b          30          30          0（所影响的行数为 2 行）
*/

Answer 10

--加合计
create table A(id int, name varchar(10) , chargename varchar(10),date datetime, account int)
insert into A values(1, 'a', '加班费', '2007-12-1 ' , 100 )
insert into A values(2, 'b', '加班费', '2007-12-3 ' , 50 )
insert into A values(3, 'a', '过夜费', '2007-12-5 ' , 80 )
insert into A values(4, 'c', '误餐费', '2007-11-3 ' , 20 )
insert into A values(5, 'a', '交通费', '2007-12-3 ' , 30 )
insert into A values(6, 'a', '办公费', '2007-12-10' , 100 )
insert into A values(7, 'b', '交通费', '2007-12-7 ' , 30 )
create table B(id int, name varchar(10) , chargename varchar(10),date datetime, account int)
insert into B values(1, 'a', '加班费', '2007-12-1 ' , 80 )
insert into B values(2, 'b', '加班费', '2007-12-3 ' , 40 )
insert into B values(3, 'a', '过夜费', '2007-12-5 ' , 60 )
insert into B values(4, 'c', '误餐费', '2007-11-3 ' , 20 )
insert into B values(5, 'a', '交通费', '2007-12-3 ' , 30 )
insert into B values(6, 'a', '办公费', '2007-12-10' , 90 )
insert into B values(7, 'b', '交通费', '2007-12-7 ' , 30 )
godeclare @dt as varchar(7)
set @dt = '2007-12'select isnull(m.name,n.name) name , isnull(m.应发,0) 应发 , isnull(n.实发,0) 实发, isnull(m.应发,0) - isnull(n.实发,0) 待发 from
(select name , sum(account) 应发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) m
full join
(select name , sum(account) 实发 from B where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) n
on m.name = n.name
union all
select name = '合计' , sum(应发) 应发 ,sum(实发) 实发 ,sum(待发) 待发 from
(
select isnull(m.name,n.name) name , isnull(m.应发,0) 应发 , isnull(n.实发,0) 实发, isnull(m.应发,0) - isnull(n.实发,0) 待发 from
(select name , sum(account) 应发 from A where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) m
full join
(select name , sum(account) 实发 from B where chargename <> '加班费' and chargename <> '过夜费' and convert(varchar(7),date,120) = @dt group by name) n
on m.name = n.name
) tdrop table A,B/*
name       应发          实发          待发          
---------- ----------- ----------- ----------- 
a          130         120         10
b          30          30          0
合计         160         150         10（所影响的行数为 3 行）
*/

Answer 11

or
--
select case when grouping(name)=1 then '合计：' else name end name,
[应发]=sum(case when flag='a' then account else 0 end),
[实发]=sum(case when flag='b' then account else 0 end),
[待发]=sum(case when flag='a' then account else -account end)
from
(
select *,'a' flag from @a where chargename not in('加班费','过夜费') and convert(varchar(6),date,112)='200712'
union all
select *,'b' flag from @b where chargename not in('加班费','过夜费') and convert(varchar(6),date,112)='200712'
)aa
group by name with rollup

Answer 12

菜鸟写的，好乱。
老鸟不要笑话：
create table tableA
([id] int identity(1,1),[name] varchar(4),chargename varchar(10),[date] datetime,account int)
insert tableA([name],chargename,[date],account)
select 'a','加班费','2007-12-1',100 union all
select 'b','加班费','2007-12-3',50 union all
select 'a','过夜费','2007-12-5',80 union all
select 'c','午餐费','2007-11-3',20 union all
select 'a','交通费','2007-12-3',30 union all
select 'a','办公费','2007-12-10',100 union all
select 'b','交通费','2007-12-7',30 create table tableB
([id] int identity(1,1),[name] varchar(4),chargename varchar(10),[date] datetime,account int)
insert tableB([name],chargename,[date],account)
select 'a','加班费','2007-12-1',80 union all
select 'b','加班费','2007-12-3',40 union all
select 'a','过夜费','2007-12-5',60 union all
select 'c','误餐费','2007-11-3',20 union all
select 'a','交通费','2007-12-3',30 union all
select 'a','办公费','2007-12-10',90 union all
select 'b','交通费','2007-12-7',30 goselect ta.[name],ta.应发Account,tb.实发Account,待发=ta.应发Account-tb.实发Account
from
(select [name],应发Account=sum(case when chargename not in('加班费','过夜费') then account else 0 end)
from tableA a 
where [name]=a.[name] and ([date] between '2007-12-1' and '2007-12-31')
group by [name]) ta 
join
(select [name],实发Account=sum(case when chargename not in('加班费','过夜费') then account else 0 end) 
from tableB b 
where [name]=b.[name] and ([date] between '2007-12-1' and '2007-12-31')
group by [name]) tb 
on ta.[name]=tb.[name]union all select '合计:',
(select sum(case when chargename not in('加班费','过夜费') then account end) from tableA 
where [date] between '2007-12-1' and '2007-12-31'),
(select sum(case when chargename not in('加班费','过夜费') then account end) from tableB 
where [date] between '2007-12-1' and '2007-12-31'),
(select sum(case when chargename not in('加班费','过夜费') then account end) from tableA 
where [date] between '2007-12-1' and '2007-12-31' )-
(select sum(case when chargename not in('加班费','过夜费') then account end) from tableB
where [date] between '2007-12-1' and '2007-12-31')drop table tableA,tableB
/*（所影响的行数为 7 行）
（所影响的行数为 7 行）name  应发Account   实发Account   待发          
----- ----------- ----------- ----------- 
a     130         120         10
b     30          30          0
合计:   160         150         10（所影响的行数为 3 行）警告: 聚合或其它 SET 操作消除了空值。
*/

Answer 13

select ta.[name],ta.应发Account,tb.实发Account,待发=ta.应发Account-tb.实发Account
from
(select [name],应发Account=sum(case when chargename not in('加班费','过夜费') then account else 0 end)
from tableA a 
where [name]=a.[name] and ([date] between '2007-12-1' and '2007-12-31')
group by [name]) ta 
join
(select [name],实发Account=sum(case when chargename not in('加班费','过夜费') then account else 0 end) 
from tableB b 
where [name]=b.[name] and ([date] between '2007-12-1' and '2007-12-31')
group by [name]) tb 
on ta.[name]=tb.[name]union all select '合计',应发Account,实发Account,待发=应发Account-实发Account
from (select 应发Account=sum(case when chargename not in('加班费','过夜费') then account end) from tableA 
where [date] between '2007-12-1' and '2007-12-31') ta,
(select 实发Account=sum(case when chargename not in('加班费','过夜费') then account end) from tableB 
where [date] between '2007-12-1' and '2007-12-31') tb