【原】SQL with的用法執行個體

DECLARE @City TABLE(    ID INT IDENTITY(1,1),    NAME NVARCHAR(50),    ParentID INT NULL)INSERT INTO @CityVALUES ('上海市', NULL), ('江蘇省', NULL), ('浙江省', NULL),        ('徐匯區', 1), ('閔行區', 1), ('黃浦區', 1),        ('南京市', 2), ('蘇州市', 2), ('常州市', 2),        ('杭州市', 3), ('溫州市', 3), ('餘姚市', 3)--SELECT * FROM @City;WITH tempCity (Id, NAME, ParentID, CityLevel)AS(    SELECT        ID, NAME, ParentID, 0 AS CityLevel    FROM        @City    WHERE        ParentID IS NULL    UNION ALL    SELECT        c.ID, c.NAME, c.ParentID, CityLevel + 1    FROM        @City c    JOIN        tempCity t    ON        c.ParentID = t.Id)SELECT * FROM tempCity

 

例子2：

;WITH entities(entityId, setEntityFieldTaskTypeId, valueToSet)  AS (    SELECT         customerID,,        CAST('8' AS VARCHAR(20))    FROM         ADMIN.dbo.customers WITH(NOLOCK)    WHERE        customerID = 13255    UNION ALL    SELECT        cia.iproBrokerID,,        CAST('0' AS VARCHAR(20))    FROM        ADMIN.dbo.customerIproAccounts cia WITH(NOLOCK), entities e    WHERE        cia.customerID = e.entityId AND        e.setEntityFieldTaskTypeId = 4    UNION ALL    SELECT        bm.eProBrokerID,,        CAST('False' AS VARCHAR(20))    FROM        dbo.brokerMappings bm WITH(NOLOCK), entities e    WHERE        BM.iProBrokerID = e.entityId AND        e.setEntityFieldTaskTypeId = 3)--SELECT * FROM entitiesSELECT    st.*FROM    dbo.scheduledTask st WITH(NOLOCK)INNER JOIN    dbo.setEntityFieldTask seft WITH(NOLOCK)ON    st.scheduledTaskId = seft.scheduledTaskIdINNER JOIN    entities eON    e.entityId = seft.entityId AND    e.setEntityFieldTaskTypeId = seft.setEntityFieldTaskTypeId AND    e.valueToSet = seft.valueToSetWHERE    st.taskStatusId = 1