{"id":1898,"date":"2011-08-24T10:22:11","date_gmt":"2011-08-24T02:22:11","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=1898"},"modified":"2011-08-24T10:22:11","modified_gmt":"2011-08-24T02:22:11","slug":"%e8%bd%89%e8%b2%bc%e9%97%9c%e6%96%bccte","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2011\/08\/24\/%e8%bd%89%e8%b2%bc%e9%97%9c%e6%96%bccte\/","title":{"rendered":"[\u8f49\u8cbc]\u95dc\u65bcCTE"},"content":{"rendered":"

SQL – \u4f7f\u7528 \u4e00\u822c\u8cc7\u6599\u8868\u904b\u7b97\u5f0f CTE (Common Table Expression)<\/a><\/p>\n

\u65e9\u671f\u53ef\u4ee5\u900f\u904e\u66ab\u5b58\u8cc7\u6599 \u8868(# \u6216 ##)\u6216\u8005 table \u4f86\u5b58\u653e\u8cc7\u6599\uff0c\u73fe\u5728\u4e5f\u53ef\u4ee5\u4f7f\u7528 \u4e00\u822c\u8cc7\u6599\u8868\u904b\u7b97\u5f0f CTE (Common Table Expression) \u4f86\u66ab\u5b58\u8cc7\u6599\u3002\u96d6\u7136 CTE \u7684\u67e5\u8a62\u662f\u4f7f\u7528 Select \u67e5\u8a62\uff0c\u4f46\u662f\u4f9d\u7167 MSDN \u7684\u8aaa\u660e\uff0c\u4ecd\u6709\u4e00\u4e9b\u9650\u5236\uff0c\u90a3\u5c31\u662f\u5728 CTE \u4e2d\u4e0d\u53ef\u4ee5\u4f7f\u7528\u4ee5\u4e0b\u7684\u5b50\u53e5\uff1a<\/p>\n

1.COMPUTE \u6216 COMPUTE BY
\n2.ORDER BY (\u9664\u975e\u6307\u5b9a\u4e86 TOP \u5b50\u53e5)
\n3.INTO
\n4.\u542b\u6709\u67e5\u8a62\u63d0\u793a\u7684 OPTION \u5b50\u53e5
\n5.FOR XML
\n6.FOR BROWSE<\/p>\n

\u4ee5\u4e0b\u5c31\u4f86\u5be6\u4f5c\u4e00\u4e9b CTE \u7684\u7bc4\u4f8b…<\/p>\n

\u7bc4\u4f8b\u4e00\uff1a\u55ae\u4e00\u7684 CTE \u7528\u6cd5<\/p>\n

WITH OrdersTable (\u8a02\u55aeID, \u5ba2\u6236ID, \u54e1\u5de5ID) as
\n(
\nSelect OrderID, CustomerID, EmployeeID
\nfrom dbo.Orders
\n)<\/p>\n

Select *
\nFrom OrdersTable<\/p>\n

\"\"<\/p>\n

\u7bc4\u4f8b\u4e8c\uff1a\u5169\u500b CTE \u4f5c Join<\/p>\n

WITH OrdersTable (\u8a02\u55aeID, \u5ba2\u6236ID, \u54e1\u5de5ID) as
\n(
\nSelect OrderID, CustomerID, EmployeeID
\nfrom dbo.Orders
\n),
\nOrderDetailsTable as
\n(
\nSelect OrderID as ‘\u8a02\u55aeID’, ProductID as ‘\u7522\u54c1ID’, UnitPrice as ‘\u50f9\u683c’
\nfrom dbo.[Order Details]
\n)<\/p>\n

Select *
\nFrom OrdersTable A inner join OrderDetailsTable B
\non A.\u8a02\u55aeID = B.\u8a02\u55aeID<\/p>\n

\"\"<\/p>\n

\u7bc4\u4f8b\u4e09\uff1aCTE \u905e\u8ff4 (\u53c3\u8003\uff1a\u9ed1\u6697\u5927\u7684\u90e8\u843d\u683c\u6587\u7ae0)<\/p>\n

\u8cc7\u6599\u6e05\u55ae
\n\"\"<\/p>\n

Code\uff1a<\/p>\n

–\u5982\u679cCTE\u7684WITH\u4e0d\u5728\u7b2c\u4e00\u5217, \u524d\u65b9\u8981\u52a0\u4e0a;
\n;WITH \u516c\u53f8\u7d44\u7e54\u7d50\u679c(\u90e8\u9580\u540d\u7a31, \u76f4\u5c6c\u90e8\u9580, \u5c64\u7d1a, \u6392\u5e8f\u6b04\u4f4d)
\nAS
\n(
\n–Recursive CTE\u5206\u70ba\u5169\u500b\u90e8\u5206, \u7b2c\u4e00\u90e8\u5206\u70baAnchor Member
\n–\u6307\u4e0d\u6703\u88ab\u905e\u8ff4\u547c\u53eb\u5230\u7684\u90e8\u5206
\nSELECT \u90e8\u9580\u540d\u7a31, \u76f4\u5c6c\u90e8\u9580, 0, CONVERT(nvarchar(128), \u90e8\u9580ID)
\nFROM dbo.\u516c\u53f8\u7d44\u7e54 WHERE \u76f4\u5c6c\u90e8\u9580=N’ROOT’<\/p>\n

UNION ALL<\/p>\n

–UNION ALL\u5f8c\u65b9\u7684\u90e8\u5206\u7a31\u70baRecursive Member, \u6703\u5728\u905e\u8ff4\u904e\u7a0b\u4e2d\u53cd\u8986\u57f7\u884c,
\n–\u76f4\u5230\u7121\u4efb\u4f55\u67e5\u8a62\u7d50\u679c\u70ba\u6b62
\nSELECT P.\u90e8\u9580\u540d\u7a31, P.\u76f4\u5c6c\u90e8\u9580, B.\u5c64\u7d1a+1,
\nCONVERT(nvarchar(128), B.\u6392\u5e8f\u6b04\u4f4d + ‘-‘ + CONVERT(nvarchar(128), P.\u90e8\u9580ID))
\nFROM dbo.\u516c\u53f8\u7d44\u7e54 P, \u516c\u53f8\u7d44\u7e54\u7d50\u679c B
\nWHERE P.\u76f4\u5c6c\u90e8\u9580=B.\u90e8\u9580\u540d\u7a31
\n)
\nSELECT (REPLICATE(‘\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘, \u5c64\u7d1a) + \u90e8\u9580\u540d\u7a31) as ‘\u90e8\u9580\u540d\u7a31’, \u5c64\u7d1a, \u6392\u5e8f\u6b04\u4f4d
\nFROM \u516c\u53f8\u7d44\u7e54\u7d50\u679c
\nORDER BY \u6392\u5e8f\u6b04\u4f4d<\/p>\n

\"\"<\/p>\n

\u53e6\u4e00\u500b CTE \u905e\u8ff4\u7bc4\u4f8b\u00a0(\u53c3\u8003\uff1aJourney to SQL Authority with Pinal Dave)<\/p>\n

Code\uff1a<\/p>\n

USE AdventureWorks
\nGO
\nWITH Emp_CTE AS (
\n–\u627e\u51fa ManagerID \u70ba NULL \u7684\u8cc7\u6599\u7576\u4f5c Anchor Member
\nSELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
\nFROM HumanResources.Employee
\nWHERE ManagerID IS NULL<\/p>\n

UNION ALL<\/p>\n

–\u4e4b\u5f8c\u4ee5 Anchor Member \u70ba\u4f9d\u64da\u905e\u8ff4\u67e5\u8a62
\nSELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
\nFROM HumanResources.Employee e
\nINNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
\n)
\nSELECT *
\nFROM Emp_CTE
\nGO<\/p>\n

\"\"<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

SQL – \u4f7f\u7528 \u4e00\u822c\u8cc7\u6599\u8868\u904b\u7b97\u5f0f CTE (Common Table Expression) \u65e9 … \u95b1\u8b80\u5168\u6587 [\u8f49\u8cbc]\u95dc\u65bcCTE<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-1898","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/1898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=1898"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/1898\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=1898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=1898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=1898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}