27,579
社区成员
发帖
与我相关
我的任务
分享关于数据库SQL语句中的一个问题请教
邹老大现在有这样一个表，是用来记录工作人员的效绩数据的，表结构如下：
create table a(man varchar(20), --员工姓名
iyaer varchar(4), --效绩年份
PJan int, --p：开头的是计划的，F：开头的是实际的,后面的代表的是月份，因为全写上去看着太晕，所以写到7月份的，不好意思
FJan int,
PFeb int,
FFeb int,
PMar int,
FMar int,
PApr int,
FApr int,
PMay int,
FMay int,
PJun int,
FJun int,
PJul int,
FJul int
)
insert into a
select 'abc','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15
union
select 'abc','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15
union
select 'aaa','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15
union
select 'aaa','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15
在输入时间段后，得到相关的结果，如输入时间段为：2009-01至2010-06
现在要得到的结果如下：
在输入时间段后，得到相关的结果，如输入时间段为：2009-01至2010-06
name 2009年1月份计划 2009年1月份实际 2009年2月份计划......2010年6月份实际
abc 12 12 21 98
aaa 12 12 21 98
有许多员工，这里只举两位员工的例子，请老大帮忙，希望我写得还算明白
create table a(man varchar(20), --员工姓名
iyaer varchar(4), --效绩年份
PJan int, --p：开头的是计划的，F：开头的是实际的,后面的代表的是月份，因为全写上去看着太晕，所以写到7月份的，不好意思
FJan int,
PFeb int,
FFeb int,
PMar int,
FMar int,
PApr int,
FApr int,
PMay int,
FMay int,
PJun int,
FJun int,
PJul int,
FJul int
)
insert into a
select 'abc','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15
union
select 'abc','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15
union
select 'aaa','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15
union
select 'aaa','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15
在输入时间段后，得到相关的结果，如输入时间段为：2009-01至2010-06
现在要得到的结果如下：
在输入时间段后，得到相关的结果，如输入时间段为：2009-01至2010-06
name 2009年1月份计划 2009年1月份实际 2009年2月份计划......2010年6月份实际
abc 12 12 21 98
aaa 12 12 21 98
有许多员工，这里只举两位员工的例子，请老大帮忙，希望我写得还算明白
...全文
请发表友善的回复…
发表回复
Godsaidlwq 2009-02-17
- 打赏
- 举报
多谢子陌老大提醒，我已经搞定啦！给分ING。。。。
dawugui 2009-02-17
- 打赏
- 举报
不是结果看起来别扭,是表设计得太差了.
'abc','2009','P' , 1 , 12
'abc','2009','F' , 1 , 12
...
这样就好做多了.
'abc','2009','P' , 1 , 12
'abc','2009','F' , 1 , 12
...
这样就好做多了.
子陌红尘 2009-02-17
- 打赏
- 举报
输出的结果，看起来别扭
/*
man 2009年2月份计划 2009年2月份实际 2009年3月份计划 2009年3月份实际 2009年4月份计划 2009年4月份实际 2009年5月份计划 2009年5月份实际 2009年6月份计划 2009年6月份实际 2009年7月份计划 2009年7月份实际 2009年8月份计划 2009年8月份实际 2009年9月份计划 2009年9月份实际 2009年10月份计划 2009年10月份实际 2009年11月份计划 2009年11月份实际 2009年12月份计划 2009年12月份实际 2010年1月份计划 2010年1月份实际 2010年2月份计划 2010年2月份实际 2010年3月份计划 2010年3月份实际
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
aaa 21 13 14 15 16 16 20 10 90 20 98 15 14 15 16 16 20 10 90 20 98 15 12 12 21 13 14 15
abc 21 13 14 15 16 16 20 10 90 20 98 15 14 15 16 16 20 10 90 20 98 15 12 12 21 13 14 15
8?
/*
man 2009年2月份计划 2009年2月份实际 2009年3月份计划 2009年3月份实际 2009年4月份计划 2009年4月份实际 2009年5月份计划 2009年5月份实际 2009年6月份计划 2009年6月份实际 2009年7月份计划 2009年7月份实际 2009年8月份计划 2009年8月份实际 2009年9月份计划 2009年9月份实际 2009年10月份计划 2009年10月份实际 2009年11月份计划 2009年11月份实际 2009年12月份计划 2009年12月份实际 2010年1月份计划 2010年1月份实际 2010年2月份计划 2010年2月份实际 2010年3月份计划 2010年3月份实际
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
aaa 21 13 14 15 16 16 20 10 90 20 98 15 14 15 16 16 20 10 90 20 98 15 12 12 21 13 14 15
abc 21 13 14 15 16 16 20 10 90 20 98 15 14 15 16 16 20 10 90 20 98 15 12 12 21 13 14 15
8?
子陌红尘 2009-02-17
- 打赏
- 举报
create table Test(man varchar(20),iyaer varchar(4),
PJan int,FJan int, PFeb int, FFeb int, PMar int, FMar int, PApr int, FApr int,PMay int, FMay int, PJun int, FJun int,
PJul int,FJul int, PAug int, FAug int, PSep int, FSep int, POct int, FOct int,PNov int, FNov int, PDec int, FDec int)
insert into Test
select 'abc','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15,14,15,16,16,20,10,90,20,98,15
union
select 'abc','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15,14,15,16,16,20,10,90,20,98,15
union
select 'aaa','2009',12,12,21,13,14,15,16,16,20,10,90,20,98,15,14,15,16,16,20,10,90,20,98,15
union
select 'aaa','2010',12,12,21,13,14,15,16,16,20,10,90,98,98,15,14,15,16,16,20,10,90,20,98,15
go
create procedure sp_test(@SD varchar(7),@ED varchar(7))
as
begin
declare @sql varchar(8000),@SYear int,@SMonth int,@EYear int,@EMonth int
select
@sql = '',
@SYear = left(@SD,4),
@SMonth = right(@SD,2),
@EYear = left(@ED,4),
@EMonth = right(@ED,2)
while (@SYear<@EYear) or (@SYear=@EYear and @SMonth<=@EMonth)
begin
select @sql=@sql+',['+rtrim(@SYear)+'年'+rtrim(@SMonth)+'月份计划]=max(case when iyaer='+rtrim(@SYear)+' then '+
(case @SMonth
when 1 then 'PJan'
when 2 then 'PFeb'
when 3 then 'PMar'
when 4 then 'PApr'
when 5 then 'PMay'
when 6 then 'PJun'
when 7 then 'PJul'
when 8 then 'PAug'
when 9 then 'PSep'
when 10 then 'POct'
when 11 then 'PNov'
when 12 then 'PDec'
end)
+' end)'
+',['+rtrim(@SYear)+'年'+rtrim(@SMonth)+'月份实际]=max(case when iyaer='+rtrim(@SYear)+' then '+
(case @SMonth
when 1 then 'FJan'
when 2 then 'FFeb'
when 3 then 'FMar'
when 4 then 'FApr'
when 5 then 'FMay'
when 6 then 'FJun'
when 7 then 'FJul'
when 8 then 'FAug'
when 9 then 'FSep'
when 10 then 'FOct'
when 11 then 'FNov'
when 12 then 'FDec'
end)
+' end)'
set @SMonth=(case when @SMonth=12 then 1 else @SMonth+1 end)
set @SYear =@SYear+(case when @SMonth= 1 then 1 else 0 end)
end
set @sql='select man'+@sql+' from Test group by man'
--print @sql
exec(@sql)
end
go
exec sp_test '2009-02','2010-03'
go
drop table test
drop procedure sp_test
go
子陌红尘 2009-02-17
- 打赏
- 举报
[Quote=引用 4 楼 Godsaidlwq 的回复:]
我也想过用动态语句，但因为有许多员工，所以，8000的长度不够用！
[/Quote]
动态SQL语句的长度跟员工的多少没关系，只与选取的时间跨度相关。
我也想过用动态语句，但因为有许多员工，所以，8000的长度不够用！
[/Quote]
动态SQL语句的长度跟员工的多少没关系，只与选取的时间跨度相关。
-狙击手- 2009-02-17
- 打赏
- 举报
[Quote=引用 4 楼 Godsaidlwq 的回复:]
我也想过用动态语句，但因为有许多员工，所以，8000的长度不够用！
[/Quote]
以员工分组，和员工多与少无关了
我也想过用动态语句，但因为有许多员工，所以，8000的长度不够用！
[/Quote]
以员工分组，和员工多与少无关了
dawugui 2009-02-17
- 打赏
- 举报
--你的表设计的实在是太不科学了,尝试这么写吧?自己补充完.
select man,
[2009年1月份计划] = sum(case iyaer when 2009 then PJan else 0 end),
[2009年1月份实际] = sum(case iyaer when 2009 then FJan else 0 end),
[2009年2月份计划] = sum(case iyaer when 2009 then PFeb else 0 end),
[2009年2月份实际] = sum(case iyaer when 2009 then FFeb else 0 end),
...
from a
group manGodsaidlwq 2009-02-17
- 打赏
- 举报
我也想过用动态语句，但因为有许多员工，所以，8000的长度不够用！
Godsaidlwq 2009-02-17
- 打赏
- 举报
是的
子陌红尘 2009-02-17
- 打赏
- 举报
楼主的意思是，如何把同一个用户的多条数据跟动态的年月结合起来，合并为一条记录？
dawugui 2009-02-17
- 打赏
- 举报
又是行列转换?参考:
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
