【三张数据表生成二维报表的存储怎么写效率高】

Question

DepInfo 部门表
字段，DepID，DepName
FItem 项目表
字段，FeeId，Feename
BugPlay 计划表
字段，Isyscode，DepID，FeeID，Year，Month，BugMoney
-- 生成测试数据CREATE TABLE [dbo].[DepInfo] (
[DepID] [int] IDENTITY (1, 1) NOT NULL ,
[DepName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
)  ON [PRIMARY]
GOinsert into DepInfo(DepName)
select '部门1'
union
select '部门2'
union
select '部门3'
union
select '部门4'
union
select '部门5'CREATE TABLE [dbo].[FItem] (
[FeeID] [int] IDENTITY (1, 1) NOT NULL ,
[FeeName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GOinsert into FItem(FeeName)
select '项目1'
union
select '项目2'
union
select '项目3'
union
select '项目4'
union
select '项目5'CREATE TABLE [dbo].[BudgetMonthDep] (
[Isyscode] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [int] NOT NULL ,
[Month] [int] NOT NULL ,
[DepID] [int] NOT NULL ,
[FeeID] [int] NOT NULL ,
[BugMoney] [decimal](19, 4) NOT NULL ,
) ON [PRIMARY]
GOInsert into BudgetMonthDep([year],[month],depid,feeid,BugMoney)
select 2011,11,1,1,200.00
union
select 2011,11,1,3,600.00
union
select 2011,11,2,1,300.00
union
select 2011,11,3,1,20.00
union
select 2011,11,3,5,10.00
union
select 2011,11,4,1,800.00
union
select 2011,12,1,2,500.00
union
select 2011,12,2,3,800.00/* 效果 查询年度月份，假设查2011,11如下效果，如果没有计划，为0或者为空都行。
部门1   部门2   部门3   部门4   部门5
项目1  200.00  300.00  20.00   800.00    0
项目2  0       0       0       0         0
项目3  600.00  0       0       0         0
项目4  0       0       0       0         0
项目5  0       0       10.00   0         0
*/
怎么实现这个效果？

Answer 1

更正，计划表名字错。 应该是BugPlay 不是BudgetMonthDep

Answer 2

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+N','+quotename([DepName])+N'=sum(case when a.[DepID]=N'+quotename([DepID],'''')+N' then a.BugMoney else 0 end)'from [DepInfo] 
PRINT @s
--顯示生成語句
print N'
SELECT b.[FeeName]'+@s+'
FROM BugPlay AS a
right JOIN [FItem] AS b ON a.[FeeID]=b.[FeeID]
group by b.[FeeName]'EXEC('
SELECT b.[FeeName]'+@s+'
FROM BugPlay AS a
right JOIN [FItem] AS b ON a.[FeeID]=b.[FeeID]
group by b.[FeeName]')go/*
FeeName 部门1 部门2 部门3 部门4 部门5
项目1 200.0000 300.0000 20.0000 800.0000 0.0000
项目2 500.0000 0.0000 0.0000 0.0000 0.0000
项目3 600.0000 800.0000 0.0000 0.0000 0.0000
项目4 0.0000 0.0000 0.0000 0.0000 0.0000
项目5 0.0000 0.0000 10.0000 0.0000 0.0000
*/

Answer 3

動態生成的語句SELECT b.[FeeName],[部门1]=sum(case when a.[DepID]=N'1' then a.BugMoney else 0 end),[部门2]=sum(case when a.[DepID]=N'2' then a.BugMoney else 0 end),[部门3]=sum(case when a.[DepID]=N'3' then a.BugMoney else 0 end),[部门4]=sum(case when a.[DepID]=N'4' then a.BugMoney else 0 end),[部门5]=sum(case when a.[DepID]=N'5' then a.BugMoney else 0 end)
FROM BugPlay AS a
right JOIN [FItem] AS b ON a.[FeeID]=b.[FeeID]
group by b.[FeeName]

Answer 4

http://blog.csdn.net/qianjin036a/article/details/6926841

Answer 5

感谢，部门和项目都是不确定的，肯定要用动态语句，就是怕数据多了之后查询慢，想要一个效率高点的查询。
--------------
不確定只能用動態sql2005的方法
declare @s nvarchar(max),@s2 NVARCHAR(max)
Select   
 @s=isnull(@s+',','')+quotename([DepID]),
 @s2=isnull(@s2+',','')+'isnull('+quotename([DepID]) +',0) as '+QUOTENAME([DepName])
from [DepInfo]PRINT 'select [FeeName],'+@s2+'
from 
(SELECT b.[FeeName],a.[DepID],a.[BugMoney] FROM BugPlay AS a RIGHT  JOIN [FItem]  AS b ON a.[FeeID]=b.[FeeID] ) as a 
pivot (sum([BugMoney]) for [DepID] in('+@s+') as b'exec('select [FeeName],'+@s2+'
from 
(SELECT b.[FeeName],a.[DepID],a.[BugMoney] FROM BugPlay AS a RIGHT  JOIN [FItem]  AS b ON a.[FeeID]=b.[FeeID] ) as a 
pivot (sum([BugMoney]) for [DepID] in('+@s+'))as b')/*FeeName 部门1 部门2 部门3 部门4 部门5
项目1 200.0000 300.0000 20.0000 800.0000 0.0000
项目2 500.0000 0.0000 0.0000 0.0000 0.0000
项目3 600.0000 800.0000 0.0000 0.0000 0.0000
项目4 0.0000 0.0000 0.0000 0.0000 0.0000
项目5 0.0000 0.0000 10.0000 0.0000 0.0000
*/--生成語句select [FeeName],isnull([1],0) as [部门1],isnull([2],0) as [部门2],isnull([3],0) as [部门3],isnull([4],0) as [部门4],isnull([5],0) as [部门5]
from 
(SELECT b.[FeeName],a.[DepID],a.[BugMoney] FROM BugPlay AS a RIGHT  JOIN [FItem]  AS b ON a.[FeeID]=b.[FeeID] ) as a 
pivot (sum([BugMoney]) for [DepID] in([1],[2],[3],[4],[5]) as b

Answer 6

CREATE TABLE [dbo].[DepInfo] (
    [DepID] [int] IDENTITY (1, 1) NOT NULL ,
    [DepName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
)  ON [PRIMARY]
GOinsert into DepInfo(DepName)
select '部门1'
union
select '部门2'
union
select '部门3'
union
select '部门4'
union
select '部门5'CREATE TABLE [dbo].[FItem] (
    [FeeID] [int] IDENTITY (1, 1) NOT NULL ,
    [FeeName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GOinsert into FItem(FeeName)
select '项目1'
union
select '项目2'
union
select '项目3'
union
select '项目4'
union
select '项目5'CREATE TABLE [dbo].[BugPlay] (
    [Isyscode] [int] IDENTITY (1, 1) NOT NULL ,
    [Year] [int] NOT NULL ,
    [Month] [int] NOT NULL ,
    [DepID] [int] NOT NULL ,
    [FeeID] [int] NOT NULL ,
    [BugMoney] [decimal](19, 4) NOT NULL ,
) ON [PRIMARY]
GOInsert into BugPlay([year],[month],depid,feeid,BugMoney)
select 2011,11,1,1,200.00
union
select 2011,11,1,3,600.00
union
select 2011,11,2,1,300.00
union
select 2011,11,3,1,20.00
union
select 2011,11,3,5,10.00
union
select 2011,11,4,1,800.00
union
select 2011,12,1,2,500.00
union
select 2011,12,2,3,800.00
go
/*
--单表固定行转列
select feeid,[1],[2],[3],[4]  
from (select feeid,depid,bugmoney from BugPlay)a 
pivot  
(sum(BugMoney) for depid in([1],[2],[3],[4]))t
--两表代入固定行转列
select FeeName as 项目,[1],[2],[3],[4]  
from (select f.FeeName,b.depid,b.bugmoney from BugPlay b inner join FItem f on f.FeeID=b.FeeID)a
pivot  
(sum(BugMoney) for depid in([1],[2],[3],[4]))t 
*/
--三表动态行转列
declare @str1 nvarchar(4000),@str2 nvarchar(4000)  
select @str1=ISNULL(@str1+', ','')+'['+ltrim(depid)+']',@str2=ISNULL(@str2+', ','')+'['+LTRIM(depid)+']['+depname+']' from DepInfo
exec('select FeeName as 项目,'+@str1+'
 from (select f.FeeName,b.depid,b.bugmoney from BugPlay b inner join FItem f on f.FeeID=b.FeeID)a
pivot
(sum(BugMoney) for depid in('+@str1+'))t')
/*
项目                                                 1                                       2                                       3                                       4                                       5
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
项目1                                                200.0000                                300.0000                                20.0000                                 800.0000                                NULL
项目2                                                500.0000                                NULL                                    NULL                                    NULL                                    NULL
项目3                                                600.0000                                800.0000                                NULL                                    NULL                                    NULL
项目5                                                NULL                                    NULL                                    10.0000                                 NULL                                    NULL(4 行受影响)
*/
go
drop table BugPlay,FItem,DepInfo

Answer 7

若為sql 2005以上剛可以用pivot ；