htl258在不，请教个跟上次相似的问题

[水哥阿乐]

上次贴子http://topic.csdn.net/u/20100609/23/5503c40d-3c28-4cff-b9ee-a0238bd93a09.html

--测试数据
CREATE TABLE Tasks
	(ID bigint IDENTITY(1,1),
	ProjectID nvarchar(255),
	PersonName nvarchar(255),
	CurrentWorking nvarchar(50),
    TaskEvent nvarchar(50),
    TaskDate nvarchar(50))
	
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','张三','挖地','设计','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','李四','挖土','试制','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','王一','采摘','竞标2','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻三','修剪','竞标3','2010-7-9')

加上两行，然后查出离当天时间最近(比当天时间大)的数据并将做的事情（TaskEvent)和符合的时间(taskEvent)分配到各对应的ProjectID中（ProjectID变成列)
效果如下（数据是动态的,研制阶段和计划节点是固定的）
和上次一样如果解决问题，我再补贴加分你

[永生天地]

有些复杂，呼叫tony

[-狙击手-]

嘿嘿，那我观望

[dawugui]

数据怎么来的?

例如研制阶段和计划节点怎么来的?

[水哥阿乐]

研制阶段和计划节点是另外加上去的是可以固定不变的数据

[nalnait]

[Quote=引用 2 楼 happyflystone 的回复:]
嘿嘿，那我观望
[/Quote]
好久不见了

[dawugui]

CREATE TABLE Tasks
    (ID bigint IDENTITY(1,1),
    ProjectID nvarchar(255),
    PersonName nvarchar(255),
    CurrentWorking nvarchar(50),
    TaskEvent nvarchar(50),
    TaskDate nvarchar(50))
    
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','张三','挖地','设计','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','李四','挖土','试制','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','王一','采摘','竞标2','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻三','修剪','竞标3','2010-7-9')

select expr1 = '研制阶段', 
  max(case projectid when 1 then TaskEvent end) [1],
  max(case projectid when 2 then TaskEvent end) [2],
  max(case projectid when 3 then TaskEvent end) [3]
from
(
  select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid) 
) m
union all
select expr1 = '计划阶段', 
  max(case projectid when 1 then convert(varchar(10) , taskdate, 120) end) [1],
  max(case projectid when 2 then convert(varchar(10) , taskdate, 120) end) [2],
  max(case projectid when 3 then convert(varchar(10) , taskdate, 120) end) [3]
from
(
  select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid) 
) m

/*
expr1    1                                                  2                                                  3                                                  
-------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
研制阶段     试制                                                 调试                                                 竞标3
计划阶段     2010-7-7                                           2010-6-18                                          2010-7-9

（所影响的行数为 2 行）
*/

drop table tasks

[水哥阿乐]

先谢了，我去试试

[dawugui]

CREATE TABLE Tasks
    (ID bigint IDENTITY(1,1),
    ProjectID nvarchar(255),
    PersonName nvarchar(255),
    CurrentWorking nvarchar(50),
    TaskEvent nvarchar(50),
    TaskDate nvarchar(50))
    
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','张三','挖地','设计','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','李四','挖土','试制','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','王一','采摘','竞标2','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻三','修剪','竞标3','2010-7-9')
go
--静态的
select expr1 = '研制阶段', 
  max(case projectid when 1 then TaskEvent else '' end) [1],
  max(case projectid when 2 then TaskEvent else '' end) [2],
  max(case projectid when 3 then TaskEvent else '' end) [3]
from
(
  select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid) 
) m
union all
select expr1 = '计划阶段', 
  max(case projectid when 1 then convert(varchar(10) , taskdate, 120) else '' end) [1],
  max(case projectid when 2 then convert(varchar(10) , taskdate, 120) else '' end) [2],
  max(case projectid when 3 then convert(varchar(10) , taskdate, 120) else '' end) [3]
from
(
  select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid) 
) m

/*
expr1    1                                                  2                                                  3                                                  
-------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
研制阶段     试制                                                 调试                                                 竞标3
计划阶段     2010-7-7                                           2010-6-18                                          2010-7-9

（所影响的行数为 2 行）
*/


--动态的，则如下：
declare @sql1 varchar(8000)
set @sql1 = 'select expr1 = ''研制阶段'' '
select @sql1 = @sql1 + ' , max(case projectid when ''' + cast(projectid as varchar) + ''' then TaskEvent  else '''' end) [' + cast(projectid as varchar) + ']'
from (select distinct projectid from tasks) as a
set @sql1 = @sql1 + ' from (select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid)) m '
declare @sql2 varchar(8000)
set @sql2 = 'select expr1 = ''计划阶段'' '
select @sql2 = @sql2 + ' , max(case projectid when ''' + cast(projectid as varchar) + ''' then convert(varchar(10) , taskdate, 120)  else '''' end) [' + cast(projectid as varchar) + ']'
from (select distinct projectid from tasks) as a
set @sql2 = @sql2 + ' from (select t.* from tasks t where taskdate > getdate() and taskdate = (select min(taskdate) from tasks where taskdate > getdate() and projectid = t.projectid)) m '

exec(@sql1 + ' union all ' + @sql2) 
/*
expr1    1                                                  2                                                  3                                                  
-------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
研制阶段     试制                                                 调试                                                 竞标3
计划阶段     2010-7-7                                           2010-6-18                                          2010-7-9
*/

drop table tasks

[-狙击手-]

--测试数据
CREATE TABLE Tasks
    (ID bigint IDENTITY(1,1),
    ProjectID nvarchar(255),
    PersonName nvarchar(255),
    CurrentWorking nvarchar(50),
    TaskEvent nvarchar(50),
    TaskDate nvarchar(50))
    
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','张三','挖地','设计','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('1','李四','挖土','试制','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','王一','采摘','竞标2','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻三','修剪','竞标3','2010-7-9')

go

select * into tmp
from tasks a
where not exists(select 1 from tasks where projectid = a.projectid and taskdate < a.taskdate and cast(taskdate as datetime) > getdate())
      and cast(taskdate as datetime) > getdate()

declare @s varchar(8000)

select @s = isnull(@s+',','')+ '['+ltrim(projectid)+']=max(case when projectid = '''+projectid+''' then taskevent else null end)'
from (select distinct projectid from tmp) a

exec('select [exp],'+@s+' from ( 
select projectid,''研制阶段'' as [exp],taskevent from tmp
union all
select projectid,''计划节点'' as [exp],TaskDate from tmp
) a group by [exp] order by 1 desc')

/*
exp      1                                                  2                                                  3                                                  
-------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
研制阶段     试制                                                 调试                                                 竞标3
计划节点     2010-7-7                                           2010-6-18                                          2010-7-9

*/

drop table Tasks,tmp

[-狙击手-]

太复杂了，无语。。。