问题是这样的,我创建了一个表CostTable,专门用来放需要缴费的项目,表格式如下:
prj amount year
X1费 10000 1
X2费 5000 1
X3费 8000 1
X4费 35000 2
X5费 10000 2
X6费 10000 3
现在需要实现一张临时表,当‘year == 1’时,表的结构为
X1费 X2费 X3费
10000 5000 8000
当‘year == 2’时,表的结构为
X4费 X5费
35000 10000
刚学写程序,只会写点SQL语句,对这种问题束手无策,谢谢大家了哦~~~
prj amount year
X1费 10000 1
X2费 5000 1
X3费 8000 1
X4费 35000 2
X5费 10000 2
X6费 10000 3
现在需要实现一张临时表,当‘year == 1’时,表的结构为
X1费 X2费 X3费
10000 5000 8000
当‘year == 2’时,表的结构为
X4费 X5费
35000 10000
刚学写程序,只会写点SQL语句,对这种问题束手无策,谢谢大家了哦~~~
解决方案 »
- 请问一段简单的脚本错误……
- SQL2000 企业管理器中的表,存储过程,用户自定义函数能否按照最后修改日期排序
- 请教一个SQL查询语句
- 如何调用存储过程已经被调用过的更新过的数据
- 这个问题怎么解决:如何往刚用Sql语句创建的新数据库里添加存储过程
- 求 分组统计 sql 语句!!!
- 请问怎样用SQL语句把SQL数据库中的表导出到EXCEL,TXT,ACESS中?
- 关于网页中使用数据库的问题
- 我从别的服务器上insert一个表过来只要4分钟, 现在我update却要30分钟,帮我分析分析。
- 我也遇到了这个问题。
- 比如说我用上面的SQL代码,创建一个表AccountDestination ,其中UpdateID代表的是'更新的ID号',CustomerID 代表的是'客户ID',CompanyName指
- 表中字段的说明问题
insert aggg select
'X1费', 10000, 1
union all select 'X2费', 5000 ,1
union all select 'X3费', 8000 ,1
union all select 'X4费', 35000, 2
union all select 'X5费', 10000, 2
union all select 'X6费', 10000 ,3create proc pt
@y int
as
if object_id('表') is not null
drop table 表
declare @s varchar(8000)
set @s=''
select @s=@s+'sum(case when [prj]='''+[prj]+''' then amount else 0 end ) '+[prj]+',' from aggg where [year]=@y
select @s='select '+left(@s,len(@s)-1) +' into 表 from aggg where [year]=' +ltrim(@y)
exec(@s)
select * from 表
gopt 1
declare @year int ---此参数为你要查询的年
set @sql=''
select @sql=@sql+' select (case when prj='''+ prj+''' when amount end) ['''+ prj+'''] into from CostTable from CostTable' from CostTable where [year]=@year
select @sql=@sql+' select * from #Tmp '
exec(@sql)
to chuifengde() :
CostTable 表里的数据不是固定的,随时会增会减的。
insert CostTable select
'X1费', 10000, 1
union all select 'X2费', 5000 ,1
union all select 'X3费', 8000 ,1
union all select 'X4费', 35000, 2
union all select 'X5费', 10000, 2
union all select 'X6费', 10000 ,3create proc pro_test(@year int) --此参数为你要查询的年
as
begin
declare @sql varchar(8000)
set @sql='select'
select @sql=@sql+' sum(case when prj='''+ prj+''' then amount end) ['+ prj+'],' from CostTable where [year]=@year
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' into #Tmp from CostTable select * from #Tmp '
--print @sql
exec(@sql)
end
表名加个变量 declare @tablename varchar(100)
set @tablename='#mingzi'
create table @tablename
字段你自己定。
insert aggg select
'X1费', 10000, 1
union all select 'X2费', 5000 ,1
union all select 'X3费', 8000 ,1
union all select 'X4费', 35000, 2
union all select 'X5费', 10000, 2
union all select 'X6费', 10000 ,3
select a.year,
'x1'=sum(isnull(case when a.year=1 and a.prj='X1费'then a.amount end,0))
,'x2'=sum(isnull(case when a.year=1 and a.prj='X2费'then a.amount end,0))
,'x3'=sum(isnull(case when a.year=1 and a.prj='X3费'then a.amount end,0))
,'x4'=sum(isnull(case when a.year=2 and a.prj='X4费'then a.amount end,0))
,'x5'=sum(isnull(case when a.year=2 and a.prj='X5费'then a.amount end,0))
,'x6'=sum(isnull(case when a.year=3 and a.prj='X6费'then a.amount end,0))
from aggg a
group by year
insert #t select
'X1费', 10000, 1
union all select 'X2费', 5000 ,1
union all select 'X3费', 8000 ,1
union all select 'X4费', 35000, 2
union all select 'X5费', 10000, 2
union all select 'X6费', 10000 ,3
declare @r varchar(4000)
set @r=''
select @r=@r+'['+prj+'] =sum(case prj when '''+prj+''' then amount else 0 end),'
from #t where year=1 group by prj --year是这里的条件
select @r=left(@r,len(@r)-1)
set @r='select '+@r+' from #t '
exec(@r)
X1费 X2费 X3费
---------------------------------------- ---------------------------------------- ----------------------------------------
10000 5000 8000
select
[X1费]=sum(case prj when 'X1费' then amount else 0 end),
[X2费]=sum(case prj when 'X2费' then amount else 0 end),
[X3费]=sum(case prj when 'X3费' then amount else 0 end),
[X4费]=sum(case prj when 'X4费' then amount else 0 end),
[X5费]=sum(case prj when 'X5费' then amount else 0 end),
[X6费]=sum(case prj when 'X6费' then amount else 0 end)
from #t