select 1月份=CASE 月份 WHEN 1 ISNULL(统计字段,0) ELSE 0 END,....... FROM 表
create table table_ (column float default(0) )
晕是不是加一个1到12月的表,使用LEFT JOIN?
一般这样: SELECT * FROM TABLE1 A CROSS JOIN( SELECT [MONTH]=1 UNION ALL SELECT [MONTH]=2 UNION ALL SELECT [MONTH]=3 UNION ALL SELECT [MONTH]=4 . . . . ) LEFT JOIN TABLE2 B ON.....
CREATE PROCEDURE [dbo].[PROC_advkind] @seltype int=0, @selyear int=0, @strt varchar(100) ='', @advkind varchar(100) ='', @buskind varchar(100) ='', @charttype varchar(100) ='', @accountdate varchar(100) ='' AS--业务类型,行业类型,播出形式 if @seltype=0 begin exec('select advkind as ''部门'',sum(isnull(getmoney,0)) as ''收入'' from paydetialall_view where '+ @strt+' and buskind in ('+@buskind+') and year(paydate)='+@selyear+' group by advkind' ) endif @seltype=1 begin exec(' select a.quarter as ''季度'',sum(b.getmoney) as ''收入'' from ( SELECT quarter=''第一季度'' UNION ALL SELECT quarter=''第二季度'' UNION ALL SELECT quarter=''第三季度'' UNION ALL SELECT quarter=''第四季度'' ) a left join ( select(case when( month(paydate)>=1 and month(paydate)<=3) then ''第一季度'' else case when( month(paydate)>=4 and month(paydate)<=6) then ''第二季度'' else case when ( month(paydate)>=7 and month(paydate)<=9) then ''第三季度'' else case when ( month(paydate)>=10 and month(paydate)<=12) then ''第四季度'' else '''' end end end end ) as quarter ,isnull(getmoney,0) as getmoney,advkind from paydetialall_view where year(paydate)='+@selyear+' and advkind='''+@advkind+''' and buskind in ('+@buskind+') ) b on a.quarter =b.quartergroup by a.quarter order by charindex('',''+rtrim(a.quarter)+'','' , '',第二季度,第三季度,第四季度,第一季度'') ') endif @seltype=2 begin exec(' select a.themonth as ''月份'' ,sum( getmoney) as ''收入'' from ( SELECT themonth=1 UNION ALL SELECT themonth=2 UNION ALL SELECT themonth=3 UNION ALL SELECT themonth=4 UNION ALL SELECT themonth=5 UNION ALL SELECT themonth=6 UNION ALL SELECT themonth=7 UNION ALL SELECT themonth=8 UNION ALL SELECT themonth=9 UNION ALL SELECT themonth=10 UNION ALL SELECT themonth=11 UNION ALL SELECT themonth=12 ) a left join( select month(paydate) as themonth ,isnull(getmoney,0) as getmoney,advkind from paydetialall_view where year(paydate)='+@selyear+' and advkind='''+@advkind+''' and buskind in ('+@buskind+') ) b on a.themonth =b.themonthgroup by a.themonth order by a.themonth ') end GO
(column float default(0)
)
SELECT *
FROM TABLE1 A
CROSS JOIN(
SELECT [MONTH]=1 UNION ALL
SELECT [MONTH]=2 UNION ALL
SELECT [MONTH]=3 UNION ALL
SELECT [MONTH]=4
.
.
.
.
)
LEFT JOIN
TABLE2 B
ON.....
@seltype int=0,
@selyear int=0,
@strt varchar(100) ='',
@advkind varchar(100) ='',
@buskind varchar(100) ='',
@charttype varchar(100) ='',
@accountdate varchar(100) =''
AS--业务类型,行业类型,播出形式
if @seltype=0
begin
exec('select advkind as ''部门'',sum(isnull(getmoney,0)) as ''收入'' from paydetialall_view where '+ @strt+' and buskind in ('+@buskind+') and year(paydate)='+@selyear+' group by advkind' )
endif @seltype=1
begin
exec('
select a.quarter as ''季度'',sum(b.getmoney) as ''收入'' from
(
SELECT quarter=''第一季度'' UNION ALL
SELECT quarter=''第二季度'' UNION ALL
SELECT quarter=''第三季度'' UNION ALL
SELECT quarter=''第四季度''
) a
left join
(
select(case when( month(paydate)>=1 and month(paydate)<=3) then ''第一季度''
else case when( month(paydate)>=4 and month(paydate)<=6) then ''第二季度'' else case when ( month(paydate)>=7 and month(paydate)<=9) then ''第三季度''
else case when ( month(paydate)>=10 and month(paydate)<=12) then ''第四季度'' else '''' end end end end )
as quarter ,isnull(getmoney,0) as getmoney,advkind from paydetialall_view where year(paydate)='+@selyear+' and advkind='''+@advkind+''' and buskind in ('+@buskind+')
) b
on a.quarter =b.quartergroup by a.quarter order by charindex('',''+rtrim(a.quarter)+'','' , '',第二季度,第三季度,第四季度,第一季度'') ')
endif @seltype=2
begin
exec('
select a.themonth as ''月份'' ,sum( getmoney) as ''收入'' from (
SELECT themonth=1 UNION ALL
SELECT themonth=2 UNION ALL
SELECT themonth=3 UNION ALL
SELECT themonth=4 UNION ALL
SELECT themonth=5 UNION ALL
SELECT themonth=6 UNION ALL
SELECT themonth=7 UNION ALL
SELECT themonth=8 UNION ALL
SELECT themonth=9 UNION ALL
SELECT themonth=10 UNION ALL
SELECT themonth=11 UNION ALL
SELECT themonth=12
) a
left join(
select month(paydate)
as themonth ,isnull(getmoney,0) as getmoney,advkind from paydetialall_view where year(paydate)='+@selyear+' and advkind='''+@advkind+''' and buskind in ('+@buskind+')
) b
on a.themonth =b.themonthgroup by a.themonth order by a.themonth ')
end
GO