http://topic.csdn.net/u/20100611/21/6d8bd5bf-1de8-4d08-a8e0-542f46b45ff6.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('1','李四','吃饭','试制2','2010-7-8')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','赵六','追土','竞标5','2010-10-31')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻四','剪枝','竞标6','2010-8-1')
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')
应该出现的结果是
研制阶段 试制 调试 竞标3
计划阶段 2010-7-7 2010-6-18 2010-7-9
当测试数据发生改动后,上贴得到的结果出现问题
--测试数据
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('1','李四','吃饭','试制2','2010-7-8')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','赵六','追土','竞标5','2010-10-31')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻四','剪枝','竞标6','2010-8-1')
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')
应该出现的结果是
研制阶段 试制 调试 竞标3
计划阶段 2010-7-7 2010-6-18 2010-7-9
解决方案 »
- sqlserver2005数据恢复
- 帮我改一个存储过程
- 如何將SQL語句的查詢結果導出到xml文件中?
- 数据库中使用多个文件组的好处:
- sqlserver中,一个datetime的字段,如何查询出2004年12月份的数据?
- 存储过程是什么时候编译的,优化的,如果修改了表的一个主键或索引会影响存储过程的优化效果吗.
- where 条件筛选
- SQL 必须弄懂的东西
- ((@)(@))ODBC-DBASE 5.0 数据库,单用户,在删除某行记录时,发生警告"由于表被占用,无法锁定数据库",查询代码在此之前并无其他连接(op
- 请问怎么取检索出来的记录的最后一行?
- 那位大虾有看过这类数据加密办法的?
- 关于UDF的问题~~
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('1','李四','吃饭','试制2','2010-7-8')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','王二','浇水','调试','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','赵六','追土','竞标5','2010-10-31')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('2','麻子','松土','竞标1','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,TaskEvent,taskdate) values('3','麻四','剪枝','竞标6','2010-8-1')
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 cast(taskdate as datetime) = (select min(cast(taskdate as datetime)) 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 cast(taskdate as datetime) = (select min(cast(taskdate as datetime)) 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 cast(taskdate as datetime) = (select min(cast(taskdate as datetime)) 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 cast(taskdate as datetime) = (select min(cast(taskdate as datetime)) 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(所影响的行数为 2 行)
*/drop table tasks