100分求教 菜鸟求问怎么做跨表的汇总?

Question

假设有如下 几个表
---------------------------------------------
客户档案公司名 结算开始日期 
A公司      1       
B公司      26      
---------------------------------------------
销售单表公司名   单号   金额  开单日期
A公司     1     100   2013-8-15
A公司     2     200   2013-8-20
B公司     3     300   2013-8-24  
B公司     4     400   2013-8-30
---------------------------------------------
收款记录
公司名  收款金额    收款日期
A公司    100        2013-8-30
A公司    100        2013-8-31
B公司    100        2013-08-25
B公司    200        2013-08-27
----------------------------------------------
要求生成这个表: 8月份销售收入表公司名  销售金额   收款金额
A公司   300           200
B公司   300           100  
---------------------------------------------
说明: 因为有些公司 的结算周期是由上个月26号至本月的25号的,所以不能直接按MONTH来判断.例如A公司的8月份的结算周期就是 2013-08-01 至 2013-08-31 , 而B公司 的结算周期是 2013-07-26 至 2013-08-25请问这个SELECT 语句应该怎么写? 

Answer 1

select A.公司名,SUM(金额) as 销售金额,SUM(收款金额) as 收款金额
from 客户档案 a
left join 销售单表 b on A.公司名=b.公司名
left join 收款记录 c on A.公司名=c.公司名
where (条件表达式)
group by A.公司名

Answer 2

