---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-08-20 15:24:59 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] go create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int) insert [TB] select 'FY1213','P1','IT',810,10000 union all select 'FY1213','P2','IT',810,10000 union all select 'FY1213','P3','IT',810,10000 union all select 'FY1213','P4','IT',810,10000 union all select 'FY1213','P5','IT',810,10000 union all select 'FY1213','P1','AC',810,10000 union all select 'FY1213','P2','AC',810,10000 union all select 'FY1213','P3','AC',810,10000 union all select 'FY1213','P4','AC',810,10000 union all select 'FY1213','P5','AC',810,10000 union all select 'FY1314','P1','IT',810,20000 union all select 'FY1314','P2','IT',810,20000 union all select 'FY1314','P3','IT',810,20000 union all select 'FY1314','P4','IT',810,20000 union all select 'FY1314','P5','IT',810,20000 union ALL select 'FY1314','P1','AC',810,20000 union all select 'FY1314','P2','AC',810,20000 union all select 'FY1314','P3','AC',810,20000 union all select 'FY1314','P4','AC',810,20000 union all select 'FY1314','P5','AC',810,20000 --------------开始查询-------------------------- SELECT ISNULL(b.dept,a.dept)dept,b.Actual,a.[Actual Last Year],CAST(CAST(([Actual Last Year]*1.0/Actual)*100 AS DECIMAL(5,2))AS VARCHAR(5))+'%' [%] FROM ( select DEPT,SUM([Actual])[Actual Last Year] from [TB] WHERE fiscalyear='fy1213'AND FiscalMonth <'P5' GROUP BY DEpt)a RIGHT JOIN ( select dept,SUM([Actual])[Actual] from [TB] WHERE fiscalyear='fy1314' AND FiscalMonth <'P5' GROUP BY DEpt) b ON a.dept=b.dept ----------------结果---------------------------- /* dept Actual Actual Last Year % ---- ----------- ---------------- ------ AC 80000 40000 50.00% IT 80000 40000 50.00% */
select sum(Actual) Actual from tbl where FiscalMonth<'P5'
select dept, sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) as Actual, sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [Actual Last Y], sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) *1.0/sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [%] from tb group by dept
where条件可以使用变量来实现不同年份和Pxx这些的筛选
---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-08-20 15:24:59 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] go create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int) insert [TB] select 'FY1213','P1','IT',810,10000 union all select 'FY1213','P2','IT',810,10000 union all select 'FY1213','P3','IT',810,10000 union all select 'FY1213','P4','IT',810,10000 union all select 'FY1213','P5','IT',810,10000 union all select 'FY1213','P1','AC',810,10000 union all select 'FY1213','P2','AC',810,10000 union all select 'FY1213','P3','AC',810,10000 union all select 'FY1213','P4','AC',810,10000 union all select 'FY1213','P5','AC',810,10000 union all select 'FY1314','P1','IT',810,20000 union all select 'FY1314','P2','IT',810,20000 union all select 'FY1314','P3','IT',810,20000 union all select 'FY1314','P4','IT',810,20000 union all select 'FY1314','P5','IT',810,20000 union ALL select 'FY1314','P1','AC',810,20000 union all select 'FY1314','P2','AC',810,20000 union all select 'FY1314','P3','AC',810,20000 union all select 'FY1314','P4','AC',810,20000 union all select 'FY1314','P5','AC',810,20000 --------------开始查询-------------------------- select dept, sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as Actual, sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) as [Actual Last Y], sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) *100.0/sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as [%] from tb group by dept ----------------结果---------------------------- /* dept Actual Actual Last Y % ---- ----------- ------------- --------------------------------------- AC 80000 40000 50.000000000000 IT 80000 40000 50.000000000000(2 行受影响)*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:24:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int)
insert [TB]
select 'FY1213','P1','IT',810,10000 union all
select 'FY1213','P2','IT',810,10000 union all
select 'FY1213','P3','IT',810,10000 union all
select 'FY1213','P4','IT',810,10000 union all
select 'FY1213','P5','IT',810,10000 union all
select 'FY1213','P1','AC',810,10000 union all
select 'FY1213','P2','AC',810,10000 union all
select 'FY1213','P3','AC',810,10000 union all
select 'FY1213','P4','AC',810,10000 union all
select 'FY1213','P5','AC',810,10000 union all
select 'FY1314','P1','IT',810,20000 union all
select 'FY1314','P2','IT',810,20000 union all
select 'FY1314','P3','IT',810,20000 union all
select 'FY1314','P4','IT',810,20000 union all
select 'FY1314','P5','IT',810,20000 union ALL
select 'FY1314','P1','AC',810,20000 union all
select 'FY1314','P2','AC',810,20000 union all
select 'FY1314','P3','AC',810,20000 union all
select 'FY1314','P4','AC',810,20000 union all
select 'FY1314','P5','AC',810,20000
--------------开始查询--------------------------
SELECT ISNULL(b.dept,a.dept)dept,b.Actual,a.[Actual Last Year],CAST(CAST(([Actual Last Year]*1.0/Actual)*100 AS DECIMAL(5,2))AS VARCHAR(5))+'%' [%]
FROM (
select DEPT,SUM([Actual])[Actual Last Year] from [TB]
WHERE fiscalyear='fy1213'AND FiscalMonth <'P5'
GROUP BY DEpt)a RIGHT JOIN (
select dept,SUM([Actual])[Actual]
from [TB]
WHERE fiscalyear='fy1314' AND FiscalMonth <'P5'
GROUP BY DEpt) b ON a.dept=b.dept
----------------结果----------------------------
/*
dept Actual Actual Last Year %
---- ----------- ---------------- ------
AC 80000 40000 50.00%
IT 80000 40000 50.00%
*/
select sum(Actual) Actual from tbl
where FiscalMonth<'P5'
sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) as Actual,
sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [Actual Last Y],
sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) *1.0/sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [%]
from
tb
group by
dept
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:24:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int)
insert [TB]
select 'FY1213','P1','IT',810,10000 union all
select 'FY1213','P2','IT',810,10000 union all
select 'FY1213','P3','IT',810,10000 union all
select 'FY1213','P4','IT',810,10000 union all
select 'FY1213','P5','IT',810,10000 union all
select 'FY1213','P1','AC',810,10000 union all
select 'FY1213','P2','AC',810,10000 union all
select 'FY1213','P3','AC',810,10000 union all
select 'FY1213','P4','AC',810,10000 union all
select 'FY1213','P5','AC',810,10000 union all
select 'FY1314','P1','IT',810,20000 union all
select 'FY1314','P2','IT',810,20000 union all
select 'FY1314','P3','IT',810,20000 union all
select 'FY1314','P4','IT',810,20000 union all
select 'FY1314','P5','IT',810,20000 union ALL
select 'FY1314','P1','AC',810,20000 union all
select 'FY1314','P2','AC',810,20000 union all
select 'FY1314','P3','AC',810,20000 union all
select 'FY1314','P4','AC',810,20000 union all
select 'FY1314','P5','AC',810,20000
--------------开始查询--------------------------
select dept,
sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as Actual,
sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) as [Actual Last Y],
sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) *100.0/sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as [%]
from
tb
group by
dept
----------------结果----------------------------
/*
dept Actual Actual Last Y %
---- ----------- ------------- ---------------------------------------
AC 80000 40000 50.000000000000
IT 80000 40000 50.000000000000(2 行受影响)*/