日期:2014-05-18  浏览次数:20572 次

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