create table #ta(公司名 varchar(10),结算开始日期 int)
insert into #ta
select 'A公司',1
union all select 'B公司',26create table #tb(公司名 varchar(10),单号 int,金额 numeric(12,2),开单日期 datetime)
insert into #tb
select 'A公司',1,100,'2013-8-15'
union all select 'A公司',2,200,'2013-8-20'
union all select 'B公司',3,300,'2013-8-24'
union all select 'B公司',4,400,'2013-8-30'create table #tc(公司名 varchar(10),收款金额 numeric(12,2),收款日期 datetime)
insert into #tc
select 'A公司',100,'2013-8-30'
union all select 'A公司',100,'2013-8-31'
union all select 'B公司',100,'2013-08-25'
union all select 'B公司',200,'2013-08-27'select a.公司名,b.结算月份,SUM(b.金额) as 销售金额,SUM(c.收款金额) as 收款金额
from #ta a
left join 
(select 公司名,结算月份,SUM(金额) as 金额 from (
select b.*,
结算月份=case when b.开单日期 between convert(varchar(8),开单日期,120)+cast(a.结算开始日期 as varchar)
and DATEADD(day,-1, CONVERT(varchar(8),dateadd(month,1,开单日期),120)+cast(a.结算开始日期 as varchar))
then convert(varchar(7),DATEADD(day,-1, CONVERT(varchar(8),dateadd(month,1,开单日期),120)+cast(a.结算开始日期 as varchar)),120)
else convert(varchar(7),开单日期,120)  end
from #tb b left join #ta a on a.公司名=b.公司名) t group by 公司名,结算月份
)b on a.公司名=b.公司名
left join 
(select 公司名,结算月份,SUM(收款金额) as 收款金额 from (
select c.*,
结算月份=case when c.收款日期 between convert(varchar(8),收款日期,120)+cast(a.结算开始日期 as varchar)
and DATEADD(day,-1, CONVERT(varchar(8),dateadd(month,1,收款日期),120)+cast(a.结算开始日期 as varchar))
then convert(varchar(7),DATEADD(day,-1, CONVERT(varchar(8),dateadd(month,1,收款日期),120)+cast(a.结算开始日期 as varchar)),120)
else convert(varchar(7),收款日期,120)  end
from #tc c left join #ta a on a.公司名=c.公司名)t group by 公司名,结算月份
)c on a.公司名=c.公司名 and b.结算月份=c.结算月份
group by a.公司名,b.结算月份/*
公司名 结算月份 销售金额 收款金额
A公司 2013-08 300.00 200.00
B公司 2013-08 300.00 100.00
B公司 2013-09 400.00 200.00
*/

Answer 3

DECLARE @m CHAR(7)
SET @m='2013-08'select A.公司名,SUM(b.金额) as 销售金额,SUM(c.收款金额) as 收款金额
from 客户档案 a
left join 销售单表 b on A.公司名=b.公司名 AND 
b.开单日期 
BETWEEN CASE WHEN a.结算开始日期=1 THEN @m+'-01' ELSE dataadd(mm,-1,@m+'-'+RTRIM(a.结算开始日期)) END
AND CASE WHEN a.结算开始日期=1 THEN dateadd(dd,-1,dataadd(mm,1,@m+'-01')) ELSE dataadd(dd,-1,@m+'-'+RTRIM(a.结算开始日期)) END
left join 收款记录 c on A.公司名=c.公司名 AND 
c.开单日期 
BETWEEN CASE WHEN a.结算开始日期=1 THEN @m+'-01' ELSE dataadd(mm,-1,@m+'-'+RTRIM(a.结算开始日期)) END
AND CASE WHEN a.结算开始日期=1 THEN dateadd(dd,-1,dataadd(mm,1,@m+'-01')) ELSE dataadd(dd,-1,@m+'-'+RTRIM(a.结算开始日期)) END
group by A.公司名

Answer 4

create table 客户档案
(公司名 varchar(10), 结算开始日期 int)insert into 客户档案
 select 'A公司', 1 union all       
 select 'B公司', 26create table 销售单表
(公司名 varchar(10), 单号 int, 金额 int, 开单日期 date)insert into 销售单表
 select 'A公司', 1, 100, '2013-8-15' union all
 select 'A公司', 2, 200, '2013-8-20' union all
 select 'B公司', 3, 300, '2013-8-24' union all
 select 'B公司', 4, 400, '2013-8-30'create table 收款记录
(公司名 varchar(10), 收款金额 int, 收款日期 date)insert into 收款记录
 select 'A公司', 100, '2013-8-30' union all
 select 'A公司', 100, '2013-8-31' union all
 select 'B公司', 100, '2013-08-25' union all
 select 'B公司', 200, '2013-08-27'
declare @y int, @m int
select @y=2013, @m=8  -- 设定报表年月;with t as
(select 公司名,
   case when 结算开始日期=1 then cast(rtrim(@y)+'-'+rtrim(@m)+'-'+rtrim(结算开始日期) as date)
        else cast(rtrim(case when @m=1 then @y-1 else @y end)+'-'+rtrim(case when @m=1 then 12 else @m-1 end)+'-'+rtrim(结算开始日期) as date) end 'begindate',
   case when 结算开始日期=1 then dateadd(d,-1,dateadd(m,1,cast(rtrim(@y)+'-'+rtrim(@m)+'-'+rtrim(结算开始日期) as date)))
        else dateadd(d,-1,dateadd(m,1,cast(rtrim(case when @m=1 then @y-1 else @y end)+'-'+rtrim(case when @m=1 then 12 else @m-1 end)+'-'+rtrim(结算开始日期) as date))) end 'enddate'
  from 客户档案
)
select 公司名,[销售金额],[收款金额] from
(select t.公司名,r.金额,r.type from t outer apply
  (select 公司名,金额 '金额','销售金额' 'type' 
     from 销售单表 where 公司名=t.公司名 and 开单日期 between t.begindate and t.enddate
   union all
   select 公司名,收款金额 '金额','收款金额' 'type' 
     from 收款记录 where 公司名=t.公司名 and 收款日期 between t.begindate and t.enddate) r) c
pivot(sum(金额) for type in([销售金额],[收款金额])) p/*
公司名        销售金额        收款金额
---------- ----------- -----------
A公司        300         200
B公司        300         100(2 row(s) affected)
*/