DECLARE @City TABLE( ID INT IDENTITY(1,1), NAME NVARCHAR(50), ParentID INT NULL ) INSERT INTO @City VALUES ('上海市', 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 entities SELECT st.* FROM dbo.scheduledTask st WITH(NOLOCK) INNER JOIN dbo.setEntityFieldTask seft WITH(NOLOCK) ON st.scheduledTaskId = seft.scheduledTaskId INNER JOIN entities e ON e.entityId = seft.entityId AND e.setEntityFieldTaskTypeId = seft.setEntityFieldTaskTypeId AND e.valueToSet = seft.valueToSet WHERE st.taskStatusId = 1