(
plan_date smalldatetime,--日期
plan_emp varchar(20),--业务员
plan_area varchar(20),--区域
plan_quantity float--数量
)
INSERT INTO saleplan
SELECT '2011-05-01','张三','山东',100 UNION
SELECT '2011-05-01','张三','上海',100 UNION
SELECT '2011-05-01','李四','山东',100 UNION
SELECT '2011-06-01','王五','北京',100
GO
CREATE TABLE salereal --实际销售
(
real_date smalldatetime,
real_emp varchar(20),
real_area varchar(20),
real_quantity float
)
INSERT INTO salereal
SELECT '2011-05-01','张三','山东',20 UNION
SELECT '2011-05-03','张三','山东',30 UNION
SELECT '2011-05-03','李四','山东',30 UNION
SELECT '2011-05-04','张三','上海',30
GO
DECLARE @planstr varchar(max)
SET @planstr=''
SELECT @planstr=@planstr+','+'SUM(CASE WHEN CONVERT(char(7),plan_date,120)='''+RTRIM(CONVERT(char(7),plan_date,120))+''' THEN CAST(plan_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),plan_date,120))+'计划'+']'
FROM saleplan GROUP BY CONVERT(char(7),plan_date,120)
DECLARE @realstr varchar(max)
SET @realstr=''
SELECT @realstr=@realstr+','+'SUM(CASE WHEN CONVERT(char(7),real_date,120)='''+RTRIM(CONVERT(char(7),real_date,120))+''' THEN CAST(real_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),real_date,120))+'实际'+']'
FROM salereal GROUP BY CONVERT(char(7),real_date,120)
EXEC(';WITH t AS
(
SELECT plan_emp,plan_area'+@planstr+' FROM saleplan GROUP BY plan_emp,plan_area
),t1 AS
(
SELECT real_emp,real_area'+@realstr+' FROM salereal GROUP BY real_emp,real_area
)
SELECT * FROM t LEFT JOIN t1 ON t.plan_emp=t1.real_emp AND t.plan_area=t1.real_area')
/*************************************************************************************************
plan_emp plan_area 2011-05计划 2011-06计划 real_emp real_area 2011-05实际
李四 山东 100 0 李四 山东 30
王五 北京 0 100 NULL NULL NULL
张三 山东 100 0 张三 山东 50
张三 上海 100 0 张三 上海 30
*************************************************************************************************/
/*************************************************************************************************
想要结果如下:后面两个合计如果弄不出来可以不要。出来最好(*^__^*) 嘻嘻……
plan_emp plan_area 2011-05计划 2011-05实际 2011-06计划 2011-06实际 计划合计 实际合计
李四 山东 100 30 0 0 100 30
王五 北京 0 0 100 0 100 0
张三 山东 100 50 0 0 100 50
张三 上海 100 30 0 0 100 30
*************************************************************************************************/
16 个解决方案
#1
#2
还有一问题,为撒我后面的那注释在SQL里面都对齐了,咋到这上面就对不齐了,求解
#3
CREATE TABLE saleplan --销售计划
(
plan_date smalldatetime,--日期
plan_emp varchar(20),--业务员
plan_area varchar(20),--区域
plan_quantity float--数量
)
INSERT INTO saleplan
SELECT '2011-05-01','张三','山东',100 UNION
SELECT '2011-05-01','张三','上海',100 UNION
SELECT '2011-05-01','李四','山东',100 UNION
SELECT '2011-06-01','王五','北京',100
GO
CREATE TABLE salereal --实际销售
(
real_date smalldatetime,
real_emp varchar(20),
real_area varchar(20),
real_quantity float
)
INSERT INTO salereal
SELECT '2011-05-01','张三','山东',20 UNION
SELECT '2011-05-03','张三','山东',30 UNION
SELECT '2011-05-03','李四','山东',30 UNION
SELECT '2011-05-04','张三','上海',30
GO
select a.plan_emp,a.plan_area,a.[2011-05] as [2011-05计划],b.[2011-05] as [2011-05实际],a.[2011-06] as [2011-06计划],b.[2011-06] as [2011-06实际],a.计划合计,b.实际合计
from(
select plan_emp,plan_area,[2011-05],[2011-06],isnull([2011-05],0)+isnull([2011-06],0)as 计划合计 from
(select convert(varchar(7),plan_date,120)as plan_date,plan_emp,plan_area,plan_quantity from saleplan )t
pivot (sum([plan_quantity]) for [plan_date] in([2011-05],[2011-06]))tb
)a left join (
select real_emp,real_area,[2011-05],[2011-06],isnull([2011-05],0)+isnull([2011-06],0)as 实际合计 from
(select convert(varchar(7),real_date,120)as real_date,real_emp,real_area,real_quantity from salereal )t
pivot (sum([real_quantity]) for [real_date] in([2011-05],[2011-06]))tb
)b on a.plan_emp=b.real_emp and a.plan_area=b.real_area
go
drop table saleplan,salereal
/*
plan_emp plan_area 2011-05计划 2011-05实际 2011-06计划 2011-06实际 计划合计 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
李四 山东 100 30 NULL NULL 100 30
王五 北京 NULL NULL 100 NULL 100 NULL
张三 山东 100 50 NULL NULL 100 50
张三 上海 100 30 NULL NULL 100 30
(4 行受影响)
*/
#4
空格的字体占位大小不同.
#5
3# 威武
#6
CREATE TABLE saleplan --销售计划
(
plan_date smalldatetime,--日期
plan_emp varchar(20),--业务员
plan_area varchar(20),--区域
plan_quantity float--数量
)
INSERT INTO saleplan
SELECT '2011-05-01','张三','山东',100 UNION
SELECT '2011-05-01','张三','上海',100 UNION
SELECT '2011-05-01','李四','山东',100 UNION
SELECT '2011-06-01','王五','北京',100
GO
CREATE TABLE salereal --实际销售
(
real_date smalldatetime,
real_emp varchar(20),
real_area varchar(20),
real_quantity float
)
INSERT INTO salereal
SELECT '2011-05-01','张三','山东',20 UNION
SELECT '2011-05-03','张三','山东',30 UNION
SELECT '2011-05-03','李四','山东',30 UNION
SELECT '2011-05-04','张三','上海',30
GO
DECLARE @planstr varchar(max)
SET @planstr=''
SELECT @planstr=@planstr+','+'SUM(CASE WHEN CONVERT(char(7),plan_date,120)='''+RTRIM(CONVERT(char(7),date,120))+''' THEN CAST(plan_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),date,120))+'计划'+']'
FROM (select plan_date as date from saleplan union all select real_date from salereal) a GROUP BY CONVERT(char(7),date,120)
DECLARE @realstr varchar(max)
SET @realstr=''
SELECT @realstr=@realstr+','+'SUM(CASE WHEN CONVERT(char(7),real_date,120)='''+RTRIM(CONVERT(char(7),date,120))+''' THEN CAST(real_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),date,120))+'实际'+']'
FROM (select plan_date as date from saleplan union all select real_date from salereal) a GROUP BY CONVERT(char(7),date,120)
EXEC(';WITH t AS
(
SELECT plan_emp,plan_area'+@planstr+',sum(plan_quantity)[计划合计] FROM saleplan GROUP BY plan_emp,plan_area
),t1 AS
(
SELECT real_emp,real_area'+@realstr+',sum(real_quantity) [实际合计] FROM salereal GROUP BY real_emp,real_area
)
SELECT * FROM t Full JOIN t1 ON t.plan_emp=t1.real_emp AND t.plan_area=t1.real_area')
/*
plan_emp plan_area 2011-05计划 2011-06计划 计划合计 real_emp real_area 2011-05实际 2011-06实际 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ---------------------- ----------------------
李四 山东 100 0 100 李四 山东 30 0 30
王五 北京 0 100 100 NULL NULL NULL NULL NULL
张三 山东 100 0 100 张三 山东 50 0 50
张三 上海 100 0 100 张三 上海 30 0 30
(4 行受影响)
#7
学习,我也写了一个行转例的你可以借鉴一下
select
[project],
[多功能一体机]=max(case when [item]='多功能一体机' then [qty] else 0 end),
[其他(多功能业务)]=max(case when [item]='其他(多功能业务)' then [qty] else 0 end),
[激光打印机]=max(case when [item]='激光打印机' then [qty] else 0 end),
[其他(激光业务)]=max(case when [item]='其他(激光业务)' then [qty] else 0 end),
[喷墨打印机]=max(case when [item]='喷墨打印机' then [qty] else 0 end),
[喷墨耗材]=max(case when [item]='喷墨耗材' then [qty] else 0 end),
[其他(喷墨业务)]=max(case when [item]='其他(喷墨业务)' then [qty] else 0 end),
[耗材]=max(case when [item]='耗材' then [qty] else 0 end),
[外设数码]=max(case when [item]='外设数码' then [qty] else 0 end),
getdate() as 日期
from
Periph
group by [project]
#8
日期是动态的
#9
不是固定的
#10
#11
6楼的时间不是固定的,不符合要求?
#12
不符合,他只是在我的基础上加个合计
#13
还有木有人呀,帮帮忙呀
#14
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when dt='''+dt+''' then plan_quantity else 0 end) as ['+dt+'计划],'
+'max(case when dt='''+dt+''' then real_quantity else 0 end) as ['+dt+'实际]'
from
(select convert(varchar(7),plan_date,120) as dt from saleplan
union
select convert(varchar(7),real_date,120) as dt from salereal
) t
set @sql=';with cte1 as(
select convert(varchar(7),plan_date,120) as dt,plan_emp,plan_area,
sum(plan_quantity) as plan_quantity,sum(real_quantity) as real_quantity
from(
select *,0 as real_quantity from saleplan
union all
select real_date,real_emp,real_area,0,real_quantity from salereal
)t
group by convert(varchar(7),plan_date,120),plan_emp,plan_area
)'
+' select plan_emp,plan_area,'+@sql
+',sum(plan_quantity) as [计划合计],sum(real_quantity) as [实际合计]'
+' from cte1 group by plan_emp,plan_area'
--print @sql
exec (@sql)
/**
plan_emp plan_area 2011-05计划 2011-05实际 2011-06计划 2011-06实际 计划合计 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
王五 北京 0 0 100 0 100 0
李四 山东 100 30 0 0 100 30
张三 山东 100 50 0 0 100 50
张三 上海 100 30 0 0 100 30
(4 行受影响)
**/
#15
顺便问下你下面呢注释是咋搞的,呢么齐整
#16
#1
#2
还有一问题,为撒我后面的那注释在SQL里面都对齐了,咋到这上面就对不齐了,求解
#3
CREATE TABLE saleplan --销售计划
(
plan_date smalldatetime,--日期
plan_emp varchar(20),--业务员
plan_area varchar(20),--区域
plan_quantity float--数量
)
INSERT INTO saleplan
SELECT '2011-05-01','张三','山东',100 UNION
SELECT '2011-05-01','张三','上海',100 UNION
SELECT '2011-05-01','李四','山东',100 UNION
SELECT '2011-06-01','王五','北京',100
GO
CREATE TABLE salereal --实际销售
(
real_date smalldatetime,
real_emp varchar(20),
real_area varchar(20),
real_quantity float
)
INSERT INTO salereal
SELECT '2011-05-01','张三','山东',20 UNION
SELECT '2011-05-03','张三','山东',30 UNION
SELECT '2011-05-03','李四','山东',30 UNION
SELECT '2011-05-04','张三','上海',30
GO
select a.plan_emp,a.plan_area,a.[2011-05] as [2011-05计划],b.[2011-05] as [2011-05实际],a.[2011-06] as [2011-06计划],b.[2011-06] as [2011-06实际],a.计划合计,b.实际合计
from(
select plan_emp,plan_area,[2011-05],[2011-06],isnull([2011-05],0)+isnull([2011-06],0)as 计划合计 from
(select convert(varchar(7),plan_date,120)as plan_date,plan_emp,plan_area,plan_quantity from saleplan )t
pivot (sum([plan_quantity]) for [plan_date] in([2011-05],[2011-06]))tb
)a left join (
select real_emp,real_area,[2011-05],[2011-06],isnull([2011-05],0)+isnull([2011-06],0)as 实际合计 from
(select convert(varchar(7),real_date,120)as real_date,real_emp,real_area,real_quantity from salereal )t
pivot (sum([real_quantity]) for [real_date] in([2011-05],[2011-06]))tb
)b on a.plan_emp=b.real_emp and a.plan_area=b.real_area
go
drop table saleplan,salereal
/*
plan_emp plan_area 2011-05计划 2011-05实际 2011-06计划 2011-06实际 计划合计 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
李四 山东 100 30 NULL NULL 100 30
王五 北京 NULL NULL 100 NULL 100 NULL
张三 山东 100 50 NULL NULL 100 50
张三 上海 100 30 NULL NULL 100 30
(4 行受影响)
*/
#4
空格的字体占位大小不同.
#5
3# 威武
#6
CREATE TABLE saleplan --销售计划
(
plan_date smalldatetime,--日期
plan_emp varchar(20),--业务员
plan_area varchar(20),--区域
plan_quantity float--数量
)
INSERT INTO saleplan
SELECT '2011-05-01','张三','山东',100 UNION
SELECT '2011-05-01','张三','上海',100 UNION
SELECT '2011-05-01','李四','山东',100 UNION
SELECT '2011-06-01','王五','北京',100
GO
CREATE TABLE salereal --实际销售
(
real_date smalldatetime,
real_emp varchar(20),
real_area varchar(20),
real_quantity float
)
INSERT INTO salereal
SELECT '2011-05-01','张三','山东',20 UNION
SELECT '2011-05-03','张三','山东',30 UNION
SELECT '2011-05-03','李四','山东',30 UNION
SELECT '2011-05-04','张三','上海',30
GO
DECLARE @planstr varchar(max)
SET @planstr=''
SELECT @planstr=@planstr+','+'SUM(CASE WHEN CONVERT(char(7),plan_date,120)='''+RTRIM(CONVERT(char(7),date,120))+''' THEN CAST(plan_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),date,120))+'计划'+']'
FROM (select plan_date as date from saleplan union all select real_date from salereal) a GROUP BY CONVERT(char(7),date,120)
DECLARE @realstr varchar(max)
SET @realstr=''
SELECT @realstr=@realstr+','+'SUM(CASE WHEN CONVERT(char(7),real_date,120)='''+RTRIM(CONVERT(char(7),date,120))+''' THEN CAST(real_quantity AS float) ELSE 0 END) AS ['+RTRIM(CONVERT(char(7),date,120))+'实际'+']'
FROM (select plan_date as date from saleplan union all select real_date from salereal) a GROUP BY CONVERT(char(7),date,120)
EXEC(';WITH t AS
(
SELECT plan_emp,plan_area'+@planstr+',sum(plan_quantity)[计划合计] FROM saleplan GROUP BY plan_emp,plan_area
),t1 AS
(
SELECT real_emp,real_area'+@realstr+',sum(real_quantity) [实际合计] FROM salereal GROUP BY real_emp,real_area
)
SELECT * FROM t Full JOIN t1 ON t.plan_emp=t1.real_emp AND t.plan_area=t1.real_area')
/*
plan_emp plan_area 2011-05计划 2011-06计划 计划合计 real_emp real_area 2011-05实际 2011-06实际 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ---------------------- ----------------------
李四 山东 100 0 100 李四 山东 30 0 30
王五 北京 0 100 100 NULL NULL NULL NULL NULL
张三 山东 100 0 100 张三 山东 50 0 50
张三 上海 100 0 100 张三 上海 30 0 30
(4 行受影响)
#7
学习,我也写了一个行转例的你可以借鉴一下
select
[project],
[多功能一体机]=max(case when [item]='多功能一体机' then [qty] else 0 end),
[其他(多功能业务)]=max(case when [item]='其他(多功能业务)' then [qty] else 0 end),
[激光打印机]=max(case when [item]='激光打印机' then [qty] else 0 end),
[其他(激光业务)]=max(case when [item]='其他(激光业务)' then [qty] else 0 end),
[喷墨打印机]=max(case when [item]='喷墨打印机' then [qty] else 0 end),
[喷墨耗材]=max(case when [item]='喷墨耗材' then [qty] else 0 end),
[其他(喷墨业务)]=max(case when [item]='其他(喷墨业务)' then [qty] else 0 end),
[耗材]=max(case when [item]='耗材' then [qty] else 0 end),
[外设数码]=max(case when [item]='外设数码' then [qty] else 0 end),
getdate() as 日期
from
Periph
group by [project]
#8
日期是动态的
#9
不是固定的
#10
#11
6楼的时间不是固定的,不符合要求?
#12
不符合,他只是在我的基础上加个合计
#13
还有木有人呀,帮帮忙呀
#14
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when dt='''+dt+''' then plan_quantity else 0 end) as ['+dt+'计划],'
+'max(case when dt='''+dt+''' then real_quantity else 0 end) as ['+dt+'实际]'
from
(select convert(varchar(7),plan_date,120) as dt from saleplan
union
select convert(varchar(7),real_date,120) as dt from salereal
) t
set @sql=';with cte1 as(
select convert(varchar(7),plan_date,120) as dt,plan_emp,plan_area,
sum(plan_quantity) as plan_quantity,sum(real_quantity) as real_quantity
from(
select *,0 as real_quantity from saleplan
union all
select real_date,real_emp,real_area,0,real_quantity from salereal
)t
group by convert(varchar(7),plan_date,120),plan_emp,plan_area
)'
+' select plan_emp,plan_area,'+@sql
+',sum(plan_quantity) as [计划合计],sum(real_quantity) as [实际合计]'
+' from cte1 group by plan_emp,plan_area'
--print @sql
exec (@sql)
/**
plan_emp plan_area 2011-05计划 2011-05实际 2011-06计划 2011-06实际 计划合计 实际合计
-------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
王五 北京 0 0 100 0 100 0
李四 山东 100 30 0 0 100 30
张三 山东 100 50 0 0 100 50
张三 上海 100 30 0 0 100 30
(4 行受影响)
**/
#15
顺便问下你下面呢注释是咋搞的,呢么齐整