****************************************表结构****************************************
姓名 周1计划 周2计划 周3计划 周4计划 周5计划 周6计划 周7计划 其他计划 计划周期开始（周1） 计划周期截至（周7）
a 写代码1 写代码2 写代码3 写代码4 写代码5 写代码6 写代码7 写代码8 2008-02-18 2008-02-24
b 写需求1 写需求2 写需求3 写需求4 写需求5 写需求6 写需求7 无 2008-02-18 2008-02-24
c 聊天1 聊天2 聊天3 聊天4 聊天5 聊天6 聊天7 无 2008-02-11 2008-02-17
****************************************要实现功能****************************************
如果我选择时间段为2008-02-13到2008-02-20要实现如下显示的功能
姓名 2008-02-13计划 2008-02-14计划 ... 2008-02-19计划 2008-02-20计划
a 无 无 ... 写代码2 写代码3
b 无 无 ... 写需求2 写需求3
c 聊天3 聊天4 ... 无 无
姓名 周1计划 周2计划 周3计划 周4计划 周5计划 周6计划 周7计划 其他计划 计划周期开始（周1） 计划周期截至（周7）
a 写代码1 写代码2 写代码3 写代码4 写代码5 写代码6 写代码7 写代码8 2008-02-18 2008-02-24
b 写需求1 写需求2 写需求3 写需求4 写需求5 写需求6 写需求7 无 2008-02-18 2008-02-24
c 聊天1 聊天2 聊天3 聊天4 聊天5 聊天6 聊天7 无 2008-02-11 2008-02-17
****************************************要实现功能****************************************
如果我选择时间段为2008-02-13到2008-02-20要实现如下显示的功能
姓名 2008-02-13计划 2008-02-14计划 ... 2008-02-19计划 2008-02-20计划
a 无 无 ... 写代码2 写代码3
b 无 无 ... 写需求2 写需求3
c 聊天3 聊天4 ... 无 无
姓名 周1计划 周2计划 周3计划 周4计划 周5计划 周6计划 周7计划 其他计划 周期开始（周1） 周期截至（周7）
a 写代码1 写代码2 写代码3 写代码4 写代码5 写代码6 写代码7 写代码8 2008-02-18 2008-02-24
b 写需求1 写需求2 写需求3 写需求4 写需求5 写需求6 写需求7 无 2008-02-18 2008-02-24
c 聊天1 聊天2 聊天3 聊天4 聊天5 聊天6 聊天7 无 2008-02-11 2008-02-17
****************************************要实现功能****************************************
如果我选择时间段为2008-02-13到2008-02-20要实现如下显示的功能
姓名 2008-02-13计划 2008-02-14计划 ... 2008-02-19计划 2008-02-20计划
a 无 无 ... 写代码2 写代码3
b 无 无 ... 写需求2 写需求3
c 聊天3 聊天4 ... 无 无注：不好意思，为了让大家看的更明白，多次提交！
create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),
starttime datetime, endtime datetime)insert into T select 'A','寫代碼1','寫代碼2','寫代碼3','寫代碼4','寫代碼5','寫代碼6','寫代碼7','2008-2-18','2008-2-24'
insert into T select 'B','寫需求1','寫需求2','寫需求3','寫需求4','寫需求5','寫需求6','寫需求7','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','2008-2-11','2008-2-17'GO
--設定時間起始
declare @start datetime,@end datetime
set @start='2008-02-13'
set @end='2008-02-20'select * into #
from
(
select name,a as [work], starttime
from T
union all
select name,b as [work], dateadd(day,1,starttime)
from T
union all
select name,c as [work], dateadd(day,2,starttime)
from T
union all
select name,d as [work], dateadd(day,3,starttime)
from T
union all
select name,e as [work], dateadd(day,4,starttime)
from T
union all
select name,f as [work], dateadd(day,5,starttime)
from T
union all
select name,g as [work], dateadd(day,6,starttime)
from T
) A
where starttime>=@start
and starttime<=@enddeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when starttime='''+convert(char(10),starttime,120)+''' then [work] else '''' end ) as ['
+convert(char(10),starttime,120)+'計劃]'
from #
group by starttimeselect @sql='select [name]'+@sql+' from # group by [name]'exec(@sql)
/*
name 2008-02-13計劃 2008-02-14計劃 2008-02-15計劃 2008-02-16計劃 2008-02-17計劃 2008-02-18計劃 2008-02-19計劃 2008-02-20計劃
-------------------------------------------------------------------------------------------------------
A 寫代碼1 寫代碼2 寫代碼3
B 寫需求1 寫需求2 寫需求3
C 聊天3 聊天4 聊天5 聊天6 聊天7 */drop table T,#
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),
starttime datetime, endtime datetime)insert into T select 'A','寫代碼1','寫代碼2','寫代碼3','寫代碼4','寫代碼5','寫代碼6','寫代碼7','2008-2-18','2008-2-24'
insert into T select 'B','寫需求1','寫需求2','寫需求3','寫需求4','寫需求5','寫需求6','寫需求7','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','2008-2-11','2008-2-17'--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select name'
select * into #
from (select name,a as p,dateadd(day,0,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,b as p,dateadd(day,1,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,c as p,dateadd(day,2,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,d as p,dateadd(day,3,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,e as p,dateadd(day,4,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,f as p,dateadd(day,5,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,g as p,dateadd(day,6,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
) a where dt between @start and @end order by dt
select @s = @s+',max(case when dt ='''+convert(char(10),dt,120)+''' then p else '''' end) as ['+convert(char(10),dt,120)+'计划]' from #
select @s = @s+' from # group by name'
--print @s
exec(@s)
drop table #,t
/*
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 2008-02-18计划 2008-02-18计划 2008-02-19计划 2008-02-19计划 2008-02-20计划 2008-02-20计划
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A 寫代碼1 寫代碼1 寫代碼2 寫代碼2 寫代碼3 寫代碼3
B 寫需求1 寫需求1 寫需求2 寫需求2 寫需求3 寫需求3
C 聊天3 聊天4 聊天5 聊天6 聊天7
*/
日期出现重复！
2008-02-18计划 2008-02-18计划 2008-02-19计划 2008-02-19计划 2008-02-20计划 2008-02-20计划
create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-24','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO注意：
1.在“周7计划”后面还有一个“其他计划”的字段，“其他计划”意为上司额外安排的任务
2.结果显示的全部字段应为
2008-02-13计划； 2008-02-14计划； 2008-02-15计划； 2008-02-16计划； 2008-02-17计划；本周计划外任务； 2008-02-18计划 ； 2008-02-19计划 ； 2008-02-20计划 ；本周计划外任务；
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 本周计划外任务 2008-02-18计划 2008-02-19计划 2008-02-20计划 本周计划外任务
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A 无 无 无 无 无 无 写代码1 写代码2 写代码3 写代码8
B 无 无 无 无 无 无 写需求1 写需求2 写需求3 无
C 聊天3 聊天4 聊天5 聊天6 聊天7 无 无 无 无 无以上为想要的结果
create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO
--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select name'
select * into #
from (select name,a as p,dateadd(day,0,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,b as p,dateadd(day,1,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,c as p,dateadd(day,2,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,d as p,dateadd(day,3,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,e as p,dateadd(day,4,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,f as p,dateadd(day,5,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,g as p,dateadd(day,6,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
) a where dt between @start and @end order by dt select @s = @s+',max(case when convert(char(10),dt,120) ='''+convert(char(10),dt,120)+''' then p else '''' end) as ['+convert(char(10),dt,120)+'计划]' from # group by dt
select @s = @s+' from # group by name'
exec(@s)
drop table #,t
/*
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 2008-02-18计划 2008-02-19计划 2008-02-20计划
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A 写代码1 写代码2 写代码3
B 写需求1 写需求2 写需求3
C 聊天3 聊天4 聊天5 聊天6 聊天7
*/
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO
--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select name'
select * into #
from (select name,a as p,dateadd(day,0,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,b as p,dateadd(day,1,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,c as p,dateadd(day,2,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,d as p,dateadd(day,3,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,e as p,dateadd(day,4,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,f as p,dateadd(day,5,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,g as p,dateadd(day,6,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
) a where dt between @start and @end order by dt select @s = @s+',max(case when convert(char(10),dt,120) ='''+convert(char(10),dt,120)+''' then p else ''无'' end) as ['+convert(char(10),dt,120)+'计划]' from # group by dt
select @s = @s+' from # group by name'
exec(@s)
drop table #,t
/*
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 2008-02-18计划 2008-02-19计划 2008-02-20计划
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A 无 无 无 无 无 写代码1 写代码2 写代码3
B 无 无 无 无 无 写需求1 写需求2 写需求3
C 无 无 无 无 无 无 无 无
*/
create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO
--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select name'
select * into #
from (select name,a as p,dateadd(day,0,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,b as p,dateadd(day,1,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,c as p,dateadd(day,2,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,d as p,dateadd(day,3,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,e as p,dateadd(day,4,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,f as p,dateadd(day,5,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select name,g as p,dateadd(day,6,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
) a where dt between @start and @end order by dt select @s = @s+',max(case when convert(char(10),dt,120) ='''+convert(char(10),dt,120)+''' then ltrim(p) else '' 无'' end) as ['+convert(char(10),dt,120)+'计划]' from # group by dt
select @s = @s+' from # group by name'
exec(@s)
drop table #,t
/*
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 2008-02-18计划 2008-02-19计划 2008-02-20计划
---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
A 无 无 无 无 无 写代码1 写代码2 写代码3
B 无 无 无 无 无 写需求1 写需求2 写需求3
C 聊天3 聊天4 聊天5 聊天6 聊天7 无 无 无
*/
人家（jianghongtao）将洪涛已经把东西给你做出来了，你还要什么？没有那个计划外任务吗？我给你加上create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','无','2008-2-11','2008-2-17'
GO
--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select t.name'
select * into #
from (select t.name,a as p,dateadd(day,0,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,b as p,dateadd(day,1,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,c as p,dateadd(day,2,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,d as p,dateadd(day,3,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,e as p,dateadd(day,4,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,f as p,dateadd(day,5,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
union select t.name,g as p,dateadd(day,6,starttime) dt from t where @start between starttime and endtime or @end between starttime and endtime
) a where dt between @start and @end order by dt select @s = @s+',max(case when convert(char(10),dt,120) ='''+convert(char(10),dt,120)+''' then ltrim(p) else '' 无'' end) as ['+convert(char(10),dt,120)+'计划]' from # group by dt
select @s = @s+' ,T.h as ''计划外任务'' from #,T where #.name=T.name group by t.name,T.h order by t.name'
exec(@s)
drop table #,t
还是请高人吧
sorry
下面的我暂时没有发现问题，最近对自己的代码心里没底，总会在一些小的地方出现错误。
create table T(name varchar(10), a varchar(10),b varchar(10),
c varchar(10),d varchar(10),e varchar(10),f varchar(10), g varchar(10),h varchar(10),
starttime datetime, endtime datetime)
--插入测试数据
insert into T select 'A','写代码1','写代码2','写代码3','写代码4','写代码5','写代码6','写代码7','写代码8','2008-2-18','2008-2-24'
insert into T select 'B','写需求1','写需求2','写需求3','写需求4','写需求5','写需求6','写需求7','无','2008-2-18','2008-2-24'
insert into T select 'C','聊天1','聊天2','聊天3','聊天4','聊天5','聊天6','聊天7','聊天8','2008-2-11','2008-2-17'
GO
--設定時間起始
declare @start datetime,@end datetime,@s varchar(8000)
select @start='2008-02-13', @end='2008-02-20',@s = 'select name'
create table #(name varchar(10),p varchar(10),dtc varchar(100))
insert # select name,a,convert(char(10),dateadd(day,0,starttime),120)+'计划' from t where dateadd(day,0,starttime) between @start and @end
insert # select name,b,convert(char(10),dateadd(day,1,starttime),120)+'计划' from t where dateadd(day,1,starttime) between @start and @end
insert # select name,c,convert(char(10),dateadd(day,2,starttime),120)+'计划' from t where dateadd(day,2,starttime) between @start and @end
insert # select name,d,convert(char(10),dateadd(day,3,starttime),120)+'计划' from t where dateadd(day,3,starttime) between @start and @end
insert # select name,e,convert(char(10),dateadd(day,4,starttime),120)+'计划' from t where dateadd(day,4,starttime) between @start and @end
insert # select name,f,convert(char(10),dateadd(day,5,starttime),120)+'计划' from t where dateadd(day,5,starttime) between @start and @end
insert # select name,g,convert(char(10),dateadd(day,6,starttime),120)+'计划' from t where dateadd(day,6,starttime) between @start and @end
insert # select name,h,convert(char(10),endtime,120)+'计划A' from t where @start between starttime and endtime or @end between starttime and endtimeselect @s = @s+',max(case when dtc ='''+dtc+''' then ltrim(p) else '' 无'' end) as ['+case right(dtc,1) when 'A' then '本周其它计划' else dtc end +']' from # group by dtc
select @s = @s+' from # group by name'
exec(@s)
drop table #,t
/*
name 2008-02-13计划 2008-02-14计划 2008-02-15计划 2008-02-16计划 2008-02-17计划 本周其它计划 2008-02-18计划 2008-02-19计划 2008-02-20计划 本周其它计划
---------- ------------ ------------ ------------ ------------ ------------ ---------- ------------ ------------ ------------ ----------
A 无 无 无 无 无 无 写代码1 写代码2 写代码3 写代码8
B 无 无 无 无 无 无 写需求1 写需求2 写需求3 无
C 聊天3 聊天4 聊天5 聊天6 聊天7 聊天8 无 无 无 无
*/