34,575
社区成员
发帖
与我相关
我的任务
分享
<script language="javascript" type="text/javascript">
function openrelogin()
{
location.href="../Relogin.aspx";
}
</script>
declare @table table (yf char(6),rq datetime,week char(6))
insert into @table
select '200601','2006-01-01',datename(w,convert(datetime,'2006-01-01'))
union all
select '200601','2006-01-02',datename(w,convert(datetime,'2006-01-02'))
union all
select '200601','2006-01-03',datename(w,convert(datetime,'2006-01-03'))
union all
select '200601','2006-01-04',datename(w,convert(datetime,'2006-01-04'))
union all
select '200601','2006-01-05',datename(w,convert(datetime,'2006-01-05'))
union all
select '200601','2006-01-06',datename(w,convert(datetime,'2006-01-06'))
union all
select '200601','2006-01-07',datename(w,convert(datetime,'2006-01-07'))
union all
select '200602','2006-02-01',datename(w,convert(datetime,'2006-02-01'))
union all
select '200602','2006-02-02',datename(w,convert(datetime,'2006-02-02'))
union all
select '200602','2006-02-03',datename(w,convert(datetime,'2006-02-03'))
union all
select '200602','2006-02-04',datename(w,convert(datetime,'2006-02-04'))
union all
select '200602','2006-02-05',datename(w,convert(datetime,'2006-02-05'))
union all
select '200602','2006-02-06',datename(w,convert(datetime,'2006-02-06'))
union all
select '200602','2006-02-07',datename(w,convert(datetime,'2006-02-07'))
----
declare @table2 table (xq char(10),rq datetime,ajnum int,week char(6))
insert into @table2
select '北区' ,'2006-01-01',10,datename(w,convert(datetime,'2006-01-01'))
union all
select '北区' ,'2006-01-02',5,datename(w,convert(datetime,'2006-01-02'))
union all
select '北区' ,'2006-01-03',9,datename(w,convert(datetime,'2006-01-03'))
union all
select '北区' ,'2006-01-04',8,datename(w,convert(datetime,'2006-01-04'))
union all
select '北区' ,'2006-01-05',1,datename(w,convert(datetime,'2006-01-05'))
union all
select '北区' ,'2006-01-06',3,datename(w,convert(datetime,'2006-01-06'))
union all
select '北区' ,'2006-01-07',8,datename(w,convert(datetime,'2006-01-07'))
union all
select '南区' ,'2006-02-01',7,datename(w,convert(datetime,'2006-02-01'))
union all
select '南区' ,'2006-02-02',4,datename(w,convert(datetime,'2006-02-02'))
union all
select '南区' ,'2006-02-03',10,datename(w,convert(datetime,'2006-02-03'))
union all
select '南区' ,'2006-02-04',8,datename(w,convert(datetime,'2006-02-04'))
union all
select '南区' ,'2006-02-05',2,datename(w,convert(datetime,'2006-02-05'))
union all
select '南区' ,'2006-02-06',8,datename(w,convert(datetime,'2006-02-06'))
union all
select '南区' ,'2006-02-07',6,datename(w,convert(datetime,'2006-02-07'))
declare @table3 table (yf char(6),xq varchar(20),week char(6),ajnum int)
insert into @table3
select a.yf,
xq,
a.week,
ajnum
from @table a ,@table2 b where convert(char,a.rq,112)=convert(char,b.rq,112)
----
select yf 月份,xq 小区,
sum(case week when '星期一' then ajnum else 0 end) 周一,
sum(case week when '星期二' then ajnum else 0 end) 周二,
sum(case week when '星期三' then ajnum else 0 end) 周三,
sum(case week when '星期四' then ajnum else 0 end) 周四,
sum(case week when '星期五' then ajnum else 0 end) 周五,
sum(case week when '星期六' then ajnum else 0 end) 周六,
sum(case week when '星期日' then ajnum else 0 end) 周日
from @table3
group by xq,yf
/*
200601 北区 5 9 8 1 3 8 10
200602 南区 8 6 7 4 10 8 2
*/
declare @s nvarchar(4000)
set @s='select 小区名'
select
@s+quotename(a.[week])+'=sum(case when 日期='+quotename(a.日期,'''')+' then ajnum else 0 end)'
from
tbl_rq a
join
TBL_AJ b on a.日期=b.日期
where
a.月份='200601'
group by a.日期,a.[week]
set @s=@s+' from TBL_AJ where left(日期,6)=''200601'' group by 小区名'
print @s--显示语句
exec(@s)
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case rq when '''+rq+''' then ajnum else 0 end) as ['+[week]+']'
from tbl_rq
where yf='200601' --查其他月改这里
order by rq
exec('select xq'+@sql+' from TBL_AJ where rq like ''200601%'' group by xq') --查其他月这里也要改
--掉了个时间
create proc wsp
@year int
@month int
as
declare @sql varchar(8000)
set @sql='select xq'
select @sql=@sql+',['+ replace(week,'星期','周') +']=max(case a.week when '''+ week +''' then
ajnum else 0 end)' from tbl_rq where left(yf,4)=@year and cast(right(yf,2) as int)=@month
order by rq
set @sql=@sql+' from tbl_rq a,TBL_AJ b where a.rq=b.rq and
left(a.yf,4)='+ltrim(@year)+' and cast(right(a.yf,2) as int)='+ltrim(@month)+' group by xq'
exec(@sql)
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case rq when '''+rq+''' then ajnum else 0 end) as ['+[week]+']
from tbl_rq
where yf='200601' --查其他月改这里
order by rq
exec('select xq'+@sql+' from TBL_AJ where rq like ''200601%'' group by xq') --查其他月这里也要改
create proc wsp
@year int
@month int
as
declare @sql varchar(8000)
set @sql='select xq'
select @sql=@sql+',['+ replace(week,'星期','周') +']=max(case a.week when '''+ week +''' then
ajnum else 0 end)' from tbl_rq where left(yf,4)=@year and cast(right(yf,2) as int)=@month
order by rq
set @sql=@sql+' from tbl_rq a,TBL_AJ b where a.rq=b.rq group by xq'
exec(@sql)
--调用:
exec wsp 2006,1
--纯属手敲。不排除有误```
/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
drop table tb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1