求一查询

Question

表A
Periodid debgin  dend
201301   2013-01-01  2013-01-31 
201302   2013-02-01  2013-02-28 
201303   2013-03-01  2013-03-31 
201304   2013-04-01  2013-04-30 
201305   2013-05-01  2013-05-31 
201306   2013-06-01  2013-06-30 
201307   2013-07-01  2013-07-31 
201308   2013-08-01  2013-08-31 
201309   2013-09-01  2013-09-30 
201310   2013-10-01  2013-10-31  
201311   2013-11-01  2013-11-30 
201312   2013-12-01  2013-12-31 表B
custname billdate fnum 
AAA 2013-01-10 100
BBB 2013-01-12 300
AAA 2013-01-12 50
BBB 2013-02-20 200--结果
根据表B计算各期间的Fnum合计
Custname 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312
AAA 150 0 0 0 0 0 0 0 0 0 0 0
BBB 300 200 0 0 0 0 0 0 0 0 0 0

Answer 1

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-17 17:30:14
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
-- Jun 10 2013 20:09:10 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([Periodid] int,[debgin] datetime,[dend] datetime)
insert [A]
select 201301,'2013-01-01','2013-01-31' union all
select 201302,'2013-02-01','2013-02-28' union all
select 201303,'2013-03-01','2013-03-31' union all
select 201304,'2013-04-01','2013-04-30' union all
select 201305,'2013-05-01','2013-05-31' union all
select 201306,'2013-06-01','2013-06-30' union all
select 201307,'2013-07-01','2013-07-31' union all
select 201308,'2013-08-01','2013-08-31' union all
select 201309,'2013-09-01','2013-09-30' union all
select 201310,'2013-10-01','2013-10-31' union all
select 201311,'2013-11-01','2013-11-30' union all
select 201312,'2013-12-01','2013-12-31'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([custname] varchar(3),[billdate] datetime,[fnum] int)
insert [B]
select 'AAA','2013-01-10',100 union all
select 'BBB','2013-01-12',300 union all
select 'AAA','2013-01-12',50 union all
select 'BBB','2013-02-20',200
--------------开始查询--------------------------declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(PERIODID)+'=SUM(case when [PERIODID]='+quotename(PERIODID,'''')+' then [FNUM] else 0 end)'
from (select B.custname,fnum,A.PERIODID 
FROM B RIGHT JOIN  [a] ON B.BILLDATE BETWEEN A.DEBGIN AND A.DEND
 ) a group by PERIODID
exec('select [custname]'+@s+' from (select B.custname,fnum,A.PERIODID 
FROM B RIGHT JOIN  [a] ON B.BILLDATE BETWEEN A.DEBGIN AND A.DEND)a
WHERE custname IS NOT NULL group by [custname]')
----------------结果----------------------------
/* 
custname 201301      201302      201303      201304      201305      201306      201307      201308      201309      201310      201311      201312
-------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
AAA      150         0           0           0           0           0           0           0           0           0           0           0
BBB      300         200         0           0           0           0           0           0           0           0           0           0
*/

Answer 2

create table #ta([Periodid] int,[debgin] datetime,[dend] datetime)
insert #ta
select 201301,'2013-01-01','2013-01-31' union all
select 201302,'2013-02-01','2013-02-28' union all
select 201303,'2013-03-01','2013-03-31' union all
select 201304,'2013-04-01','2013-04-30' union all
select 201305,'2013-05-01','2013-05-31' union all
select 201306,'2013-06-01','2013-06-30' union all
select 201307,'2013-07-01','2013-07-31' union all
select 201308,'2013-08-01','2013-08-31' union all
select 201309,'2013-09-01','2013-09-30' union all
select 201310,'2013-10-01','2013-10-31' union all
select 201311,'2013-11-01','2013-11-30' union all
select 201312,'2013-12-01','2013-12-31'create table #tb([custname] varchar(3),[billdate] datetime,[fnum] int)
insert #tb
select 'AAA','2013-01-10',100 union all
select 'BBB','2013-01-12',300 union all
select 'AAA','2013-01-12',50 union all
select 'BBB','2013-02-20',200declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+cast(Periodid as varchar)+']=sum(case Periodid when '+cast(Periodid as varchar)+' then fnum end)'
from #ta 
group by Periodidexec('select custname'+@sql+' from #tb b 
left join #ta a on billdate between [debgin] and [dend]
group by custname')
drop table #ta
drop table #tb
/*
custname 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312
AAA 150 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
BBB 300 200 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
*/

Answer 3

create table #temp([Periodid] int,[custname] varchar(3),[fnum] int)
insert into tempdb..#temp select a.Periodid,b.custname,b.fnum from tempdb..#ta a
left join
(
select custname,Periodid,SUM(fnum) as fnum from 
(
select custname,DATENAME(YY,billdate)+DATENAME(M,billdate) as Periodid,fnum 
from tempdb..#tb
) t group by custname,Periodid
) b on a.Periodid= b.PeriodidDeclare @sql varchar(max)
set @sql=STUFF((select ','+QUOTENAME([Periodid]) from #temp group by [Periodid] FOR XML PATH('')) ,1,1,'')
set @sql='select * from #temp t
          pivot (sum([fnum])for [Periodid] in ('+@sql+')) a 
          where custname is not null'
exec(@sql)drop table tempdb..#temp