sql2005 to sql2000 （with关键字）解决方案_Sql Server

sql2005 to sql2000 （with关键字）
由于with在sql2000中不可用，需要将下面语句转换一下：

SQL code

    WITH t    AS (        SELECT a.ID AS aID,b.id AS bID, b.bGUID        FROM        (            SELECT DISTINCT t.guid, t.ID, ta.guid AS bGUID             FROM dbo.PMS_Task t, dbo.PMS_TaskAllocation ta             WHERE CHARINDEX(','+LTRIM(t.id)+',',','+ta.TaskIDs+',') > 0 and t.projectid = @PKID        ) b,        (            SELECT guid, ID FROM dbo.PMS_Task WHERE projectid = @AutoID        ) a        WHERE b.guid = a.guid    ),    t1     AS (        SELECT STUFF(( SELECT ','+ RTRIM(aID)         FROM t AS A        WHERE CHARINDEX(RTRIM(bID),TaskIDs) > 0 FOR XML PATH('')),1,1,'') AS Name, TaskIDs        FROM PMS_TaskAllocation AS B    )    UPDATE PMS_TaskAllocation SET PMS_TaskAllocation.TaskIDs = Name FROM t1     WHERE PMS_TaskAllocation.TaskIDs = t1.TaskIDs    and PMS_TaskAllocation.ProjectID = @AutoID

转换后需要在SQL2000中执行成功，并和该语句执行的结果一样！

------解决方案--------------------

SQL code

        SELECT a.ID AS aID,b.id AS bID, b.bGUID into #t        FROM        (            SELECT DISTINCT t.guid, t.ID, ta.guid AS bGUID             FROM dbo.PMS_Task t, dbo.PMS_TaskAllocation ta             WHERE CHARINDEX(','+LTRIM(t.id)+',',','+ta.TaskIDs+',') > 0 and t.projectid = @PKID        ) b,        (            SELECT guid, ID FROM dbo.PMS_Task WHERE projectid = @AutoID        ) a        WHERE b.guid = a.guid        SELECT STUFF(( SELECT ','+ RTRIM(aID) into #t1        FROM #t AS A        WHERE CHARINDEX(RTRIM(bID),TaskIDs) > 0 FOR XML PATH('')),1,1,'') AS Name, TaskIDs        FROM PMS_TaskAllocation AS B    UPDATE PMS_TaskAllocation SET PMS_TaskAllocation.TaskIDs = Name FROM #t1     WHERE PMS_TaskAllocation.TaskIDs = #t1.TaskIDs    and PMS_TaskAllocation.ProjectID = @AutoID--可以用临时表替代with 公用表达式
------解决方案--------------------
可以参考下：http://www.cnblogs.com/myaspnet/archive/2011/06/15/2081536.html
------解决方案--------------------
SQL code    UPDATE PMS_TaskAllocation SET PMS_TaskAllocation.TaskIDs = Name FROM         (        SELECT STUFF(( SELECT ','+ RTRIM(aID)         FROM          (                 SELECT a.ID AS aID,b.id AS bID, b.bGUID        FROM        (            SELECT DISTINCT t.guid, t.ID, ta.guid AS bGUID             FROM dbo.PMS_Task t, dbo.PMS_TaskAllocation ta             WHERE CHARINDEX(','+LTRIM(t.id)+',',','+ta.TaskIDs+',') > 0 and t.projectid = @PKID        ) b,        (            SELECT guid, ID FROM dbo.PMS_Task WHERE projectid = @AutoID        ) a        WHERE b.guid = a.guid         ) AS A        WHERE CHARINDEX(RTRIM(bID),TaskIDs) > 0 FOR XML PATH('')),1,1,'') AS Name, TaskIDs        FROM PMS_TaskAllocation AS B    )    t1     WHERE PMS_TaskAllocation.TaskIDs = t1.TaskIDs    and PMS_TaskAllocation.ProjectID = @AutoID