由于with在sql2000中不可用,需要将下面语句转换一下:
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中执行成功,并和该语句执行的结果一样!
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中执行成功,并和该语句执行的结果一样!
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 公用表达式
--或者
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
) A
WHERE CHARINDEX(RTRIM(bID),TaskIDs) > 0 FOR XML PATH('')),1,1,'') AS Name, TaskIDs
FROM PMS_TaskAllocation AS B )
WHERE PMS_TaskAllocation.TaskIDs = t1.TaskIDs
and PMS_TaskAllocation.ProjectID = @AutoID
更正一下: 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
) A
WHERE CHARINDEX(RTRIM(bID),TaskIDs) > 0 FOR XML PATH('')),1,1,'') AS Name, TaskIDs
FROM PMS_TaskAllocation AS B ) as t1
WHERE PMS_TaskAllocation.TaskIDs = t1.TaskIDs
and PMS_TaskAllocation.ProjectID = @AutoID
(
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