How to Convert a SQL Query using Common Table Expr

问题:

I just found an adequate solution to How to Find Rows which are Duplicates by a Key but Not Duplicates in All Columns?, coded the stored procedure, then learned that the database is stuck at SQL Server 2000.

My solution, of course, relies heavily on Common Table Expressions.

Can anyone provide me a set of ru les for converting back to the SQL Server 2000 dialect?

Note that I have things like thisL:

;
WITH CTE1 AS ( ... ),
CTE2 AS (SELECT ... FROM CTE1 ... ),
CTE3 AS (SELECT ... FROM CTE1 INNER JOIN CTE2 ...)
SELECT * FROM CTE3
WHERE criteria
ORDER BY sequence

This would appear to make things more interesting...

---

Update: None of the CTEs are recursive.

回答1:

Two options (granted, neither are pretty -- that's why we like CTE's)

OPTION 1

Create a temp table (#, or if small enough @) and refer to it as you would the CTE. Drop when you are done.

OPTION 2 Put the entire CTE SELECT as a table in the FROM portion of the query.

SELECT *
FROM  (SELECT *
       FROM table1) oldCTE

回答2:

I don't think it is possible to come up with rules that would easily convert any cte into a non-cte statement. as the possibilities are too open-ended (particularly if you're working with recursive CTEs). The closest I can think of would be to take each CTE in order, break it into it's own query, and use it to populate a temporary table that's used by the following queries. Hardly efficient, and not guarnateed to work in all possible situations.