紧急的SQL问题
数据库中存储，年，月，计划值，完成值和完成率字段。
ID 年 月 计划 完成 完成率
1 2008 3 100 80 80
2 2008 1 100 3 3
要求统计出某年的计划值，完成值，完成率的统计报表。
1月 2月 3月 ...........12月 合计
计划 100 34 20 20 1200
完成 .......................... ...
完成率......................... ...
- SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YearPlan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[YearPlan]GOCREATE TABLE [dbo].[YearPlan] ( [Id] [int] NOT NULL , [Org_Id] [int] NOT NULL , [Category_Id] [int] NOT NULL , [Years] [int] NULL , [Moth] [int] NULL , [Plans] [decimal](18, 0) NULL , [Complete] [decimal](18, 0) NULL , [CompleteRatio] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL , [SubmitTime] [datetime] NULL , [Status] [char] (4) COLLATE Chinese_PRC_CI_AS NULL , [Operator_Id] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO
[解决办法]
哎呀
这年头 数据库都 这么复杂了
真愁死我了 郁闷
看来我都不是一般的菜鸟了
学学~~~~~~~~~我 死进 学~~~~~~~~~~~~
[解决办法]
就按照普通的方式取就可以了。显示样式什么的在代码里处理就可以了
[解决办法]
sql 2000还是2005？
[解决办法]
帮顶
[解决办法]
- C# code
--建立测试环境set nocount oncreate table test(ID varchar(20),年 int,月 int,计划 int,完成 int,完成率 float)insert into test select '1','2008','3','100','80','80'insert into test select '2','2008','1','100','3','3'go--测试select 年,'计划',sum(case when 月=1 then 计划 else 0 end )[1月],sum(case when 月=2 then 计划 else 0 end )[2月],sum(case when 月=3 then 计划 else 0 end )[3月],--...sum(case when 月=12 then 计划 else 0 end )[12月] from testgroup by 年unionselect 年,'完成',sum(case when 月=1 then 完成 else 0 end )[1月],sum(case when 月=2 then 完成 else 0 end )[2月],sum(case when 月=3 then 完成 else 0 end )[3月],--...sum(case when 月=12 then 完成 else 0 end )[12月] from testgroup by 年unionselect 年,'完成率',sum(case when 月=1 then 完成率 else 0 end )[1月],sum(case when 月=2 then 完成率 else 0 end )[2月],sum(case when 月=3 then 完成率 else 0 end )[3月],--...sum(case when 月=12 then 完成率 else 0 end )[12月] from testgroup by 年--删除测试环境drop table test set nocount off
[解决办法]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YearPlan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[YearPlan]
GO
CREATE TABLE YearPlan (
[Id] [int] NOT NULL identity(1,1),
[Years] [int] NULL ,
[Moth] [int] NULL ,
[Plans] [decimal](18, 0) NULL ,
[Complete] [decimal](18, 0) NULL ,
[CompleteRatio] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into YearPlan
select 2007,1,100,80,'80' union all
select 2007,1,200,80,'40' union all
select 2007,2,200,80,'40' union all
select 2007,2,80,80,'100' union all
select 2007,3,200,80,'40' union all
select 2007,4,800,80,'10' union all
select 2007,5,400,80,'20' union all
select 2007,6,400,80,'20' union all
select 2007,7,400,80,'20' union all
select 2007,8,400,80,'20' union all
select 2007,8,200,80,'40' union all
select 2007,9,200,80,'40' union all
select 2007,10,200,80,'40' union all
select 2007,11,200,80,'40' union all
select 2007,12,200,80,'40' union all
select 2008,1,100,80,'80' union all
select 2008,2,200,80,'40' union all
select 2008,3,400,80,'20' union all
select 2008,4,800,80,'10'
--select * from YearPlan
--declare @t table([Moth] int,
-- [Plans] [decimal](18, 0) ,
-- [Complete] [decimal](18, 0),
-- [CompleteRatio] [nvarchar](8))
select [Moth],[Plans],[Complete],[CompleteRatio] into #1 from YearPlan where [Years]=2007
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+'sum(case when [Moth]='+cast(a.[Moth] as varchar(1000))+' then [Plans] else 0 end) as ['+cast(a.[Moth] as varchar(100)) +'月],'
from
(
select distinct [Moth] from #1
) a
set @sql=substring(@sql,1,len(@sql)-1)+' from #1 '
create table #11
(
[1月] [decimal](18, 0),
[2月] [decimal](18, 0),
[3月] [decimal](18, 0),
[4月] [decimal](18, 0),
[5月] [decimal](18, 0),
[6月] [decimal](18, 0),
[7月] [decimal](18, 0),
[8月] [decimal](18, 0),
[9月] [decimal](18, 0),
[10月] [decimal](18, 0),
[11月] [decimal](18, 0),
[12月] [decimal](18, 0)
)
insert into #11
exec(@sql)
declare @sq2 varchar(8000)
set @sq2='select '
select @sq2=@sq2+'sum(case when [Moth]='+cast(a.[Moth] as varchar(1000))+' then [Complete] else 0 end) as ['+cast(a.[Moth] as varchar(100)) +'月],'
from
(
select distinct [Moth] from #1
) a
set @sq2=substring(@sq2,1,len(@sq2)-1)+' from #1 '
print @sq2
create table #12
(
[1月] [decimal](18, 0),
[2月] [decimal](18, 0),
[3月] [decimal](18, 0),
[4月] [decimal](18, 0),
[5月] [decimal](18, 0),
[6月] [decimal](18, 0),
[7月] [decimal](18, 0),
[8月] [decimal](18, 0),
[9月] [decimal](18, 0),
[10月] [decimal](18, 0),
[11月] [decimal](18, 0),
[12月] [decimal](18, 0)
)
insert into #12
exec(@sq2)
select '计划',* from #11 a union all
select '完成',* from #12
drop table #11
drop table #12
drop table #1
这个不完善，而且完成率还没有实现
比较麻烦
[解决办法]
8楼的代码写的不错,怎么没人顶呀!!!!
[解决办法]
select
sum(case Moth when 1 then plans else 0 end) '1月份',
sum(case Moth when 2 then plans else 0 end)'2月份',
sum(case Moth when 3 then plans else 0 end) '3月份',
sum(case Moth when 4 then plans else 0 end) '4月份' ,
sum(case Moth when 5 then plans else 0 end) '5月份' ,
sum(case Moth when 6 then plans else 0 end) '6月份' ,
sum(case Moth when 7 then plans else 0 end) '7月份' ,
sum(case Moth when 8 then plans else 0 end) '8月份',
sum(case Moth when 9 then plans else 0 end) '9月份',
sum(case Moth when 10 then plans else 0 end) '10月份' ,
sum(case Moth when 11 then plans else 0 end) '11月份',
sum(case Moth when 12 then plans else 0 end) '12月份'
from YearPlan where Years='2000'
可以实现
1月 2月 3月 ...........12月 合计
计划 100 34 20 20 1200
select
sum(case Moth when 1 then complete else 0 end) '1月份',
sum(case Moth when 2 then complete else 0 end)'2月份',
sum(case Moth when 3 then complete else 0 end) '3月份',
sum(case Moth when 4 then complete else 0 end) '4月份' ,
sum(case Moth when 5 then complete else 0 end) '5月份' ,
sum(case Moth when 6 then complete else 0 end) '6月份' ,
sum(case Moth when 7 then complete else 0 end) '7月份' ,
sum(case Moth when 8 then complete else 0 end) '8月份',
sum(case Moth when 9 then complete else 0 end) '9月份',
sum(case Moth when 10 then complete else 0 end) '10月份' ,
sum(case Moth when 11 then complete else 0 end) '11月份',
sum(case Moth when 12 then complete else 0 end) '12月份'
from YearPlan where Years='2000'
可以实现
1月 2月 3月 ...........12月 合计
完成 .......................... ...
[解决办法]
你的完成率类型修改成decimal的话就方便了，可是使用下面这个
select
sum(case Moth when 1 then completeratio else 0 end) '1月份',
sum(case Moth when 2 then completeratio else 0 end)'2月份',
sum(case Moth when 3 then completeratio else 0 end) '3月份',
sum(case Moth when 4 then completeratio else 0 end) '4月份' ,
sum(case Moth when 5 then completeratio else 0 end) '5月份' ,
sum(case Moth when 6 then completeratio else 0 end) '6月份' ,
sum(case Moth when 7 then completeratio else 0 end) '7月份' ,
sum(case Moth when 8 then completeratio else 0 end) '8月份',
sum(case Moth when 9 then completeratio else 0 end) '9月份',
sum(case Moth when 10 then completeratio else 0 end) '10月份' ,
sum(case Moth when 11 then completeratio else 0 end) '11月份',
sum(case Moth when 12 then completeratio else 0 end) '12月份'
from YearPlan where Years='2000'
[解决办法]
select '计划',
IsNull(sum(case when moth = 1 then plans end),0),
IsNull(sum(case when moth = 2 then plans end),0),
IsNull(sum(case when moth = 3 then plans end),0),
IsNull(sum(case when moth = 4 then plans end),0),
IsNull(sum(case when moth = 5 then plans end),0),
IsNull(sum(case when moth = 6 then plans end),0),
IsNull(sum(case when moth = 7 then plans end),0),
IsNull(sum(case when moth = 8 then plans end),0),
IsNull(sum(case when moth = 9 then plans end),0),
IsNull(sum(case when moth = 10 then plans end),0),
IsNull(sum(case when moth = 11 then plans end),0) ,
IsNull(sum(case when moth = 12 then plans end),0),
IsNull(sum(plans),0)
from YearPlan where Years = '2008'
union
select '完成',
IsNull(sum(case when moth = 1 then Complete end),0),
IsNull(sum(case when moth = 2 then Complete end),0),
IsNull(sum(case when moth = 3 then Complete end),0),
IsNull(sum(case when moth = 4 then Complete end),0),
IsNull(sum(case when moth = 5 then Complete end),0),
IsNull(sum(case when moth = 6 then Complete end),0),
IsNull(sum(case when moth = 7 then Complete end),0),
IsNull(sum(case when moth = 8 then Complete end),0),
IsNull(sum(case when moth = 9 then Complete end),0),
IsNull(sum(case when moth = 10 then Complete end),0),
IsNull(sum(case when moth = 11 then Complete end),0) ,
IsNull(sum(case when moth = 12 then Complete end),0),
IsNull(sum(Complete),0)
from YearPlan where Years = '2008'
union
select '完成率',
IsNull(max(case when moth = 1 then CompleteRatio end),0),
IsNull(max(case when moth = 2 then CompleteRatio end),0),
IsNull(max(case when moth = 3 then CompleteRatio end),0),
IsNull(max(case when moth = 4 then CompleteRatio end),0),
IsNull(max(case when moth = 5 then CompleteRatio end),0),
IsNull(max(case when moth = 6 then CompleteRatio end),0),
IsNull(max(case when moth = 7 then CompleteRatio end),0),
IsNull(max(case when moth = 8 then CompleteRatio end),0),
IsNull(max(case when moth = 9 then CompleteRatio end),0),
IsNull(max(case when moth = 10 then CompleteRatio end),0),
IsNull(max(case when moth = 11 then CompleteRatio end),0) ,
IsNull(max(case when moth = 12 then CompleteRatio end),0),
(select case when sum(plans) = 0 then 0 else sum(complete)/sum(plans)*100 end from yearplan where years = '2008')
from YearPlan where Years = '2008'
group by years
[解决办法]
把你的完成率改成decimal吧，那样的话就可以使用这个语句实现下面的效果，遗憾的是没有合计
1月 2月 3月 ...........12月
计划 100 34 20 20
完成 ..........................
完成率.........................
select
sum(case Moth when 1 then plans else 0 end) '1月份',
sum(case Moth when 2 then plans else 0 end)'2月份',
sum(case Moth when 3 then plans else 0 end) '3月份',
sum(case Moth when 4 then plans else 0 end) '4月份' ,
sum(case Moth when 5 then plans else 0 end) '5月份' ,
sum(case Moth when 6 then plans else 0 end) '6月份' ,
sum(case Moth when 7 then plans else 0 end) '7月份' ,
sum(case Moth when 8 then plans else 0 end) '8月份',
sum(case Moth when 9 then plans else 0 end) '9月份',
sum(case Moth when 10 then plans else 0 end) '10月份' ,
sum(case Moth when 11 then plans else 0 end) '11月份',
sum(case Moth when 12 then plans else 0 end) '12月份'
from YearPlan where Years='2000'
union
select
sum(case Moth when 1 then complete else 0 end) '1月份',
sum(case Moth when 2 then complete else 0 end)'2月份',
sum(case Moth when 3 then complete else 0 end) '3月份',
sum(case Moth when 4 then complete else 0 end) '4月份' ,
sum(case Moth when 5 then complete else 0 end) '5月份' ,
sum(case Moth when 6 then complete else 0 end) '6月份' ,
sum(case Moth when 7 then complete else 0 end) '7月份' ,
sum(case Moth when 8 then complete else 0 end) '8月份',
sum(case Moth when 9 then complete else 0 end) '9月份',
sum(case Moth when 10 then complete else 0 end) '10月份' ,
sum(case Moth when 11 then complete else 0 end) '11月份',
sum(case Moth when 12 then complete else 0 end) '12月份'
from YearPlan where Years='2000'
union
select
sum(case Moth when 1 then completeratio else 0 end) '1月份',
sum(case Moth when 2 then completeratio else 0 end)'2月份',
sum(case Moth when 3 then completeratio else 0 end) '3月份',
sum(case Moth when 4 then completeratio else 0 end) '4月份' ,
sum(case Moth when 5 then completeratio else 0 end) '5月份' ,
sum(case Moth when 6 then completeratio else 0 end) '6月份' ,
sum(case Moth when 7 then completeratio else 0 end) '7月份' ,
sum(case Moth when 8 then completeratio else 0 end) '8月份',
sum(case Moth when 9 then completeratio else 0 end) '9月份',
sum(case Moth when 10 then completeratio else 0 end) '10月份' ,
sum(case Moth when 11 then completeratio else 0 end) '11月份',
sum(case Moth when 12 then completeratio else 0 end) '12月份'
from YearPlan where Years='2000'
[解决办法]
最终答案，把你的完成率改成decimal类型
select
sum(case Moth when 1 then plans else 0 end) '1月份',
sum(case Moth when 2 then plans else 0 end)'2月份',
sum(case Moth when 3 then plans else 0 end) '3月份',
sum(case Moth when 4 then plans else 0 end) '4月份' ,
sum(case Moth when 5 then plans else 0 end) '5月份' ,
sum(case Moth when 6 then plans else 0 end) '6月份' ,
sum(case Moth when 7 then plans else 0 end) '7月份' ,
sum(case Moth when 8 then plans else 0 end) '8月份',
sum(case Moth when 9 then plans else 0 end) '9月份',
sum(case Moth when 10 then plans else 0 end) '10月份' ,
sum(case Moth when 11 then plans else 0 end) '11月份',
sum(case Moth when 12 then plans else 0 end) '12月份' ,
sum(plans) '合计'
from YearPlan where Years='2000'
union
select
sum(case Moth when 1 then complete else 0 end) '1月份',
sum(case Moth when 2 then complete else 0 end)'2月份',
sum(case Moth when 3 then complete else 0 end) '3月份',
sum(case Moth when 4 then complete else 0 end) '4月份' ,
sum(case Moth when 5 then complete else 0 end) '5月份' ,
sum(case Moth when 6 then complete else 0 end) '6月份' ,
sum(case Moth when 7 then complete else 0 end) '7月份' ,
sum(case Moth when 8 then complete else 0 end) '8月份',
sum(case Moth when 9 then complete else 0 end) '9月份',
sum(case Moth when 10 then complete else 0 end) '10月份' ,
sum(case Moth when 11 then complete else 0 end) '11月份',
sum(case Moth when 12 then complete else 0 end) '12月份' ,
sum(complete) '合计'
from YearPlan where Years='2000'
union
select
sum(case Moth when 1 then completeratio else 0 end) '1月份',
sum(case Moth when 2 then completeratio else 0 end)'2月份',
sum(case Moth when 3 then completeratio else 0 end) '3月份',
sum(case Moth when 4 then completeratio else 0 end) '4月份' ,
sum(case Moth when 5 then completeratio else 0 end) '5月份' ,
sum(case Moth when 6 then completeratio else 0 end) '6月份' ,
sum(case Moth when 7 then completeratio else 0 end) '7月份' ,
sum(case Moth when 8 then completeratio else 0 end) '8月份',
sum(case Moth when 9 then completeratio else 0 end) '9月份',
sum(case Moth when 10 then completeratio else 0 end) '10月份' ,
sum(case Moth when 11 then completeratio else 0 end) '11月份',
sum(case Moth when 12 then completeratio else 0 end) '12月份' ,
avg(completeratio) '完成率'
from YearPlan where Years='2000'
[解决办法]
现在看到都头痛了啊..现在数据库都是2005了
[解决办法]
看的头晕，帮顶
[解决办法]