# 求一个 sql2005语句！高手请进！问题解决再追加分数！

0

2012-09-01发  2012-09-01完成  2012-09-02发  2012-09-02完成

2012-09-03 我就不写了  求高手写个完整的sql语句！

1

GO

CREATE TABLE [dbo].[Users]

([UserName] [nvarchar](20) NULL,  [SDate] [date] NULL,

[GetTask] [int] NULL,  [FnishTask] [int] NULL ) ON [PRIMARY]

GO

VALUES (N'张三', CAST(0x1A360B00 AS Date), 20, 15)

VALUES (N'李四', CAST(0x1A360B00 AS Date), 20, 16)

VALUES (N'张三', CAST(0x1B360B00 AS Date), 25, 23)

VALUES (N'李四', CAST(0x1B360B00 AS Date), 25, 20)

VALUES (N'张三', CAST(0x1C360B00 AS Date), 23, 23)

VALUES (N'李四', CAST(0x1C360B00 AS Date), 23, 20)

GO

CREATE TABLE #tempUser(UserName NVARCHAR(20))

INSERT INTO #tempUser(UserName) SELECT DISTINCT(u.UserName) FROM Users u;

CREATE TABLE #tempDate (id INT IDENTITY(1,1),tDate date)

INSERT INTO #tempDate(tDate) SELECT DISTINCT(u.SDate) FROM Users u

DECLARE @dateCounts INT;

SELECT @dateCounts = COUNT(1) FROM #tempDate;

DECLARE @startCounts INT;

SET @startCounts = 1;

WHILE(@startCounts<=@dateCounts)

BEGIN

DECLARE @tempColumnName NVARCHAR(20);

SELECT @tempColumnName=Convert(nvarchar(20),td.tDate) FROM #tempDate td WHERE td.id=@startCounts;

EXECUTE('ALTER TABLE #tempUser ADD ['+@tempColumnName+'发' +'] NVARCHAR(20)')

EXECUTE('ALTER TABLE #tempUser ADD ['+@tempColumnName+'完成' +'] NVARCHAR(20)')

EXECUTE('UPDATE #tempUser  SET ['+@tempColumnName+'发' +'] = u.GetTask,  ['+@tempColumnName+'完成' +'] = u.FnishTask

FROM #tempUser t

INNER JOIN #tempDate td ON u.SDate=td.tDate

WHERE td.id='+@startCounts);

SET @startCounts = @startCounts+1;

END

SELECT * FROM #tempUser tu;

DROP TABLE #tempUser;

DROP TABLE #tempDate;

SELECT * FROM Users u;

/**我跑的结果是这样的，希望你的也是，呵呵。

sharplizhi | 菜鸟二级 |园豆：366 | 2012-09-13 23:36

0

0

http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

0

zhengyingcan | 园豆：12 (初学一级) | 2012-09-13 18:55
0

yj_smile | 园豆：228 (菜鸟二级) | 2012-09-13 22:35
0

select *
into #temp_aa
from(
select 名字 as Name,日期+'发' as  rq,任务下发 as num
from A
union all
select 名字 as Name,日期+'完成' as  rq,任务完成 as num
from A
) b

declare @aa nvarchar(2000)
set @aa=' select Name, '

select @aa = @aa + ' sum( case rq when  '''+rq+''' then num else null end ) as  '''+rq+''' ,'
from (select distinct rq from #temp_aa)   a

select @aa = left(@aa,len(@aa)-1) + ' from #temp_aa group by Name      '

exec(@aa)

cobby | 园豆：211 (菜鸟二级) | 2012-09-19 17:52

您需要登录以后才能回答，未注册用户请先注册