{"id":79850,"date":"2024-10-12T05:27:46","date_gmt":"2024-10-12T01:57:46","guid":{"rendered":"https:\/\/nabfollower.com\/blog\/cte-%d8%a8%d8%a7%d8%b2%da%af%d8%b4%d8%aa%db%8c-%d8%a8%d8%ae%d8%b4-2-%d8%a7%d9%86%d8%ac%d9%85%d9%86-dev\/"},"modified":"2024-10-12T05:27:46","modified_gmt":"2024-10-12T01:57:46","slug":"cte-%d8%a8%d8%a7%d8%b2%da%af%d8%b4%d8%aa%db%8c-%d8%a8%d8%ae%d8%b4-2-%d8%a7%d9%86%d8%ac%d9%85%d9%86-dev","status":"publish","type":"post","link":"https:\/\/nabfollower.com\/blog\/cte-%d8%a8%d8%a7%d8%b2%da%af%d8%b4%d8%aa%db%8c-%d8%a8%d8%ae%d8%b4-2-%d8%a7%d9%86%d8%ac%d9%85%d9%86-dev\/","title":{"rendered":"CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0628\u062e\u0634 2 – \u0627\u0646\u062c\u0645\u0646 DEV"},"content":{"rendered":"

<\/p>\n

\n

\u0628\u06cc\u0627\u06cc\u06cc\u062f \u06cc\u06a9 \u0645\u062b\u0627\u0644 \u06a9\u0627\u0645\u0644 \u0628\u0627 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u06cc\u06a9 CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0628\u0627 \u062f\u0627\u062f\u0647 \u0647\u0627\u06cc \u0646\u0645\u0648\u0646\u0647\u060c \u0627\u0632 \u062c\u0645\u0644\u0647 \u0627\u06cc\u062c\u0627\u062f \u062c\u062f\u0648\u0644\u060c \u062f\u0631\u062c \u062f\u0627\u062f\u0647\u060c \u0648 \u062e\u0648\u062f \u067e\u0631\u0633 \u0648 \u062c\u0648 \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u06cc\u0645.<\/p>\n

\u0645\u0631\u062d\u0644\u0647 1: \u062c\u062f\u0648\u0644 \u0646\u0645\u0648\u0646\u0647 \u0631\u0627 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u06cc\u062f<\/p>\n

\u0627\u0628\u062a\u062f\u0627 \u06cc\u06a9 \u062c\u062f\u0648\u0644 \u0633\u0627\u062f\u0647 \u0628\u0631\u0627\u06cc \u06a9\u0627\u0631\u06a9\u0646\u0627\u0646 \u0627\u06cc\u062c\u0627\u062f \u0645\u06cc \u06a9\u0646\u06cc\u0645 \u062a\u0627 \u0627\u0637\u0644\u0627\u0639\u0627\u062a \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646 \u062e\u0648\u062f \u0631\u0627 \u0646\u06af\u0647\u062f\u0627\u0631\u06cc \u06a9\u0646\u06cc\u0645.<\/p>\n

\u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646 CREATE TABLE (
EmployeeID INT PRIMARY KEY\u060c
\u0646\u0627\u0645 VARCHAR(100)\u060c
ManagerID INT\u060c
FOREIGN KEY (ManagerID) REFERENCES \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646 (EmployeeID)
)<\/p>\n

\u0645\u0631\u062d\u0644\u0647 2: \u062f\u0627\u062f\u0647 \u0647\u0627\u06cc \u0646\u0645\u0648\u0646\u0647 \u0631\u0627 \u0648\u0627\u0631\u062f \u06a9\u0646\u06cc\u062f<\/p>\n

\u0627\u06a9\u0646\u0648\u0646\u060c \u0646\u0645\u0648\u0646\u0647 \u0627\u06cc \u0627\u0632 \u062f\u0627\u062f\u0647 \u0647\u0627 \u0631\u0627 \u062f\u0631 \u062c\u062f\u0648\u0644 \u06a9\u0627\u0631\u06a9\u0646\u0627\u0646 \u0642\u0631\u0627\u0631 \u0645\u06cc \u062f\u0647\u06cc\u0645.<\/p>\n

\u0627\u0631\u0632\u0634 \u0647\u0627 \u0631\u0627 \u062f\u0631 \u06a9\u0627\u0631\u06a9\u0646\u0627\u0646 (EmployeeID\u060c \u0646\u0627\u0645\u060c ManagerID) \u062f\u0631\u062c \u06a9\u0646\u06cc\u062f
(1\u060c '\u0622\u0644\u06cc\u0633'\u060c NULL)\u060c — \u0622\u0644\u06cc\u0633 \u0645\u062f\u06cc\u0631 \u0633\u0637\u062d \u0628\u0627\u0644\u0627 \u0627\u0633\u062a
(2\u060c '\u0628\u0627\u0628'\u060c 1)\u060c — \u0628\u0627\u0628 \u0628\u0647 \u0622\u0644\u06cc\u0633 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f
(3\u060c '\u06a9\u0627\u0631\u0648\u0644'\u060c 1)\u060c — \u06a9\u0627\u0631\u0648\u0644 \u0628\u0647 \u0622\u0644\u06cc\u0633 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f
(4\u060c '\u062f\u06cc\u0648'\u060c 2)\u060c — \u062f\u06cc\u0648 \u0628\u0647 \u0628\u0627\u0628 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f
(5\u060c '\u062d\u0648\u0627'\u060c 2)\u060c — \u062d\u0648\u0627 \u0628\u0647 \u0628\u0627\u0628 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f
(6\u060c '\u0641\u0631\u0627\u0646\u06a9'\u060c 3)\u060c — \u0641\u0631\u0627\u0646\u06a9 \u0628\u0647 \u06a9\u0627\u0631\u0648\u0644 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f
(7\u060c '\u0641\u06cc\u0636'\u060c 3); – \u06af\u0631\u06cc\u0633 \u0628\u0647 \u06a9\u0627\u0631\u0648\u0644 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f<\/p>\n

\u0645\u0631\u062d\u0644\u0647 3: CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0628\u0631\u0627\u06cc \u0628\u0627\u0632\u06cc\u0627\u0628\u06cc \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628 \u06a9\u0627\u0631\u06a9\u0646\u0627\u0646<\/p>\n

\u0627\u06a9\u0646\u0648\u0646 CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0631\u0627 \u0628\u0631\u0627\u06cc \u0628\u0627\u0632\u06cc\u0627\u0628\u06cc \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628 \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646\u060c \u0627\u0632 Alice (\u0645\u062f\u06cc\u0631 \u0633\u0637\u062d \u0628\u0627\u0644\u0627) \u0645\u06cc \u0646\u0648\u06cc\u0633\u06cc\u0645.<\/p>\n

\u0628\u0627 \u0628\u0627\u0632\u06af\u0634\u062a\u06cc EmployeeHierarchy AS (
– \u0639\u0636\u0648 \u0644\u0646\u06af\u0631: \u0645\u062f\u06cc\u0631 \u0633\u0637\u062d \u0628\u0627\u0644\u0627 \u0631\u0627 \u0627\u0646\u062a\u062e\u0627\u0628 \u06a9\u0646\u06cc\u062f (\u0622\u0644\u06cc\u0633)
SELECT EmployeeID, Name, ManagerID, 0 AS Level
\u0627\u0632 \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646
WHERE ManagerID IS NULL \u0627\u0633\u062a<\/p>\n

\n
UNION ALL\n\n-- Recursive member: Select employees reporting to the current level's employees\nSELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1\nFROM employees e\nINNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
)
– \u0627\u0646\u062a\u062e\u0627\u0628 \u0646\u0647\u0627\u06cc\u06cc: \u06a9\u0644 \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628 \u0631\u0627 \u062f\u0631\u06cc\u0627\u0641\u062a \u06a9\u0646\u06cc\u062f
\u0634\u0646\u0627\u0633\u0647 \u06a9\u0627\u0631\u0645\u0646\u062f\u060c \u0646\u0627\u0645\u060c \u0634\u0646\u0627\u0633\u0647 \u0645\u062f\u06cc\u0631\u060c \u0633\u0637\u062d \u0631\u0627 \u0627\u0646\u062a\u062e\u0627\u0628 \u06a9\u0646\u06cc\u062f
FROM EmployeeHierarchy
\u0633\u0641\u0627\u0631\u0634 \u0628\u0631 \u0627\u0633\u0627\u0633 \u0633\u0637\u062d\u060c EmployeeID.<\/p>\n
\u062a\u0648\u0636\u06cc\u062d CTE:<\/p>\n
    \n
  1. \u0639\u0636\u0648 \u0644\u0646\u06af\u0631:<\/li>\n<\/ol>\n
    \u06a9\u0627\u0631\u0645\u0646\u062f\u06cc \u0631\u0627 \u0627\u0646\u062a\u062e\u0627\u0628 \u0645\u06cc \u06a9\u0646\u062f \u06a9\u0647 \u0645\u062f\u06cc\u0631 \u0646\u062f\u0627\u0631\u062f (ManagerID IS NULL) \u06a9\u0647 \u062f\u0631 \u0627\u06cc\u0646 \u0645\u0648\u0631\u062f \u0622\u0644\u06cc\u0633 \u0627\u0633\u062a.<\/p>\n
    \u0647\u0645\u0686\u0646\u06cc\u0646 \u0634\u0627\u0645\u0644 \u06cc\u06a9 \u0633\u062a\u0648\u0646 Level \u0628\u0631\u0627\u06cc \u0646\u0634\u0627\u0646 \u062f\u0627\u062f\u0646 \u0633\u0637\u062d \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628 \u0627\u0633\u062a (0 \u0628\u0631\u0627\u06cc \u0622\u0644\u06cc\u0633).<\/p>\n
      \n
    1. \u0639\u0636\u0648 \u0628\u0627\u0632\u06af\u0634\u062a\u06cc:<\/li>\n<\/ol>\n
      \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646\u06cc \u0631\u0627 \u0627\u0646\u062a\u062e\u0627\u0628 \u0645\u06cc \u06a9\u0646\u062f \u06a9\u0647 ManagerID \u0622\u0646\u0647\u0627 \u0628\u0627 EmployeeID \u0627\u0632 \u0645\u062c\u0645\u0648\u0639\u0647 \u0646\u062a\u0627\u06cc\u062c \u0642\u0628\u0644\u06cc \u0645\u0637\u0627\u0628\u0642\u062a \u062f\u0627\u0631\u062f.<\/p>\n
      \u0633\u0637\u062d \u0631\u0627 1 \u0627\u0641\u0632\u0627\u06cc\u0634 \u0645\u06cc \u062f\u0647\u062f \u062a\u0627 \u0646\u0634\u0627\u0646 \u062f\u0647\u062f \u06a9\u0627\u0631\u0645\u0646\u062f \u0686\u0642\u062f\u0631 \u062f\u0631 \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628 \u0639\u0645\u06cc\u0642 \u0627\u0633\u062a.<\/p>\n
        \n
      1. \u0627\u0646\u062a\u062e\u0627\u0628 \u0646\u0647\u0627\u06cc\u06cc:<\/li>\n<\/ol>\n
        \u0646\u062a\u0627\u06cc\u062c \u0631\u0627 \u0627\u0632 EmployeeHierarchy CTE\u060c \u0645\u0631\u062a\u0628\u200c\u0633\u0627\u0632\u06cc \u0628\u0631 \u0627\u0633\u0627\u0633 \u0633\u0637\u062d \u0648 \u0634\u0646\u0627\u0633\u0647 \u06a9\u0627\u0631\u0645\u0646\u062f \u0628\u0627\u0632\u06cc\u0627\u0628\u06cc \u0645\u06cc\u200c\u06a9\u0646\u062f.<\/p>\n
        \u062e\u0631\u0648\u062c\u06cc<\/p>\n
        \u0647\u0646\u06af\u0627\u0645\u06cc \u06a9\u0647 CTE \u0641\u0648\u0642 \u0631\u0627 \u0627\u062c\u0631\u0627 \u0645\u06cc \u06a9\u0646\u06cc\u062f\u060c \u062e\u0631\u0648\u062c\u06cc \u0628\u0647 \u0634\u06a9\u0644 \u0632\u06cc\u0631 \u062e\u0648\u0627\u0647\u062f \u0628\u0648\u062f:<\/p>\n
        \u062e\u0644\u0627\u0635\u0647<\/p>\n
        \u0633\u0637\u062d 0: \u0622\u0644\u06cc\u0633 (\u0645\u062f\u06cc\u0631 \u0633\u0637\u062d \u0628\u0627\u0644\u0627)<\/p>\n
        \u0633\u0637\u062d 1: \u0628\u0627\u0628 \u0648 \u06a9\u0627\u0631\u0648\u0644 (\u06af\u0632\u0627\u0631\u0634 \u0645\u0633\u062a\u0642\u06cc\u0645 \u0628\u0647 \u0622\u0644\u06cc\u0633)<\/p>\n
        \u0633\u0637\u062d 2: \u062f\u06cc\u0648 \u0648 \u0627\u06cc\u0648 (\u0628\u0647 \u0628\u0627\u0628 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f) \u0648 \u0641\u0631\u0627\u0646\u06a9 \u0648 \u06af\u0631\u06cc\u0633 (\u0628\u0647 \u06a9\u0627\u0631\u0648\u0644 \u06af\u0632\u0627\u0631\u0634 \u0645\u06cc \u062f\u0647\u062f)<\/p>\n
        \u0627\u06cc\u0646 \u0645\u062b\u0627\u0644 \u0646\u062d\u0648\u0647 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0631\u0627 \u0628\u0631\u0627\u06cc \u0639\u0628\u0648\u0631 \u0627\u0632 \u06cc\u06a9 \u0633\u0627\u062e\u062a\u0627\u0631 \u0633\u0644\u0633\u0644\u0647 \u0645\u0631\u0627\u062a\u0628\u06cc \u062f\u0631 SQL \u0646\u0634\u0627\u0646 \u0645\u06cc \u062f\u0647\u062f.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
        \u0628\u06cc\u0627\u06cc\u06cc\u062f \u06cc\u06a9 \u0645\u062b\u0627\u0644 \u06a9\u0627\u0645\u0644 \u0628\u0627 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u06cc\u06a9 CTE \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0628\u0627 \u062f\u0627\u062f\u0647 \u0647\u0627\u06cc \u0646\u0645\u0648\u0646\u0647\u060c \u0627\u0632 \u062c\u0645\u0644\u0647 \u0627\u06cc\u062c\u0627\u062f \u062c\u062f\u0648\u0644\u060c \u062f\u0631\u062c \u062f\u0627\u062f\u0647\u060c \u0648 \u062e\u0648\u062f \u067e\u0631\u0633 \u0648 \u062c\u0648 \u0628\u0627\u0632\u06af\u0634\u062a\u06cc \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u06cc\u0645. \u0645\u0631\u062d\u0644\u0647 1: \u062c\u062f\u0648\u0644 \u0646\u0645\u0648\u0646\u0647 \u0631\u0627 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u06cc\u062f \u0627\u0628\u062a\u062f\u0627 \u06cc\u06a9 \u062c\u062f\u0648\u0644 \u0633\u0627\u062f\u0647 \u0628\u0631\u0627\u06cc \u06a9\u0627\u0631\u06a9\u0646\u0627\u0646 \u0627\u06cc\u062c\u0627\u062f \u0645\u06cc \u06a9\u0646\u06cc\u0645 \u062a\u0627 \u0627\u0637\u0644\u0627\u0639\u0627\u062a \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646 \u062e\u0648\u062f \u0631\u0627 \u0646\u06af\u0647\u062f\u0627\u0631\u06cc \u06a9\u0646\u06cc\u0645. \u06a9\u0627\u0631\u0645\u0646\u062f\u0627\u0646 CREATE TABLE (EmployeeID …<\/p>\n","protected":false},"author":2,"featured_media":79851,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/media.dev.to\/dynamic\/image\/width=1000,height=500,fit=cover,gravity=auto,format=auto\/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F9l5istiamquixga0d7ac.png","fifu_image_alt":"","footnotes":""},"categories":[339],"tags":[],"class_list":["post-79850","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dev"],"_links":{"self":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts\/79850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/comments?post=79850"}],"version-history":[{"count":0,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts\/79850\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/media\/79851"}],"wp:attachment":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/media?parent=79850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/categories?post=79850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/tags?post=79850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}